Patricia 

Great article, thanks for sharing the knowledge, we use the same, but we even log the updated, inserted, and unchanged counts, along with the extracted counts of course.

This works fine, thanks for sharing! Does someone have an example of a stored procedure that will log the updated, inserted and deleted records in dimension and fact tables? I use slowly changing dimensions in SSIS 2008.

/Johanna

Take a look at the OUTPUT clause: http://msdn.microsoft.com/en-us/library/ms177564.aspx

This allows you to "output" to another table when you're doing an insert, update or delete.  The above page shows a bunch of examples that output into a table variable but you can use a regular table as well.

This is essentially what you could do in a trigger; this just allows you to do it without having the trigger.

Johanna,

Here is a tip on the OUTPUT clause - http://www.mssqltips.com/tip.asp?tip=1381.

Thank you,
Jeremy Kadlec

Thanks a lot! The OUTPUT clause was very useful.

/Johanna

Great article.

Which version of the AdventureWorks Database do I need and where can I get the MSSQLTIPS DB?

Thanks.

The download (link is in the Next Steps section of the tip) has a text file with the T-SQL statements to create the database objects.  You can create a database called MSSQLTIPS and run the T-SQL commands in it.

As far as AdventureWorks this tip is pretty old so it was probably the version that came with SQL Server 2005.

I don't think any of the logging is dependent on having adventureworks; in other words you test the logging and not even have the Extract Customers data flow.  There is a package variable that gets set with the number of rows extracted but that's all that happens in the data flow as far as what affects the logging.

Thanks for the reply.

Before and after each load I must got a count from the source system and staging.

If they do not match I need to record the mismatch.

I did something like this in DTS in 2004 but I can't remeber exactly how I did it. 

I seem to remeber having an Execute SQL Task to output the counts as output parameters and save in a global variable and log the error in the next task.

I want to keep the table structures as generic as possible but I still pondering the design.

Thank again. 

I ran your create_db_objects.txt script and it is missing some tables. Please advise.

What file format is the CustomLogging.database?

I was hoping to get the correct version of AdventureWorks and MSSQLTIPS Database.

Thank you.

Msg 4902, Level 16, State 1, Line 1

Cannot find the object "etl.ExtractLog" because it does not exist or you do not have permissions.

Msg 4902, Level 16, State 1, Line 1

Cannot find the object "etl.ExtractLog" because it does not exist or you do not have permissions.

Msg 4902, Level 16, State 1, Line 2

Cannot find the object "etl.LoadLog" because it does not exist or you do not have permissions.

Msg 4902, Level 16, State 1, Line 1

Cannot find the object "etl.LoadLog" because it does not exist or you do not have permissions.

I created the DDL for the tables based on your ERD.

CREATE SCHEMA etl

GO

CREATE TABLE etl.PackageLog(
PackageLogID int Identity (1,1) NOT NULL,
PackageName VARCHAR(255) NOT NULL,
StartTime SmallDateTime NOT NULL,
EndTime SmallDateTime,
Success bit
CONSTRAINT PK_PackageLog
PRIMARY KEY (PackageLogID)
)
GO

CREATE TABLE etl.ExtractLog (
ExtractLogID int IDENTITY (1,1) NOT NULL,
PackageLogID int NOT NULL,
TableName VARCHAR(55) NOT NULL,
ExtractCount int NOT NULL,
StartTime SmallDateTime NOT NULL,
EndTime SmallDateTime,
Sucess bit
CONSTRAINT PK_Extract_Log
PRIMARY KEY (ExtractLogID),
CONSTRAINT FK_ExtractLog_PackageLog
FOREIGN KEY (PackageLogID)
REFERENCES etl.PackageLog (PackageLogID)
)
GO

CREATE TABLE etl.LoadLog (
LoadLogID int IDENTITY (1,1) NOT NULL,
PackageLogID int NOT NULL,
AS400TableName VARCHAR(20) NOT NULL,
SQLServerTableName VARCHAR(55) NOT NULL,
BeforeAS400Count INT NOT NULL,
BeforeSQLServerCount INT NOT NULL,
StartTime SmallDateTime NOT NULL,
EndTime SmallDateTime,
Success int,
CONSTRAINT FK_LoadLog_Package_Log
FOREIGN KEY (PackageLogID)
REFERENCES etl.PackageLog (PackageLogID),
CONSTRAINT PK_Load_Log
PRIMARY KEY (LoadLogID)
)
GO

