How to get error description in SSIS dataflow?

In SSIS, there is no direct method to get the error description when error occurred during data Load and transformation.

We can only redirect the failed records along with Error Code to log file (or any destination component). Since the Error Code is not very useful, we may need to store the Error Description along with the failed row. Even though there is no direct mechanism there is a workaround to achieve. The workaround is to add Script Component.

Let’s explore with an example.

Below is the sample package, designed to load Sales Order details from flat file to Sales database. Note the Script Component “SC-GetErrorDesc” placed in between Sales Database and Log Error tasks.

clip_image001

The next screen shows the configuration of Script Component to get the Error Description.

1. Choose the ErrorCode from the available Input columns.

clip_image003

2. Add new output column (ErrDesc) in the Inputs and Outputs section

clip_image005

3. Edit the script

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
Row.ErrDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
}

4. Finally the result from Script component is stored in the flat file. When you map the column you will notice a new columns named ErrDesc in the Available Input Column side, this new column is created and populated within the script component task.

clip_image006

NULL value for empty field when importing flat file in SSIS

When importing data from text file you may need to apply NULL to the field where no values present (blank). Not doing so the package may fail.

Consider a scenario; you received a text file contains sales order details, it need to be loaded in to database for further reporting or analysis.

The format of CSV file:

---------------------------------------------------------------
OrderId, OrderDate, CustomerId, Qty, UnitPrice
---------------------------------------------------------------

The table definition is:

CREATE TABLE SalesOrderDetails
(
OrderId INT NOT NULL,
OrderDate DATE NOT NULL,
CustomerId INT NOT NULL,
Qty INT NULL,
UnitPrice DECIMAL(10,4) NULL
)

The below image shows how the data in text file, if you look at the Qty field of 3rd row, there is no value present. The corresponding column in the destination table is defined as INT. So, you can’t insert non integer value into Qty column. If you do so, you may end up with the following error message.

Error code: 0x80004005.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".

clip_image002[4]

So the blank field needs to be converted to NULL before inserting into table. You can do this by enabling “retain null values from the source as null values in the data flow” setting available in the Flat File Source Editor.

clip_image004[4]