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.
--> 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.
6. Convert 1st column into Integer and 2nd column into Date as shown below.
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.
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)
15. Create new connection with new error file and Check the mappings like this,
