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

Create Procedure [log].LogPackageFailure
@PkgInstanceID int
As
update log.SSISPkgInstance
set EndDateTime = GetDate()
, Status = 'Failed'
where PkgInstanceID = @PkgInstanceID
go
print 'Log.LogPackageFailure stored procedure created.'
print ''
The log.SSISPkgInstance table will hold the SSIS package instance data.
Log.LogStartofPackage inserts a row into the SSISPkgInstance table;
log.LogPackageSuccess updates the row with an EndDateTime and a 'Suc-
cess' status, while log.LogPackageFailure updates the record with an
EndDateTime and a 'Failed' status.
In Parent.dtsx , open the editor for the Foreach Child Package Foreach Loop
container. Navigate to the Variable Mappings page and add a new variable. Configure
the following settings in the Add Variable window:
• Container: Parent
• Name: AppPackageID
• Namespace: User
• Value Type: Int32
• Value: 0
Click the OK button to close the Add Variable window. The AppIn-
stanceID —which exists in the dataset inside the User::Packages SSIS vari-
able—is returned from executing the cfg.GetSSISApplication stored proced-
ure. The AppPackageID column is returned as the fifth column. Therefore, the Ap-
pPackageID variable's Index column on the Variable Mappings page of the
Foreach Child Package Foreach Loop container should be set to 4 (the fifth value in a
0-based array). Click the OK button to close the Foreach Child Package Foreach Loop
Container Editor.