[SQL Server Data Warehousing] SSIS Auditing Mechanism?‏

I'll help if I can...

In my approach, I have 2 audit tables; one for logging package execution and the other for logging table processing.  Before each data flow I log the package like this:

INSERT INTO AuditPkgExecution (
PkgName, PkgGUID, PkgVersionGUID, PkgVersionMajor, PkgVersionMinor, ExecStartDT, ParentPkgExecKey)
Values (@PkgName, @PkgGUID, @PkgVersionGUID, @PkgVersionMajor, @PkgVersionMinor, @ExecStartDT,
@ParentPkgExecKey)

On the insert my PkgExecKey increments, so I grab it and store it in a user variable to use later in my table logging.

SELECT MAX(PkgExecKey) AS PkgExecKey
FROM AuditPkgExecution
WHERE PkgName = @PkgName AND ExecStartDT = @ExecStartDT

Now, when loading the source file I count the input records and store that as a variable, then create the initial audit record for that source's destination table and store that TableProcessKey in a variable to use later.
INSERT INTO AuditTableProcessing (
PkgExecKey, TableName, TableInitialRowCnt)
Values (@PkgExecKey, 'FactTicket', @RowCount)

SELECT Max(TableProcessKey) AS TableProcessKey
FROM AuditTableProcessing
WHERE PkgExecKey = @PkgExecKey
AND TableName = 'FactTicket'

When I load the source data, I first count the starting number of rows in the destination table and store it in a variable.  Then I record the number of rows inserted, updated, errored on insert, and errored on update and store those in variables.  Finally, after the load I count the final number of rows in the destination table, then update the audit table...

UPDATE AuditTableProcessing
SET
ExtractRowCnt = @RC_Xtrct ,
InsertStdRowCnt = @RC_Normal,
UpdateRowCnt = @RC_Updated,
InsertErrorRowCnt = @RC_InsertError,
UpdateErrorRowCnt = @RC_UpdateError,
TableFinalRowCnt = @RowCount,
SuccessfulProcessingInd = 'Y' --if we made it here, we're cool
WHERE TableProcessKey = @TableProcessKey

The final step is, after the data flow task completes, to update the audit table for package execution:

UPDATE AuditPkgExecution
SET ExecStopDT = getdate()
, SuccessfulProcessingInd = 'Y'  
WHERE PkgExecKey = @PkgExecKey

.

social.technet.microsoft.com/Forums