Wednesday 3 April 2013

SSIS Data Flow Error Handling

Introduction
If I am taking about the data source of real world, no data source is perfect. That means we have to handle the anomalies and bad data when we process the data in SSIS data flow. That's the reason the SSIS data flow error handling is important. It is article I am trying to discuss about the Error handling portion of SSIS data flow. Hope it will be useful and informative.

Data flow Path
In my previous articles we see the precedence constraints of control flow. The path of the data flow is similar to that except the data flow path handles the row of data rather than the execution status.

There are two primary types of data flow paths
1.    The green color connectors that connects one component to another.
2.    Error paths are the read in color and connect between components.

Please note that not all components are containing the error path as they are not performing any operation on the data itself so there is no possible point of failure. For example, the multicast components. They only copies data.
Why the SSIS Error Occurs
The error may be occurs in the SSIS components for the several of reasons. Some common reasons are motioned bellow.

1.    The data conversion error
2.    For the Expression evolution error
3.    Look match error

Types of the Error
There are two types of error found in the SSIS.

Truncation:  A truncation generates results that might be usable. For example, if we are trying to insert 15 character values into 10 charter length column.
Error: This is failure of the SSIS component and it generates the NULL result. For example:  If we are trying to convert alpha-numeric string to Integer. This will result data conversion error.
Configuring Error Output
To use the error path we need to configure the error output. There are three error handling options for handling error in the data flow components



  Error Handling Options
  Description
Fail Component
Setting the error output to this causes the Data Flow task fails when an error or a truncation occurs. Failure is the default option for an error and a truncation.
Ignore Failure
The error or the truncation is ignored and the data row is directed to the output of the transformation or source.
Redirect Row
The error or the truncation data row is directed to the error output of the source, transformation, or destination.

Example of SSIS Data Flow Error Handling
Here I am taking a simple example to understand it properly. Here I use just screen shot which is self describing.

Step-1 [ The Case Study ]
Here we have Text File name Test.



We are going to extract the flat file in my SQL Server Database Table Object. The script of the SQL server table objects are mentioned bellow.

CREATE TABLE Tbl_MYEPLOYEERECORDS
(EMPID          VARCHAR(1),
 EMPFULLNAME    VARCHAR(150),
 EMPGRADE       VARCHAR(1))

If any error came during the extraction it Logged the error in our text file name "ErrorOutput".

Step-2 [ SSIS Design ]



Step-3 [ Editing Flat File Source ]







Step-4 [ Editing Flat File Destination for Error Log Saving  ]

It just like Editing Flat file Source



Step-5 [ Generate Error by editing the Text File and Execute Package ]









Hope you like it.






Posted by: MR. JOYDEEP DAS

11 comments:

  1. Hi Mr Joydeep Das

    Thanks for the tutorial, it works for me, but the error row does not appear in the error text file, the file is blank after the package runs, any suggestions with that?

    Best Regards

    ReplyDelete
    Replies
    1. Is there any error Exists? If not then the Error file is showing Blank.. I think u do not have any error.

      Delete
    2. Hi ,
      Is there any way by which we can get the error line number and error column in error output file.
      It will help to resolve the issue very quickly

      Delete
  2. very very nice article it is very easy to understand.thank you for sharing knowledge
    have a nice day

    ReplyDelete
  3. Hello,firstly I would like to thank you for such a informative article on SSIS and also would like to ask you that do you have some more articles like this on the wide use of SSIS Postgresql Write.Thanks.

    ReplyDelete
  4. Thank you for putting up a descriptive post on a very useful aspect of SSIS. This actually helped me a lot to understand SSIS.

    SSIS PostgreSql Write

    ReplyDelete
  5. Hedges Information Technology LLC is a leading and established IT, ELV & AUTOMATION System Integrator based in Dubai, U.A.E, with over a decade of experience catering to key business verticals in SMB, Mid-market & Enterprise accounts.
    elv and automation systems uae

    ReplyDelete
  6. While designing the interior your main concern should be furniture and windows. Blinds play significant role in beautifying the offices.
    Roman blinds or Shades has power to fully transform your space
    roman blinds dubai

    ReplyDelete


  7. We offer 14 days easy return and exchange if goods have any problems. Please email us at sales@zamarah.com if you would like to request a return.
    melissa and doug

    ReplyDelete