Error Description in Error Output Re-Direct Row

Introduction

In this article we are trying to provide simple error code description by script task. I try to make the script task as simple as possible that every developer can use it. The code of the Script task is just a single line code written in C# language. Hope it will be useful.

The Data Flow Task of the Package

Configuring the Script Task

Step – 1 [ Input Columns  Tab ]

Step – 2 [ Inputs and Outputs Column Tab ]

Select the Output columns and click the Add column Button to add output columns. We must change the property of Newly Output columns data type and size.

Step – 3 [ Edit Script Button ]

Need a single line code here.

public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

     Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    }

Step – 4 [ Text File Destination ]

Step – 5 [ The Error Output Flat file ]

Error Description Details from Microsoft

https://msdn.microsoft.com/en-us/library/ms345164(v=sql.100).aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS

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

Introduction

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  

SSIS Problem with Buffer and BLOB data type when in Migration

Introduction

In this article we are trying to discuss a about a common memory related problem at least all SSIS developer faced. This type of problem makes developer life little disturbed …just joking.

What is the Warning Message that we faced

We must remember that warning sometimes convert to Error. That’s the reason, I am writing this warning message in red color. I personally treat warning as an error as I try to remove warning from SSIS package.

[DTS.Pipline] Information: The buffer manager detected that the system was low virtual memory, but was unable to swap out any buffer. 4 buffer was considered and 4 was locked………

Danger … Danger … Danger …The SSIS package can stop within any moment of time.

Lot of the developer has the same experience when they are working with BLOB data type in SSIS package.

How we solved it

There are lot of process to solve this issue. But the thing is that which approach is best suitable for your SSIS package that you have to decide it.

Anyway, here I am providing three approach to solve it.

Approach - 1

The approach is increasing physical memory or virtual memory - can solve the problem. But it is not possible every time due to tight schedule, budget etc.

So, as a developer we have to provide solution within all this constraint and hence my next two approach is come into the picture.

Approach - 2

The BESTapproach that I recommended is load the data step by step. Means not loading the entire data at a time but go to the incremental load approach.

We must retrieve a limited amount of data at apoint of time from source not retrieve entire source data. It depends on our logic, how you retrieve data from source step by step way.

If the no of record count is limited this type of error generally not occurs. But it also depends on data type and size.

If anyone ask me for how many number of records can be retrieved at a time then my answer to him is I have no idea. Check your source table, data type …data volume, physical REM, virtual memory.

Approach - 3

Now SSIS is also provide us something to solve this problem.

SSIS provide two properties that can solve problem related to Virtual memory buffering problem.

The data flow task have this property named BufferTempStorageParh and BLOBTempStoragePath.

[Right Click the Data Flow task and choose Property… In the property window… in the MISC section you can find those two property named BufferTempStoragePath and BLOBTempStoragePath.]

By default the value of this property is BLANK.

Working with BufferTempStoragePath:

The property named BufferTempStoragePath, works when the problem of virtual memory comes.

I mean to say that in the condition of Low Virtual Memory we have to set the BufferTempStoragePath property values.

Here in the BufferTempStoragePath, we have to specify a path where the memory is dumped when low virtual memory problem arise. For Example E:\MyMemeoryDump.

Working with BLOBTempStoragePath

There is another property is equivalent to important when we are migrating data from one Server DB to another server DB with Binary Large Object (BLOB) exists in the source DB.

To working with BLOB data type data we must understand how SSIS works with it.

It just four steps activity.

Step-1: Read the BLOB contents from Source.

Step-2: Write the BLOB contents to the temporary file.

Step-3: Read the BLOB from temporary file.

Step-4: Write the BLOB contents to the destination.

So by default SSIS used C:\Windows\temp location for this activity.

Recommendation

Drive C: is active for Operating system, so it is not a good idea to put the path for property named BufferTempStorageParhandBLOBTempStoragePath in drive C:

So I recommended that don’t use the drive C and the drive that contains the Database. Use any locale good size drive for that purpose.

Hope you like it.

Posed by: MR. JOYDEEP DAS