Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

Error Handling in SSIS

SSIS Error Handling : In SQL Server Integration Services or SSIS, errors might occur when we are extracting data from a source, or loading data into a destination, or when you are performing a transformation. It 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. Let’s see 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 the SSIS data flow tab. Now, Drag and drop Excel Source, Data Conversion Transformation, OLEDB 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 choosing 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. 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.

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 exactly mapped to the destination columns.

Error Handling in SSIS 8

Click OK to close the above window.

Configure Error Handling in SSIS

To 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 SSIS Error output on OLEDB 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 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.

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 Destination1 will open the Destination Editor. For the time being, we are creating a new table using the New button.

Error Handling in SSIS 13

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

Error Handling in SSIS 14

Click OK to finish configuring the OLE-DB destination. The below screenshot is the final SSIS Error Handling 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 SSIS Error Handling package result.

Error Handling in SSIS 17

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

Error Handling in SSIS 18

Filed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy