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
