Customized Sysssislog Method: The ETL team has created a method of adding the existing logging functionality within SSIS. This method adds custom scripts to insert data for package metric information. The current design is set up for simplicity as the jobs do not require many additions.
Additions to the jobs and design are as follows:
The design is used to keep track of the Parent (Sequence) Package and child packages with our implementation. There are 2 variables that are added to the parent package. The ParentExecutionId is what the job creates and sends to the logging table. So the design is to get the ParentExecutionId and send it to the child packages. The ParentSourceId is related to the package id that the parent job creates.
Add our initial container to get the ParentSourceId and ParentExecutionId and put them into our variables. After that a script is executed to add a record to the AuditParentExecution table.
Add the ParentExecutionId and ParentSourceId variables to the Child package as we did in the Parent Package.
Map the ParentExecutionId and ParentSourceId to the Child Package using the Parent Package variable settings. This will need to be added to each child package to relate the parent and child execution information.
Add the initial Child container that will run a script to add the child record with the ChildId, ChildExecutionId, ParentSourceId, and ParentExecutionId to the AuditChildExecution table.
Advantages: The Parent and Child package relationship is established and reports will come out nice for related job execution. A lot of the data is generated from the logging database so we just need to relate things to that table instead of creating everything as inserts to custom tables. There are only a few components that are added to the jobs making the development time much quicker. The Pipeline data that we get gives us detailed metrics information related to every component that runs in the package. So we don’t need to add any components or a stored procedure to get a row count for out target tables.
Disadvantages: The logging database and the Sysssislog table are used in this method and logging will be turned on for every package. This will allow for a lot of data to be inserted into the logging database.
Hours per package: Average testing and implementation are estimated at 30 mins
Reporting: these are some sample reports for the metrics and error logging.
This report shows the parent package SeqChcptErrRpt which has 3 children (ChcptErrSplit, ChcptErrCdLoad, and ChcptErrPolLoad) and shows the source and target information coming from the onPipelineRowset event in the sysssislog table.
This report shows the Errors for the child package ChcptErrSplit based off the onError event from the sysssislog table.
Issues and Concerns
- CheckPoint Status
- Nested Master Packages
- Child Package as the Master Package
SSIS Logging and Data Auditing, Tyler Chessman
SSIS: OnPipelineRowsSent event, Jamie Thomson,
SSIS Custom Logging the Easy Way, Michael Coles
SSIS Design Pattern-ETL Instrumentation, Part 1, Andy Leonard