Database Reference
In-Depth Information
Add an Execute SQL task to the Foreach Child Package Foreach Loop container.
Rename the new Execute SQL task
Log Start of Package
. Open the editor and set the
ResultSet property to Single Row. Set the ConnectionType property to ADO.Net and
the Connection to the
SSISConfig
connection manager. Set the SQLStatement prop-
erty to
log.LogStartOfPackage
and the IsQueryStoredProcedure property to
True
. Navigate to the Parameter Mapping page and add two new parameters:
• Variable Name:
User::AppInstanceID
• Direction: Input
• Data Type: Int32
• Parameter Name:
AppInstanceID
• Variable Name:
User::AppPackageID
• Direction: Input
• Data Type: Int32
• Parameter Name:
AppPackageID
On the Result Set page, add a new result named
0
and map it to a new Parent-level
Int32 variable named
PkgInstanceID
, with a default value of
0
. Close the Execute
SQL Task Editor. Connect a precedence constraint from the Log Start of Package Ex-
ecute SQL task to the Execute Child Package Execute Package task.
Add two more Execute SQL tasks to the Foreach Child Package Foreach Loop con-
tainer. Rename the first
Log Package Success
, set the connection properties from the
ADO.Net connection manager used to connect to the
SSISConfig
database, the
SQLStatement property to
log.LogPackageSuccess
, and the IsQueryStoredPro-
cedure property to
True
. On the Parameter Mapping page, add a parameter and map
the
User::PkgInstanceID
variable to the
PkgInstanceID
parameter for the
log.LogStartofPackage
stored procedure. Connect a precedence constraint
(
OnSuccess
) from the Execute Child Package Execute Package task to the Log Pack-
age Success Execute SQL task.
Rename the second Log Package Failure, set the connection properties from the
ADO.Net connection manager used to connect to the
SSISConfig
database, set the
SQLStatement property to
log.LogPackageFailure
, and set the
IsQueryStoredProcedure property to
True
. On the Parameter Mapping page, add a
parameter and map the
User::PkgInstanceID
variable to the
PkgInstanceID
parameter for the
log.LogStartofPackage
stored procedure. Connect a preced-
Search WWH ::
Custom Search