Unfortunately I do not understand how to configure a RowCount Task.

You define a package variable (typically at the package level) then add a row count transformation in your data flow after a data source; i.e. you read data from somewhere then each row flows through the row count.  You give the row count your package variable and it populates it with the number of rows that you retrieved.

Here are the details: http://msdn.microsoft.com/en-us/library/ms141136.aspx

Thanks for the Link on Row Count Transformation. Actually I found this article earlier this morning.

It did not go into thjat much detail, for example, how does the  Input and Output Properties get defined?

I created all of the DB objects but the EXEC SQL Task that calls EXEC etl.stp_InitExtractLog ?,'Customer' fails.

The stored procedure has two paramters, one for PackageID and that variable has no value.

I do not see what the packageID Variable is being set?

So the Stored procedure fails because it can't insert a null value in the ExtractCount Column.

The second paramater is 'Customer'. I believe that it should be 'Sales.Customer'.

Thanks for replying. 

ok, my bad the task that calls EXEC etl.stp_InitExtractLog completed successfully.

However there seems to be an issue with the OLE DB Source. The parameter does not appear to be mapped.

When I click on Preview for Get Updated Customers I get an message box 'No value given for 1 or more required paramaters?

The PackageID variable is set in the Init Package Log Execute SQL Task.  The stored proc that's called returns the value.  Pay close attention to how you configure the task.  On the General tab ResultSet is set to SingleRow.  On the Parameter Mapping tab a package variable is passed as a parameter to the stored proc.  On the Result Set tab the first column of the result set returned by the stored proc is assigned to the package variable User::v_PackageLogID

Ray,

I checked everything and is configured as you specified.

WHen I execute the Stored Procedure in SSMS it returns the package ID,

On the results table the result name is 0 and the variable name is User::v_PackageLogID.

It appears that the variable User::v_PackageLogID Variable is not being set?

The OLE DB SOurce and Destination task faile and I'm getting the following errors:

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
 The PrimeOutput method on component "Get Updated Customers" (1) returned error code 0xC02020C4.
 The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

[Put Updated Customers in Staging [56]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (69)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (69)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Put Updated Customers in Staging" (56) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (69). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[Get Updated Customers [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Get Updated Customers" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 

Ray,

I created the destination table with a SELECT INTO WHERE 1 = 2.

There were NOT NULL Constraints, I removed them and it worked.

Thanks for being patient with me?

Did you write an article on Package Error Loging?

I did not but if you search the MSSQLTips site for SSIS Error Log" you will get a number of tips.  Click the Tips tab to get to the tips.

Ray,

I have 69 tables in each of two schemas (138 Tables). The tables are the same in each schema.

Do you know how I could modifiy the Package to include the Schema Name?

Thanks again.

Add a SchemaName column to the ExtractLog and LoadLog tables.  Add a SchemaName parameter to the stp_InitExtractLog and stp_InitLoadLog stored procedures; add the SchemaName column to the INSERT statements.

I just need to add a paramater and modify the stored procedure & table to include the schema column.

I can't think of an alternative way other than hard code the table & schema.

I appreciate your replies.

Thanks.

Hard-coding the schema name and table name is usually okay unless they are going to change.  If they will change then you may be able to put them in a table that you read or possibly supply them a parameters when you run the package.  Either way if the schema and table name are variable, then your SQL statements will need to by dynamic.

I just read this article today and I was able to get everything to work except the LoadLog - where is the store procedure logic for

stp_InitLoadLog and stp_EndLoadLog  - it was not in the zip file above?  Can I use LoadLog for my load into staging and into dimensions and fact tables?  If so can you show me how?

The package only dealt with extracting data into staging.  To do custom logging for loading dimension or fact tables, stp_InitLoadLog and stp_EndLoadLog would look almost identical to stp_InitExtractLog and stp_EndExtractLog; the execute SQL tasks would also be just about the same as they are in the package in this tip.