Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 531

stored procedure. Change the Data Type property of the ApplicationName para-
meter to String. On the Result Set page, add a new result named 0 and map it to a new
Int32 variable named AppInstanceID . Set the default value of the AppIn-
stanceID variable to 0 . Close the Execute SQL Task Editor and connect a preceden-
ce constraint from the Log Start of Application Execute SQL task to the Get Package
Metadata Execute SQL task.
Drag another Execute SQL task onto the control flow beneath the Foreach Child
Package Foreach Loop container and rename it Log Application Success . Open the
editor, change the ConnectionType property to ADO.Net, and set the Connection prop-
erty to the SSISConfig connection manager. Enter
log.LogApplicationSuccess in the SQLStatement property and set the
IsQueryStoredProcedure property to True . Navigate to the Parameter Mapping page
and add a mapping between the User::AppInstanceID SSIS variable and the
Int32 AppInstanceID parameter for the log.LogApplicationSuccess
stored procedure. Close the Execute SQL Task Editor and connect a precedence con-
straint from the Foreach Child Package Foreach Loop container to the Log Application
Success Execute SQL task.
What did you just accomplish? You added SSIS application instance logging to the
control flow of the Parent.dtsx SSIS package. To test this, you need to execute
Parent.dtsx in the SSDT debugger.
Once execution completes, execute the following query to observe the logged res-
ults:
Select * From [log].SSISAppInstance
When I execute this query, I get the results that are shown in Figure A-10 .
Figure A-10 . Observing the results of querying the application instance log
What happens when an SSIS application fails? You want to update the
log.SSISAppInstance row with an EndDateTime and set the Status to Failed.