Home > SSIS > Redirecting error data to error output in a DFC – SSIS

Redirecting error data to error output in a DFC – SSIS


Error output is available in most of the Data Flow Component (DFC). We have to configure this output pipeline to capture or to move the error data from the normal data flow to avoid unnecessary package failure.

Error output pipeline is marked in red color in Flat File Source component.

pic1Scenario:

I have comma-separated value in a flat file with two columns (code, Name). Code is an integer value and name is a varchar(50) data type configured in the flat file connection manager. But, I need my package execution uninterrupted. Because, I know some the codes  in the flat files are characters. So, flat file reader component will fail reading the character value. But, I want to redirect the error data to separate table.

Implementation

Step 1: Create a new package and add data flow task.

Step 2: Go to Data Flow Task and add flat file reader component and configure the task.

Create flat file connection

pic1pic2

pic3

Step 3: Connect DFT source output (green pipeline) to destination task. In this demo, I am using Data Reader Destination task. Valid output will go to this destination task.

pic4Step 4: Configure Error output in Flat File Source task. Go to task Edit window and select error tab.

pic5Now, I am going to configure error output for the column “Code”. But, we can also set to all columns.

I am redirecting the error rows to the error output of the Flat File source component.

pic6We have done with error output configuration. Now, create a table to store the error data for further analysis.

Step 5: Add OLE DB destination task. We are going to store the error data to a SQL table. I take table schema from the OLE DB destination task.

pic7Map the error output columns to the table schema

pic8

Step 6: Execute this package

pic9We have got 5 valid rows and 2 error rows. Lets explore the error output table.

pic10

Conclusion

We have learned how to redirect the error data in data flow without package failure.

Advertisements
  1. February 14, 2013 at 5:00 pm

    Nice one.

  2. February 14, 2013 at 9:45 pm

    Thanks Rohit. Keep coming to this site and give your valuable comments.

  3. ram
    August 9, 2013 at 7:04 am

    Good article. 🙂

  4. J
    October 24, 2015 at 9:57 pm

    It is really awesome solution.

  5. Ian A
    January 5, 2017 at 3:22 pm

    many thanks, Ram

  1. November 20, 2014 at 11:02 am
  2. March 17, 2016 at 12:20 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: