Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.