question

Zoheb H Borbora avatar image
Zoheb H Borbora asked

How to log error and continue data flow task in SSIS

Hi,

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

ssiserror-messageimport-data
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Todd McDermid avatar image
Todd McDermid answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image Tom Staab ♦ commented ·
+1 for correctly reading the question (unlike me). Todd's answer is probably what you're looking for if you're having problems moving data inside a Data Flow. My answer is for handling component-level errors in any component type.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

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:

To Prevent the error from going further up the chain, open up the System Variables from within the On Error error handler of the task and change the Propogate property’s value to False.

I had gotten as far as you did before reading that article. Once I did that last step, it worked as intended.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.