I am new to SQL Server Integration Services and am building an ETL pipeline to transfer data from an Oracle DB to a MSSQL Server database.
Can you please tell me how I can configure the data flow task so that it does not stop because of a single error - but rather it should just log/consume the error and continue with the next row?
I did create an onError errorHandler which is just a simple C# script that does nothing - but that doesn't seem to do the job. Any code snippets will be much appreciated.
Thanks for the help.
Best Regards, Zoheb H Borbora Dept of CS, University of Minnesota
asked Mar 01, 2010 at 12:30 AM in Default
Zoheb H Borbora
You need to identify the component that is reporting the error - it should be the one turning red if you're running it interactively in BIDS. That component should have an option to route rows in error to the "error output" (the default is to fail the component, and you also have the choice to ignore the errors). Once you route the errors to the error output, you can attach the error output to a flat file destination - or any other kind of transform - to count rows, store the errors, whatever you like. The Data Flow will continue processing the other rows.
answered Mar 01, 2010 at 02:47 AM
When I first needed to do something similar with SSIS, I had trouble with this too. In my opinion, it is far from obvious. Fortunately, I found the following article so helpful that I saved it for future reference: http://sqlblog.com/blogs/rushabh_mehta/archive/2008/04/24/gracefully-handing-task-error-in-ssis-package.aspx
The key section is this:
I had gotten as far as you did before reading that article. Once I did that last step, it worked as intended.
answered Mar 01, 2010 at 02:12 AM
Tom Staab ♦