Post reply

New to ssis - and help needed in Audit processing

  • October 8, 2009 at 7:32 am

    #1063441

    [font="Verdana"]I am not sure how much this article will help you solving your problem. But it is kind of similar to your approch.

    http://www.sqlservercentral.com/articles/SSIS/67871/

    Let us know on this,

    Mahesh[/font]

    MH-09-AM-8694

  • January 3, 2010 at 4:05 pm

    #1098257

    Ok so you for a solution I would recommend the following.

    1. Create your audit table

    CREATE TABLE [Audit].[DataLoad] (

    [Audit_Key] INT IDENTITY (1, 1) NOT NULL,

    [ExecutionId] UNIQUEIDENTIFIER NOT NULL,

    [FileName] VARCHAR(50) NOT NULL,

    [StartDateTime] DATETIME NOT NULL,

    [EndDateTime] DATETIME NULL,

    [IsSuccess] BIT NULL

    );

    2. Create a stored procedure which will be called at the start of each package to load your staging table. This will insert a record into the table above passing the following variables System:ExecutionInstanceGUID (From SSIS) to ExecutionId, FileName, GetDate() as your StartDateTime and a 0 for your IsSuccess field.

    The ExecutionInstanceGUID is worth capturing as it's unique to each execution and if you are using the standard SSIS logging you can tie the sysdtslog90 table back up to your audit table.

    In this stored procedure use something along the lines of this;

    INSERT INTO Audit.DataLoad

    OUTPUT inserted.Audit_Key AS AuditKey

    SELECT @ExecutionId, 'TestFile', GetDate(), 0

    This covers your step 1.

    3. Create a variable in your package to hold your AuditKey. Use a Execute SQL task to call your stored procedure and map the AuditKey value to your variable in the Result Set pane. You now have this audit key value available for you to be able to attach it to the records when loading your table.

    This covers you step 2.

    4. Finally create 2 more stored procedures. One to update your audit table and set the IsSuccess = 1 and write GetDate() to the EndDateTime using the AuditKey to target the record. The other stored procedure is exactly the same just setting the IsSuccess = 0.

    In the package use constraints and execute SQL tasks to call one or the other proc depending on the outcome of the package.

    And that's your step 3!

    Good luck

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply