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. 

18 comments:

  1. Replies
    1. Welcome To Techbrothersit: Ssis - How To Get Error Column Name In Ssis >>>>> Download Now

      >>>>> Download Full

      Welcome To Techbrothersit: Ssis - How To Get Error Column Name In Ssis >>>>> Download LINK

      >>>>> Download Now

      Welcome To Techbrothersit: Ssis - How To Get Error Column Name In Ssis >>>>> Download Full

      >>>>> Download LINK 3C

      Delete
  2. This comment has been removed by the author.

    ReplyDelete

  3. You write this post very carefully I think, which is easily understand to me. Not only this, other post is also good. As a newbie this info is really helpful for me. Thanks to you.
    tally training
    Tally Training in Chennai
    Tally ERP 9 Training
    Tally Course
    tally classes
    Tally institute in Chennai
    Tally Training institute in Chennai
    Tally course in Chennai

    ReplyDelete
  4. Thank you so much for providing information about SSIS and its other aspects.I think its really the best tool to provide solutions to some very complex IT problems.

    SSIS Postgresql Read

    ReplyDelete
  5. I was really looking forward to using this, but unfortunately it failed on the line 'TaskName = ExecutableNode.SelectSingleNode("DTS:Property[@DTS:Name='ObjectName']", NameSpcMgr).InnerText;' with a NullReferenceException.
    Any idea why please?

    ReplyDelete
  6. Máy tính để bàn là một thiết bị không thể thiếu với thời đại công nghệ thông tin như hiện nay. Cũng như hầu hết các thiết bị điện tử khác máy tính để bàn cũng gặp một vài sự cố nhất định trong quá trình sử dụng. Vậy những sự cố đó là gì và cách khắc phục ra sao. Trong thông tin bài viết này chúng tôi sẽ chia sẻ tới bạn 5 sự cố thường gặp khi sử dụng máy tính để bàn và cách khắc phục. Nếu bạn cũng đang sử dụng máy tính để bàn để làm việc. Hãy tham khảo thông tin ở bài viết dưới đây nhé
    https://www.diigo.com/annotated/d814109f8a06d75bc78968592cdd6a54

    ReplyDelete
  7. It was a very good post indeed. I thoroughly enjoyed reading it in my lunch time. Will surely come and visit this blog more often. Thanks for sharing. brand names

    ReplyDelete
  8. Welcome To Techbrothersit: Ssis - How To Get Error Column Name In Ssis >>>>> Download Now

    >>>>> Download Full

    Welcome To Techbrothersit: Ssis - How To Get Error Column Name In Ssis >>>>> Download LINK

    >>>>> Download Now

    Welcome To Techbrothersit: Ssis - How To Get Error Column Name In Ssis >>>>> Download Full

    >>>>> Download LINK iV

    ReplyDelete