Saturday, 21 January 2017

Using Re-Direct Row of OLEDB Destination to Solve PK Problem

Design is very important, in case of SSIS. Here in this article we are trying to discuss about a scenario related to error handling by re-directing rows in data flow.

Case Scenario
We have a flat file and a table. We need to transfer data from flat file to Table object. Simple but have some twist in it.

Here is our Table structure:

CREATE TABLE [dbo].[tbl_Employee_Deails]
    [EmpId]     INT             NOT NULL PRIMARY KEY,
    [EmpName]   VARCHAR(50)     NOT NULL,
    [EmpSal]    NUMERIC(18,2)   NOT NULL CHECK([EmpSal]>5000)

If you have closer look into the table we can find there is a Primary Key on column name [EmpId] and a Check Constraint in [EmpSal]. The check constraint check the value of the columns should be grater then 5000.

Now we Insert a records in the table objects:

INSERT [dbo].[tbl_Employee_Deails]
       ([EmpId], [EmpName], [EmpSal])
VALUES (101, 'Pryo Bhattacharya', 8000.00);

So we can assume that the destination have some records
SELECT * FROM [dbo].[tbl_Employee_Deails];

EmpId       EmpName                                            EmpSal
----------- ----------------------------------------------------------
101         Pryo Bhattacharya                                  8000.00

(1 row(s) affected)

Now we have a Flat file and we need to Transfer data from flat file to Designation table.
Here is the sample of flat file.

If we look at the flat file we can find there is a record...

101;Pryo Bhattacharya;8000

Which is already exists in the table and causes Primary Key Violation.

SSIS Package

[Inser Records [23]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E2F  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E2F  Description: "Violation of PRIMARY KEY constraint 'PK__tbl_Empl__AF2DBB99B94F31D8'. Cannot insert duplicate key in object 'dbo.tbl_Employee_Deails'. The duplicate key value is (101).".

How to Solve it
To solve it we can use Re-Direct row of Error Output in OLEDB Destination. Using an OLEDB command we can Delete the Duplicate records and Re-Insert it again by using another OLEDB Destination. It is just simple to build.

In OLEDB Command we used

DELETE FROM [dbo].[tbl_Employee_Deails] WHERE [EmpId] = ?;

The solution is simple but generates another problem. If we look the Data Flow carefully we can find the WARNING of second OLEDB Destination

What it says.
Row Sent to Error Output(s) will be lost. Add new data flow transformation or destination....

Understand the Second Scenario and Solve the WARNING
To understand it we have to little change in source Flat file by adding another records to violate check constraint.

Now we have two problems. One is PK and another is CHECK constraint. To run the modified solution, we can solve the Primary key problem but Unable to TRAP Check constraint Error If the setting of second OLEDB Destination Error Output Error is Redirect Row or Ignore Failure.

Now check the Destination

SELECT * FROM [dbo].[tbl_Employee_Deails];

EmpId       EmpName                                            EmpSal
----------- -------------------- ---------------------------------------
101         Pryo Bhattacharya                                  8000.00
102         Deblina Bhattacharya                               7000.00

(2 row(s) affected)

There is No trace of EmpID 103.

To solve it we have to make little changes in second Destination

How we can capture the second Error.

Hope you like it.

Posted by: MR. JOYDEEP DAS