SSIS Logging Data Model

syssislog – A table to log the events from SSIS jobs. Every time the job has an event such as OnError, PackageStart, PackageEnd, and OnPipelineRowsSent we send that information to this table. The key elements are the event, source, sourceid, executionid, starttime, endtime, and message. This information tells us every thing we need to know about what is happening in a job and allows for proper auditing functionality. The negative aspect is the lack of ease for getting information out for reporting.

AuditPackage – A table to log every package that has run and that gives us a status. The important information is the PackageName, PackageGuid, ExecutionGuid, StartTime, EndTime, and status. The stored procedure sp_ssis_addlogentry has been modified to look at specific events that it will add to the sysssislog table and based on these events will insert data into the AuditPackage table. Events that it looks at from the sysssislog table are OnError, PackageStart, and PackageEnd.

AuditParentExecution – When a job runs and we have a Sequencer Package or a Master Package. The basic idea is that we need to keep track of the master packageid and executionid coming from that master job. So when the master package runs the Guids coming from the job are added to this table. We keep track of the ParentSource or name, the ParentSourceId, and the ParentExecutionId. The ParentSourceId and ParentExecutionId are the 2 values that we will pass down to the children package.

AuditChildExecution – When we have a child job running inside the master job we see all this information being added to the sysssislog table. We get the ParentSourceId and ParentExecutionId from the master package and then add the child package information with the parent package information to this table. This table is the link to the AuditParentExecution, AuditPackage, and sysssislog tables. With Each child it will have its own ChildSource, ChildSourceId, and ChildExecutionId. This link is nice because with each child you will have the ParentSourceId and ParentExecutionId associated to that job execution.

The Master/Sequencer package logging configuration is to set the OnError Event for the package only.

This will allow for minimal logging and give us basic start, stop and error information on the package. The only reason for the OnError is to record if the job failed.

The Child package logging configuration is set at the dataflow level only. We want to capture the OnError and OnPipelineRowsSent. The OnError is important to capture any errors that happen in the pipeline. The new feature that we have added to the jobs is the OnPipelineRowsSent event and this will give us all the activity that is happening in the dataflow pipeline. This event is what we want to capture row counts and metric information as it flows through each component.

The idea behind the OnPipelineRowsSent is to add these events to the sysssislog table and then due to the nature of the log and its attention to detail we will parse the message column with a function called ParsePipeline and then call a view. The view is called vwOnPipelineRowsSent whichwill give us detailed descriptions of the components and the rows sent and will have the appropriate ExectionId and Source name information that will relate all the Audit tables we have created above.

Once the sysssislog table data is inserted we now have the Events (PackageStart, PackageEnd, OnPipelineRowsSent, and OnError) that we captured for the Master and Child Packages. The sysssislog table illustrates this logging feature below.

As the data is being inserted into the sysssislog table using the sp_ssis_addlogentry stored procedure we will also be inserting the data into the AuditPackage table. We will modify the sp_ssis_addlogentry to add this feature. Then we can look at the PackageStart, PackageEnd, and OnError events to determine how the data is inserted and when to update the status.

When we run the Master Package we will insert the parent information into the AuditParentExecution table from the Insert_ParentExecution_Record stored procedure. We gather the information from the package system variables (System::PackageID, System::ExecutionInstanceGUID) with the SSIS package.

When the Child Package runs we pass the User::ParentSourceId and User::ParentExecutionId to the Insert_ChildExecution_Record along with the ChildSource, ChildSourceId, and the ChildExecutionId.

Once the sysssislog table has inserted event information we want to call the vwOnPipelineRowsSent view which will call the ParsePipeline function to get the message information parsed. This will allow us to see the information for each component and the RowsSent. We can then take this information based on the source and executionid and relate the information to the Audit Tables.

SQL Additions

  • AuditPackage – table
  • AuditParentExecution – table
  • AuditChildExecution – table
  • Insert_ParentExecution_Record – stored procedure
  • Insert_ChildExecution_Record – stored procedure
  • sp_ssis_addlogentry – (alter) stored procedure
  • up_GetExecutionContext – stored procedure
  • ParsePipeline – function
  • vwOnPipelineRowsSent – view

One thought on “SSIS Logging Data Model

  1. Pingback: SSIS Custom and SYSSSISLOG | Sladescross's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s