Chris Fleming


I am working on building a template/design pattern for a DTS to SSIS upgrade project.

During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.

I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.

Has anyone done something like this, or have suggestions on how we might accomplish





Re: Writing ETL Error records to a common table

jwelch


Chris Fleming wrote:

I am working on building a template/design pattern for a DTS to SSIS upgrade project.

During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.

I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.

Has anyone done something like this, or have suggestions on how we might accomplish

Take a look at this:

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/xml-transformations-part-2.aspx

I must warn you though - it uses the Reflection classes, which perform relatively slowly. If you are expecting high volumes of errors, you might want to modify this.







Re: Writing ETL Error records to a common table

Chris Fleming

Thanks JWelch, I will definately give this a try. I really appreciate your very timely blog post!





Re: Writing ETL Error records to a common table

Chris Fleming

John - I'm having a little trouble with this script. As luck would have it, most of my column names have underscores in them, which SSIS (for whatever reason..) removes them. This is causing a reference problem with the columnValue.GetValue statement.

Can you think of a workaround fo this






Re: Writing ETL Error records to a common table

jwelch

Sorry, I can't replicate this. Could you explain a little more around how you have this set up




Re: Writing ETL Error records to a common table

Chris Fleming

Thanks for taking time to look at this.

In my Data Flow, my OLE DB Source component is retrieving records that have underscores in the column Names. Here's a simple example. Select First_Name, Last_Name from Customer

In the XML script component, in the Buffer Wrapper, First_Name becomes FirstName, Last_Name becomes LastName, etc. This evidently is the standard behavior of the script component.

In the script, rowType.GetProperty(column.Name) is returning FirstName and columnValue.GetValue(Row, Nothing).ToString() fails with the object not being set.

I think if you have a table that has underscores in the field name, you should be able to re-produce.

Thanks







Re: Writing ETL Error records to a common table

jwelch

I was able to duplicate it - thanks for the update. Unfortunately, I don't have time to troubleshoot it right now.

Here's a link with an alternate method of generically referencing the columns, originally posted by jaegd. It's a bit more code to use, but I don't think it will have problems with underscores. It's also faster than using reflection Smile

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1706277&SiteID=1