February 10, 2009 at 5:04 pm
Hello folks,
I'm new to Visual Studio 2005 and SQL Server Integration Services 2005. I am attempting to load a series of flat files into a migration database and am having difficulty with the level of error detail in visual studio.
I have had to fight through some truncation errors (and have seen related topics here via search) and now a violation of a unique key. I'm really just hoping I'm missing some troubleshooting best practices and there is somewhere else I can look for more information as these errors crop up. So far, I am just looking at the progress tab in visual studio as I hit the "start debugging" button.
It would be great if I could find somewhere that actually showed which row the load failed on. The below information is a sample of what I am facing. It seems misleading to me because although it errors out (and the table remains empty) it reports that the rows have been written.
Any help would be greatly appreciated. Likewise, if there is a good article out there that details troubleshooting data loads through Integration Services a link would be very helpful.
Thanks,
Dak
*******************
[Destination - Mig [86]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of UNIQUE KEY constraint 'IX_Mig'. Cannot insert duplicate key in object 'dbo.Mig'.".
[Destination - Mig [86]] Information: The final commit for the data insertion has ended.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Mig" (86) failed with error code 0xC0202009. 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.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
[DTS.Pipeline] Information: "component "Destination - Mig" (86)" wrote 10449 rows.
February 10, 2009 at 7:03 pm
Dak,
You can do this using the Error Output of the OleDB Destination. Open up the OleDb Destination and go to the Error Output tab. You'll see that the default setting is to "Fail Component", which will terminate the data flow. You can change this to "Redirect Rows", which will enable the error output. You can connect that error output to another destination such as a flat file or a dump table for analysis of the errors.
Note that you'll need to turn off "Fast Mode" by choosing the "Table or View" option on the main page of the OleDb Destination. This will allow a row-by-row evaluation of the data to appropriately redirect the errors. If you leave it in Fast Mode, you'll get entire batches sent to the error output rather than just the rows in error.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
February 11, 2009 at 9:14 am
Tim,
This is exactly what I was looking for. Thanks a million.
Dak
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply