SSIS Data Flow Error Handling with Description and Column Name

Introduction

Finding the Error Description is a simple one line code but finding Error Column name is not so much simple. Microsoft made it easy from SQL 2016, when modified API comes. But before 2016 it’s really a difficult job to find the Error Column Name by using Script Task and Script Component.

This article is trying to provide a simple step by step solution to find the Error Description and Error column name.

Scenario

We are trying to insert records from Flat file to our SQL Table. The flat file has two columns named “roll” and “name”. Sometimes name contains huge text and don’t support by our Table object (Size of the name column in table object is Varchar(50)) and hence generate Error. We are trying to display Error description and the column name that generate the Error.

Control/Data Flow Details

Control Flow – Get Linage ID Task

This Script Task is used to grade the Linage id at run time and save it in a text file.

What to do:

1.    Create a text file C:\temp\write.txt

2.    Create two package level variable named User::execsObj and User::lineageIds as Object type and pass them as Read Write variable of script task.

3.    In Script Task add assembly named Microsoft.SqlServer.DTSPipelineWrap

Namespace needed

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using System.Windows.Forms;

using System.Collections.Generic;

using System.IO;

Script Task Code in C#

public void Main()

{

 // Grab the executables so we have to something to iterate over, and initialize our lineageIDs list

// Why the executables?  Well, SSIS won't let us store a reference to the Package itself...

Dts.Variables["User::execsObj"].Value =(Package)Dts.Variables["User::execsObj"].Parent).Executables;

Dts.Variables["User::lineageIds"].Value = new Dictionary<int, string>();

lineageIds = (Dictionary<int, string>)Dts.Variables["User::lineageIds"].Value;

Executables execs = (Executables)Dts.Variables["User::execsObj"].Value;

ReadExecutables(execs);

// Just proof of concept to see the results before you dedicate your time to the solution

// Delete this code in your actual implementation

using (StreamWriter writetext = new StreamWriter(@"C:\temp\write.txt", true))

        {

                foreach (var kvp in lineageIds)

                writetext.WriteLine(kvp.Key + " : " + kvp.Value);

        }

            Dts.TaskResult = (int)ScriptResults.Success;

}

        

private void ReadExecutables(Executables executables)

{

  foreach (Executable pkgExecutable in executables)

    {

     if (object.ReferenceEquals(pkgExecutable.GetType(),            

                 typeof(Microsoft.SqlServer.Dts.Runtime.TaskHost)))

                {

                    TaskHost pkgExecTaskHost = (TaskHost)pkgExecutable;

                    if (pkgExecTaskHost.CreationName.StartsWith("SSIS.Pipeline"))

                    {

                        ProcessDataFlowTask(pkgExecTaskHost);

                    }

                }

     else if (object.ReferenceEquals(pkgExecutable.GetType(), 

                 typeof(Microsoft.SqlServer.Dts.Runtime.ForEachLoop)))

                {

                    // Recurse into FELCs

                    ReadExecutables(((ForEachLoop)pkgExecutable).Executables);

                }

      }

 }

private void ProcessDataFlowTask(TaskHost currentDataFlowTask)

{

   MainPipe currentDataFlow = (MainPipe)currentDataFlowTask.InnerObject;

   foreach (IDTSComponentMetaData100 currentComponent in 

                  currentDataFlow.ComponentMetaDataCollection)

            {

              // Get the inputs in the component.

              foreach (IDTSInput100 currentInput in currentComponent.InputCollection)

                  foreach (IDTSInputColumn100 currentInputColumn in 

                          currentInput.InputColumnCollection)

                        lineageIds.Add(currentInputColumn.ID, currentInputColumn.Name);

               // Get the outputs in the component.

               foreach (IDTSOutput100 currentOutput in currentComponent.OutputCollection)

                    foreach (IDTSOutputColumn100 currentoutputColumn in 

                                currentOutput.OutputColumnCollection)

                        lineageIds.Add(currentoutputColumn.ID, currentoutputColumn.Name);

            }

}

Adding Script Component in Data Flow Task

Now add script component in data flow task.

What to do:

1.    Just pass the variable named User::lineageIds as Read variable

2.    Script Component input columns as ErrorCode and ErrorColumn

3.    Script Component output column as ErrorDescription and ErrorColumnDescription with DT_STR data type.

Name space

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Collections.Generic;

Script Component Code in C#

public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        Dictionary<int, string> lineageIds = (Dictionary<int, string>)Variables.lineageIds;

        int? colNum = Row.ErrorColumn;

        if (colNum.HasValue && (lineageIds != null))

        {

            if (lineageIds.ContainsKey(colNum.Value))

                Row.ErrorColumnDescription = lineageIds[colNum.Value];

            else

                Row.ErrorColumnDescription = "Row error";

        }

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

     }

Hope you find it informative.

Posted by: MR. JOYDEEP DAS