Tutorial Gateway

  • C Language
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • Js

Error Handling in SSIS

by suresh

SSIS Error Handling : In SQL Server Integration Services, errors might occur when we are extracting data from a source, or loading data into a destination, or when you are performing a transformation. This is because of the unexpected data, and it is a bad practice to fail the package.

To handle these situations, SSIS introduced the concept called Error Handling. In this article, we will show you, How to configure Error Handling in SSIS with an example.

For the SSIS Error Handling demonstration purpose, we will Export data present in the Employee.xls to SQL Server database table, and configure error handling at Data Conversion. Below screenshot will show you the data present in the Employee Excel file

Error Handling in SSIS 1

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 as Error Handling in SSIS.

Error Handling in SSIS 2

Double click on it will open data flow tab. Now, Drag and drop Excel Source, Data Conversion Transformation, OLE DB Destination from SSIS toolbox into the data flow region

Error Handling in SSIS 3

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 selecting the Employees.xls file present in the file system.

Error Handling in SSIS 4

From the above screenshot, you can observe that we are selecting the Employee_Destination table present in the Employee.xls file

Error Handling in SSIS 5

Click ok and drag the blue arrow from Excel Source to Data Conversion Transformation. This will allow the transformation to use the source data.

Next, Double click on the Data Conversion Transformation to edit and convert our source columns data type to required data type with length.

Here we are leaving the Occupation length as 255 because we want to fail the package deliberately.

Error Handling in SSIS 6

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.

Error Handling in SSIS 7

Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns.

Error Handling in SSIS 8

Click OK close the above window.

Configure Error Handling in SSIS

Drag and drop another OLE DB destination to store the error output. Next, drag the Red line from Data Conversion (SSIS Error Output) to OLE DB Destination 1.

Error Handling in SSIS 9

Dragging the Error output on Ole DB Destination will open the following window. You have to use this window to configure the error handling in SSIS. By default, all the columns are pointed to Fail component property

Error Handling in SSIS 10

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 rows failures.
  • Redirect Rows: It returns the successful rows to the specified destination and failed rows to failed output. This will not fail the task.

Please change the option to Redirect Row for both Errors, and Truncation columns.

Error Handling in SSIS 11

Click OK to close the Configure Error Output window.

Error Handling in SSIS 12

Double-click on the OLE DB Destination 1 to open the to OLE DB Destination Editor. For the time being, we are creating a new table using the New button.

Please refer OLE DB Destination in SSIS article to understand the available options in OLE DB Destination editor

Error Handling in SSIS 13

Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns or not.

Error Handling in SSIS 14

Click OK to finish configuring the OLE DB destination. Below screenshot is the final package.

Error Handling in SSIS 15

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.

Error Handling in SSIS 16

Please open the SQL Server Management Studio to check the result.

Error Handling in SSIS 17

Next, open the second table to check the records returned by the Error.

Error Handling in SSIS 18

Thank You for Visiting Our Blog

Placed Under: SSIS

Stay in Touch!

Sign Up to receive Email updates on Latest Tutorials

  • C Programs
  • Java Programs
  • SQL FAQ’s
  • Python Programs
  • SSIS
  • Tableau
  • JavaScript

Copyright © 2019 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy