SSIS Auditing Mechanism?‏


  • Hi All,

    I have a SSIS pkg which inserts/updates tables in my data warehouse. ( SQL Server 2008 )

    I need to develop a mechanism to capture how many records added/updated against tables on data warehosuse ( daily basis) and store data onto a table as below.

    Can you guys provide some advice on this  please??

    Table Name


    New Rows added

    Rows Updated












    Donnerstag, 18. April 2013 01:09


  • 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,

    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
    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

    • Als Antwort markiert Mira Pimbi Freitag, 19. April 2013 01:16
    Donnerstag, 18. April 2013 02:12