SSIS - How to Get Error Column Name In SSIS

Scenario: 

We are loading data from source table to destination table. The data has to be converted to destination data types before we insert the data into Destination table. We are using Data Conversion to convert the data types of input columns. If  data is not able to convert to data type we have specified in Data Conversion Transformation we want to redirect those records. After redirecting those records, we want to log the information with Column Name which became the reason for redirection.

Solution : 

Let's create SSIS Package and see if we can get error column name without doing any custom coding.

Step 1: 

Create SSIS Package and bring data flow task to Control Flow pane. Use the OLE DB Source to extract data for Source Table. Here is definition of our Source Table with couple of records

CREATE TABLE [dbo].[SourceTable](

[Name] [varchar](100) NULL,

[SaleDate] [varchar](50) NULL

)

Insert into dbo.SourceTable 

Values 

('Aamir','2013-12-03 10:19:56.887'),

('Raza','Test Date')

We want to convert SaleDate to DateTime before insert into Destination table. If any value will not be able to convert then we want to redirect the row from Data Conversion Transformation.

Let's create Data Flow Task with all transformation as shown below

Redirect the Rows If truncation or Error occur

Bring Multicast and connect to the Error Output of Data Conversion. After that put Data Viewer to see the redirected rows as shown below

After executing our package we can see that the one row was converted successfully and moved to destination but one row could not converted to required data types and it is redirect to multicast. In above snapshot we can see ErrorCode, ErrorColumn (Lineage ID) Number and ErrorCode Description but we can't really tell it happened because of SaleDate Column or Name column. 

Read Package File to Get LineageIDs 

Our goal is to get the column name, to do that we have to read the package file(.dtsx) and get the Lineage IDs for columns. The lineageID can be same for different columns in different Tasks. We will also read the Task Name so each record will be unique with combination of Task Name.

Step 2: 

We will use Data Flow Task as our very first Task to read the Package definition and extract Lineage IDs, Column Names and Task Names and save these records in Cache Transformation. So we can use this data set anywhere in our package by using Lookup transformation to extract Error column Names by joining on Error Column( LineageID) and Task Name.

Let's drag data flow task to Control Flow pane and then bring Script Component. As we have to read the definition of SSIS Package. I created a variable and saved the location of same package I am working on.

Variable Name : VarPackagePath

Type : String

Value : Where ever you have saved your package.

Configure Script Component as Source and Provide the variable value for SSIS Package Path.

Go to Input and Output Columns and add columns as shown 

Hit on Edit Script and paste below script , The only part I wrote is in Red

/* Microsoft SQL Server Integration Services Script Component

*  Write scripts using Microsoft Visual C# 2008.

*  ScriptMain is the entry point class of the script.*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Collections;

using System.Xml;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void PreExecute()

    {

        base.PreExecute();

        /*

          Add your code here for preprocessing or remove if not needed

        */

    }

    public override void PostExecute()

    {

        base.PostExecute();

        /*

          Add your code here for postprocessing or remove if not needed

          You can set read/write variables here, for example:

          Variables.MyIntVar = 100

        */

    }

    public override void CreateNewOutputRows()

    {

        //Declare Variables

        String TaskName;

        String ColName;

        Int32 ColLineageID;

        String ColKey;

        

        //Read the Package File

        XmlDocument PackageFile = new XmlDocument();

        PackageFile.Load(Variables.VarPackagePath);

        //Create Hash Table 

        Hashtable ColKeyTable = new Hashtable();

        XmlNamespaceManager NameSpcMgr = new XmlNamespaceManager(PackageFile.NameTable);

        NameSpcMgr.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");

        foreach (XmlNode childnode in PackageFile.SelectNodes("//*[@lineageId != '' and @name != '']"))

        {

            XmlNode ExecutableNode = childnode.SelectSingleNode("ancestor::DTS:Executable[1]", NameSpcMgr);

            TaskName = ExecutableNode.SelectSingleNode("DTS:Property[@DTS:Name='ObjectName']", NameSpcMgr).InnerText;

            ColName = childnode.Attributes["name"].Value;

            ColLineageID = Convert.ToInt32(childnode.Attributes["lineageId"].Value);

            ColKey = TaskName + ColName + ColLineageID;

            if (!ColKeyTable.ContainsKey(ColKey))

            {

                ColKeyTable.Add(ColKey, DBNull.Value);

                Output0Buffer.AddRow();

                Output0Buffer.ColLineageID = ColLineageID;

                Output0Buffer.ColName = ColName;

                Output0Buffer.TaskName = TaskName;

            }

        }

     

    }

}

Step 3: 

Bring Cache Transformation and connect Script component to it and configure Cache Transformation as shown.

Choose the Columns and Index Position

Put Data Viewer between Script component and Cache Transformation and see if LineageID,Column Name and Task Name is read correctly.

Step 4: 

As we have the Task Name, Column Name and Lineage ID , we can use this information in our actually data flow where rows are redirecting and get the column Name by joining on Task Name and Lineage ID.

Add Derived Column and Add Column "DER_DFT_Name". The value of this column will be the name of Data Flow it is in. We will be using this  in Lookup to find out Error Column Name for any record which is redirected in this data flow.

Final Output :

Connect the Matching output of Lookup to Multicast and put Data Viewer. Now we should be able to find out the Column Name which produces error for redirection of row.

Summary:

Quick Summary, we have to read our Package file in First Data flow and then use that information ( Task Name, Column Name, LienageID) where ever we need that in our SSIS Package. We can create a template package with First Data Flow to read package file and save information in Cache Transformation for us to use later.

OR
we can create a meta data table and read our all packages and save LineageIDs,Column Names and Task Names and then we do not have to read the package file inside the package.