October 7, 2009 at 3:34 pm
i am new to SSIS but has good design expereince.
the current requirement needs me to a design a process for audit table.
step 1: Basically we load flat files into stage tables and for every file load we want to insert a row into audit table and generate a audit_key with starttimestamp and filename informatin.
step2:while loading flat file into DB table, we also load the above generated audit_key for every row.
step3:After the load is sucessful you need to update the record in the aduit table with job status to "success"..
what is the best approach to do this in SSIS 2008.?
In this process, the step1 and step3 are same for every file load, so can we create the process and reuse for every file? if so how to do that?
Can we have a separate package for step1 and step3 and call those packages in every load package i.e step2?
also in step 2, you do lookup on audit table to get the audit key, how can we create that lookup as reusable lookup ? any thoguths?
October 8, 2009 at 7:32 am
[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
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply