Tuesday, March 8, 2011

Error Handling in SSIS

--> Error Handling can be achieved in Data Flow Task by configuring Error Output.
--> 3 actions can be performed whenever some error occurs
      - Ignore Failure
      - Redirect Row
      - Fail Component
--> By default Fail Component is being used by SSIS
--> Ignore Failure - if error occurs, SSIS will ignore that error and move ahead
--> Redirect Row - if error occurs, SSIS moves those erroneous (bad) records to different destination and   
      correct records to actual destination

We will look into an example of redirecting rows.

EXAMPLE

0. Create one source.txt file with some data. like this
           1, 01/01/20001
           2, 01/01/20002
           3, 01/01/20003
           4, 02/30/20004
    Here we can see that 4th record does not have correct date.
1. Create one Data Flow Task
2. In DFT, drag Flat File source and create new connection manager.
3. Give a source connection manager name and choose the created file.
4. Click on columns and you can see the records like this.
5. Now drag Data Conversion Transformation and connect it with source.
6. Convert 1st column into Integer and 2nd column into Date as shown below.
6. Click on Configure Error Output
7. Select "Redirect Row" from dropdown in Error column

8. Drag FlatFile Destination and connect it with Data Conversion Transformation
9. Click on new connection, give connection manager name and mention a file name. It is not required that file should exists in mentioned path.
10. "Column names in the first data row" checkbox will allow us to display header in destination file.
11. click on Advanced. and delete original column.

12. if you want to overwrite the file (means whenever you execute the package, it will overwrite the content otherwise it will delete the content and add one more time)
13. Now check the mapping once. It should be like this.
14. Drag one more Flat File Destination and connect it with Data Conversion Transformation (Red Arrow).
15. Create new connection with new error file and Check the mappings like this,
16. Now execute the package and check the result. Success File should have 3 rows and Error file should have 1 row with Error number.


7 comments:

  1. Thanks Nisang. This was very helpful.

    ReplyDelete
  2. Hi Nisarg,
    Can you please guide me about Event handler???

    ReplyDelete
  3. EXCELLENT ... THAN 'Q'... Keep post like these examples..very helpful to emerging BI developers...

    ReplyDelete
  4. Aren't all the dates wrong in your Source file? Look at the years.

    ReplyDelete
  5. Excellent It is very helpful, please also correct date format

    ReplyDelete
  6. Thanks Nisang !!!
    In my SSIS package i have created incremental load and track I/U/D rows count using ssis2012 features and now i want to redirect row on one table which would insert rows incase any failure on In/Up/Delete. Is there any way all the records redirected on one task which is connected by the all other destination table.

    ReplyDelete