SSIS Error Handling : In SQL Server Integration Services, errors might occur when we are extracting data from a source, loading data into a destination, or when you are performing a transformation. It is because of the unexpected data, and failing the package is a bad practice. To handle these situations, SSIS introduced the concept called Error Handling. Let’s see how to configure Error Handling with an example.
For the SSIS demonstration purpose, we will Export data present in the Employee.xls to the SQL Server database table and configure error handling at Data Conversion. The below screenshot will show you the data present in the Employee Excel file
From the above screenshot, you can see that Occupation of EmpId 2, 13, 14 is larger than the length 255
Error Handling in SSIS Example
Drag and drop the data flow task from the toolbox to control flow and change the name to Error Handling.
Double click on it will open the Integration Service data flow tab. Now, Drag and drop Excel Source, Data Conversion Transformation, and OLEDB Destination from the toolbox into the data flow region.
Double click on Excel source will open the connection manager settings and provides an option to select the table holding the source data. Here, we are choosing the Employees.xls file present in the file system.
From the above screenshot, you can observe that we are selecting the Employee_Destination table present in the Employee.xls file
Click ok and drag the blue arrow from Excel Source to Data Conversion Transformation. It allows the transformation to use the source data.
Next, please double click on the Data Conversion Transformation to edit and convert our source columns data type to the required data type with length.
Here we are leaving the Occupation length as 255 because we want to fail the package deliberately.
Now we have to provide the destination table details. So double-click on the OLE DB Destination and provide the required information. As you can see from the below, we selected the Duplicate Employee Error Handler table.
Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click OK to close the above window.
Configure Error Handling in SSIS
Drag and drop another OLE DB destination to configure Error Handling to store the error output. Next, drag the Red line from Data Conversion (Error Output) to OLE DB Destination 1.
Dragging the Error output on OLEDB Destination will open the following window. You have to use this window to configure the error handling. By default, all the columns are pointed to the Fail component property.
Error Output provides three SSIS Error Handling options:
- Fail Component: If there is an error, then the transformation, source, destinations, etc., will fail.
- Ignore Failure: It will ignore the row’s failures.
- Redirect Rows: It returns the successful rows to the specified destination and failed rows to failed output. It will not fail the task.
Please change the option to Redirect Row for both Errors and Truncation columns.
Click OK to close the Configure Error Output window.
Double-click on the OLE DB Destination1 will open the Destination Editor. For the time being, we are creating a new table using the New button.
Click on the Mappings tab to verify whether the source columns exactly mapped to the destination columns or not.
Click OK to finish configuring the OLE-DB destination. The below screenshot is the final SSIS Error Handling package.
Let us run the Error Handling In SSIS package. As you can see from the below screenshot, it is redirecting 11 rows (successful) to OLE DB Destination and 3 rows to the error output.
Please open the Management Studio to check the package result.
Next, open the second SQL table to check the records returned by the Error.
