x

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

more ▼

asked Mar 01, 2010 at 12:30 AM in Default

Zoheb H Borbora gravatar image

Zoheb H Borbora
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Mar 01, 2010 at 02:47 AM

Todd McDermid gravatar image

Todd McDermid
391 2

+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.
Mar 01, 2010 at 03:09 AM Tom Staab
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 01, 2010 at 02:12 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x926
x74
x66

asked: Mar 01, 2010 at 12:30 AM

Seen: 11580 times

Last Updated: Mar 01, 2010 at 12:30 AM