Meta Database – Audit Tables

The Audit tables in the META database are used to track the statistics of package executions. 

Packages

The Packages table stores information relating to the package name and versions which are executing.  The packages table has one record per package version.

  • PackageKey – The surrogate key used to uniquely identify a record in the packages table.
  • PackageName – The name of the package.
  • PackageGUID – The global unique identifier associated with the package.
  • PackageVersionGUID – The global unique identifier associated with the particular version of the package
  • PackageVersionMajor – The major version number of the package, set through the SSIS package property.
  • PackageVersionMinor – The minor version number of the package, set through the SSIS package property.
  • PackageBuild – The auto incrementing number associated with the package build.  This CAN be set through the SSIS package property.
  • InsertDate – The date that this package record was inserted into the packages table.

PackageExecutions

The PackageExecutions table stores information relating to the package that is being run, the start and end dates, and whether or not the execution was successful.  The package executions table has one record per package execution.

  • PackageExecutionKey – The surrogate key used to uniquely identify a record in the package executions table.
  • ParentPackageExecutionKey – The surrogate key of the package which called the current package.  Default of -1.  Used to track parent to child package relationships.
  • ExecutionStartDate – The date and time when this package began executing.
  • ExecutionStopDate – The date and time when the audit record was closed (Note: this is not exactly the stop time of the package, as the audit trail is closed before files are deleted and the email is sent).
  • SuccessfulProcessingIndicator – A flag used to identify whether or not the package completed successfully.  ‘Y’ for successful processing, ‘N’ for non-successful processing, any other value is unknown.
  • Comments – Relevant comments to the state of the package execution, or notes on why it might have failed.

TableProcessing

The TableProcessing table stores information relating to the statistics of the package execution. How many records where initially in the table, how many were inserted, how many updated, how many errors there were, and how many records were in the table after execution.  The table processing table has one record per table per package execution.

  • TableProcessingKey – The surrogate key used to uniquely identify a record in the table processing table.
  • TableDictionaryKey – The foreign key which relates this execution to the destination table as it is stored in the table dictionary table.
  • ExtractRowCount – The number of records extracted from the source query.  Not always a 1 to 1 on the number of records we would expect to insert.
  • InsertStandardRowCount – The number of records inserted into the destination table via standard processing.
  • InsertNonStandardRowCount – The number of records inserted into the destination table via non-standard processing.
  • UpdateRowCount – The number of records in the destination table updated during this packages execution.
  • ErrorRowCount – The number of records which gracefully failed during the execution of the package.
  • InitialRowCount – The number of records present in the destination table prior to the package execution.
  • FinalRowCount – The number of records present in the destination table after the package execution.
  • InitialMaxSurrogateKey – The maximum surrogate key value present in the destination table prior to the package execution.
  • FinalMaxSurrogateKey – The maximum surrogate key value present in the destination table after the package execution.
  • SuccessfulProcessingIndicator – A flag used to identify whether or not the package completed successfully.  ‘Y’ for successful processing, ‘N’ for non-successful processing, any other value is unknown.

DimAudit

The DimAudit table stores information to tie the PackageExecutions and TableProcessing tables together for those packages which might have more than one entry for the TableProcessing table per package execution.  The dim audit table has one record per table processing and package executions record pair.

  • AuditKey – The surrogate key used to uniquely identify a record in teh dim audit table.
  • TableProcessingKey – The foreign key which relates this audit record to the table processing record for this execution.
  • PackageExecutionKey – The foreign key which relates this audit record to the package executions record for this execution.
  • BranchName – The name for the branch for this package execution / table processing pair.
  • BranchRowCount – The number of records updated or inserted for this package execution / table processing pair.
  • ProcessingSummaryGroup – The grouping of multiple audit processes into a single unit.  Used for statistics aggregation purposes.

Population of the Audit Tables

If you are using the framework, the audit tables are populated through a series of stored procedures for each package execution in the pre-processing sequence container.  They are then updated via stored procedures in the post processing container.  The only audit table to not receive a new record for every run is the packages table.  It only receives new records when it identifies a package version which it has not yet stored. 

The scripts for the table creation, as well as those of the stored procedures and unknown members, will be available on my skydrive.

Advertisements
This entry was posted in SSIS and tagged , . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s