SSIS – Error Handling Design Pattern in Data Flow

Introduction

I have a request to handle the Error in Destination of data flow from my friends circle. No data source is perfect. So when we design the SSIS package we must take care of Error handling portion also.

The error portion of the OLEDB Destination ma came for Primary Key Violation, CHECK Constraint Violation etc. But if we design the package like it takes data validation before inserting into destination. Sounds good but it is an over head for the package.

Some approach is removing Primary key and all the Constraint before inserting records and after inserting re-create them. It also sounds good but if the garbage data insert in our destination table we are unable to create the constraint and another task we need to perform is removing the garbage from destination.

So approach is many but we have to carefully choose them before implementation which one is suited our situation.

In this article we are not going to describe all components or task in the package and we hope that the reader’s knows the Data Flow error handling process of SSIS.

Hope this article will be informative.

The Scenario

The scenario is simple. Retrieve the data from flat file and insert it into destination table. We have a flat file named Employee Details. It contains the employee data but data is not perfect over here. Some garbage is there.

We have a destination table and it contains a Primary key and Check constraint over Employee Grade. So if any kind of Primary Key, Data Type, Check Constraint Violation is not allowed over here.

Hope you understand the Scenario.

The Design Approach

If we closer look at the Design Approach we find that all the Redirection of Error output is in a UNION ALL transform. Now we can store the output of Error from Union All transform to anywhere like Fat File or Database Table.  

The Destination Table Objects Definition

IF OBJECT_ID(N'[dbo].[tbl_EmployeeDetails]', N'U') IS NOT NULL

   BEGIN

       DROP TABLE [dbo].[tbl_EmployeeDetails]

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeDetails]

   (

       EmpId     INT          NOT NULL PRIMARY KEY,

       EmpName   VARCHAR(50)  NOT NULL,

       EmpDesig  CHAR(1)      NOT NULL,

       EmpDepart VARCHAR(50)  NOT NULL

   );

GO

-- Check Constraint --

ALTER TABLE [dbo].[tbl_EmployeeDetails]

ADD CONSTRAINT chk_EmpDesig CHECK (EmpDesig IN ('A', 'B', 'C'));

GO

The Flat File Sample

If we closer look we can find that the Department “D” Makes the CHECK Constraint Violation in the Destination and Employee Id “Four” make error in Data Conversion Task.

Run the Package and Observe

Hope you like it.

Posted by: MR. JOYDEEP DAS