Getting Error Column Name in SSIS

SSIS automatically creates two columns on the Error Output from dataflow components – ErrorColumn and ErrorCode. ErrorColumn contains the column ID of the erroneous column. Column ID is a number that is unique to a dataflow within the package. Similarly ErrorCode contains the error number that caused the row to be rejected. At the moment, SSIS by default does not provide ErrorColumn name and ErrorCode description. These can be derived using the custom transformation component described below.

Here Extracting Data from CSV File, there have tree column and columns are string data type. So I use data conversion transformation for converting string to particular data types. Finally I loaded Data into SQL table. If I get any error in Data conversion it will write that row into another SQL table.

If you use an error output then you will find that you get given an error number and an error column (i.e. the column in which the error occurred) but you do not get an error description. Well not to worry because you can get the error description using a script component.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.ErrorDesc =this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
}
So as I said, you can get the error using a script component. You can use script component in the data flow – I’ve called it “ErrorDesc”. It’s a synchronous component and the first thing you will need to do is add a column to the output that is going to contain the error description.

SSIS needs to keep track of that “column” at each point throughout the flow and treat it as though it’s unique. It’s an integer and it’s unique throughout the data flow. When buffers are reused, the Lineage ID doesn’t change – it’s the same column at the input and output. When buffers are copied, a new column is created – which gets a new (unique) Lineage ID. Beyond needing to understand how to treat flow direction (ex: input column vs. output column).

Let’s say you have a row with a string column “Money” which is the Currency Data type on a table.

  • Input (Lineage ID = 16)
  • Source Error Output (Lineage ID =16)
  • Good Output (Lineage ID = 56)

When we get error in data conversion column Money then lineage id = 56 write into ErrorColumn value. We can’t get simply column by LineageID using “FindColumnByLineageID” in a script component because Lineage ID 56 going through the success or good output we have gets errorcolumn in Error Output.

1

If we used “FindColumnByLineageID” method then we want to get both pipelines in single component. Here I have built a custom transformation component that has two inputs and two outputs.

2

Here I described step wise process of creating custom transformation component for reading Error column Name using error column field value.

Step 1: Create CSV source file. Here I have three columns that are Name, Money, ID and each is string data type.

3

Step 2:  Convert money and ID column to Currency and Int data type so I used data conversion component .When I get error in data conversion the row will redirect into the error output. You can configure this option through the Configure Error Output Button.

4

Step 3: Adding new custom transformation component at bottom of data conversion component. Select an input and an output to connect the component. From the figure you can see good output connected to success input and error output connected to Error Input.

5

6

Connect the Error Output to Error input component.

7

8

Step 4: Create two destination connections one for storing success data rows and other for Error data rows. Connect success output from custom “Error column name” component to SQL server Good Output table destination and error output to SQL server Error Output table destination.

9

10

11

Step 5: add Data viewer for Error Output Data flow. You can see from figure there have extra two column came that are ErrorColumnName and ErrorDescription.

12

Also you can get error output rows at using Data viewer.

13

This code is provided in the download, but for quick access / reference I’m also including it here.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
namespace SSIS.Components.ErrorColumName
{
[DtsPipelineComponent
(DisplayName = “Error Column Name”,
Description = “First Drag and drop Error Input (Red arrow) and then success input (Green arrow)”,
ComponentType = ComponentType.Transform,
IconResource = “SSIS.Components.ErrorColumName.Error.ico”)
]
public class ColumnName : PipelineComponent
{
private int[] inputBufferColumnIndex;
private int[] outputBufferColumnIndex;
private string MaskedData;
#region Design Time Methodspublic override void OnInputPathAttached(int inputID)
{
try
{
// Call base method
base.OnInputPathAttached(inputID);IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSVirtualInput100 vInput = input.GetVirtualInput();
input.Name = input.Name; // Rename with Unique name
input.Dangling = false; // Can not be unattached// Retrieve and store Column Information
foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
{
SetUsageType(inputID, vInput, vCol.LineageID, DTSUsageType.UT_READONLY);
}
// Create another Input if user want to attached another component
if (ComponentMetaData.InputCollection.Count == 1)
{
IDTSInput100 nextInput = ComponentMetaData.InputCollection.New();
nextInput.Dangling = true; // Can be unattached
nextInput.Name = “Temporary Input”; // Temporary Name
}

}
catch (Exception ex)
{
ComponentMetaData.FireWarning(0, ComponentMetaData.Name, “OnInputPathAttached : ” + ex.Message, “”, 0);
}
}
public override void ProvideComponentProperties()
{
// Set component information
ComponentMetaData.Name = “Error Column Name”;
ComponentMetaData.Description = “A SSIS Data Flow Transformation Component To Provide Error column Name from ErrorColum column”;
ComponentMetaData.ContactInfo = “”;

// Reset the component
base.RemoveAllInputsOutputsAndCustomProperties();

//Add input objects
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = “Error Input”;
input.Dangling = false;
input.Description = “This is for Initialize component.”;

IDTSInput100 input2 = ComponentMetaData.InputCollection.New();
input2.Name = “Success Input”;
input2.Dangling = false;
input2.Description = “This is for Initialize component.”;

// Add output objects
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = “Error Output”;
output.Description = “Contains Error columns. Gets set automatically. Extra One Column that is ErrorColumnName Get from this.”;
output.SynchronousInputID = input.ID; //Synchronous transformation!

////Add error objects
//IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
//errorOutput.Name = “Error”;
//errorOutput.IsErrorOut = true;

}

public override DTSValidationStatus Validate()
{
if (ComponentMetaData.InputCollection.Count == 2 && ComponentMetaData.OutputCollection.Count == 1)
{
IDTSInput100 input = ComponentMetaData.InputCollection[1];
IDTSOutput100 output2 = ComponentMetaData.OutputCollection.New();
output2.Name = “Success Output”;
output2.Description = “Contains columns. Gets set automatically.”;
output2.SynchronousInputID = input.ID; //Synchronous transformation!

}

Int16 i = 0;
IDTSOutput100 output = ComponentMetaData.OutputCollection[i];

bool IsExist = false;
foreach (IDTSOutputColumn100 OutputColumn in output.OutputColumnCollection)
{
if (OutputColumn.Name == “ErrorColumnName”)
{
IsExist = true;
}
}

if (!IsExist)
{
IDTSOutputColumn100 outputcol1 = output.OutputColumnCollection.New();
outputcol1.Name = “ErrorColumnName”;
outputcol1.Description = “Error Column Name “;
outputcol1.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

IDTSOutputColumn100 outputcol2 = output.OutputColumnCollection.New();
outputcol2.Name = “ErrorDescription”;
outputcol2.Description = “Error Description “;
outputcol2.SetDataTypeProperties(DataType.DT_STR, 500, 0, 0, 1252);
}

return DTSValidationStatus.VS_ISVALID;
}

// Covers VSNEEDSNEWMETADATA from Validate()
public override void ReinitializeMetaData()
{
// clean-up
if (!ComponentMetaData.AreInputColumnsValid)
{
ComponentMetaData.RemoveInvalidInputColumns();
}

base.ReinitializeMetaData();
}

// Disallow adding output
public override IDTSOutputColumn100 InsertOutputColumnAt(int outputID, int outputColumnIndex, string name, string description)
{
bool cancel = true;
ComponentMetaData.FireError(0, ComponentMetaData.Name, “Output columns cannot be added to ” + ComponentMetaData.Name, “”, 0, out cancel);

throw new Exception(“Output columns cannot be added to ” + ComponentMetaData.Name, null);
return null; // Not exactly necessary, but a good practice
}

#endregion Design Time Methods

#region Run Time Methods

public override void PreExecute()
{

Int16 i =0;
IDTSInput100 input = ComponentMetaData.InputCollection[i];
inputBufferColumnIndex = new int[input.InputColumnCollection.Count];

for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
IDTSInputColumn100 column = input.InputColumnCollection[x];
if (column.Name == “ErrorColumn”)
{
inputBufferColumnIndex[0] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
}
if (column.Name == “ErrorCode”)
{
inputBufferColumnIndex[1] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
}
}

IDTSOutput100 output = ComponentMetaData.OutputCollection[i];
outputBufferColumnIndex = new int[output.OutputColumnCollection.Count];

for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn100 outcol = output.OutputColumnCollection[x];

// A synchronous output does not appear in output buffer, but in input buffer
outputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer, outcol.LineageID);
}
IDTSInput100 input1 = ComponentMetaData.InputCollection[1];

MaskedData = input1.InputColumnCollection.Count.ToString();
}

// The actual data masking
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if (input.Name == ComponentMetaData.InputCollection[0].Name)
{
if (!buffer.EndOfRowset)
{
while (buffer.NextRow())
{
if (!buffer.IsNull(0))
{
buffer.SetString(outputBufferColumnIndex[0], MaskData(buffer.GetInt32(inputBufferColumnIndex[0])));
buffer.SetString(outputBufferColumnIndex[1], ComponentMetaData.GetErrorDescription(buffer.GetInt32(inputBufferColumnIndex[1])).ToString());
}

}
}
}
}

#endregion Run Time Methods

#region Data ErrorColumnName

// Provides a basic data masking with scrambling column content
public string MaskData(Int32 Lineage)
{

MaskedData = “”;
IDTSInput100 input = ComponentMetaData.InputCollection[1];
IDTSInputColumn100 column1 = input.InputColumnCollection.GetInputColumnByLineageID(Lineage);
MaskedData = “”+column1.Name.ToString()+””;
return MaskedData;
}

#endregion Data ErrorColumnName

}
}

Download the sample Code.
Download the sample Package.
SSIS 2012 Custom Component Install Guidelines.

Advertisements
Comments
  1. Abdul Kahadir says:

    Hi Naseer,
    i need your email id because i want this concept.
    i dont know about this concept..

  2. gurvinder says:

    Hi Naseer,
    I tried to use this for my project , but am getting a error message when in connect the error output from the DataConversion task to the script component , that “All the available inputs on the target component are connected to the outputs.Edit this component to define inputs before proceeding.”
    i am using ssis 2012 , can you help me on this.
    Thanks
    gurvi

  3. Raghib says:

    the custom transformation component you have used in step 3, is it only in SSIS 2012 or I can find it in SSIS 2008?

  4. Johnb525 says:

    This website was how do you say it? Relevant!! eegkadbegede

  5. Marcos says:

    Please, where should I put the folder ErrorColumName.

  6. […] Getting Error Column Name in SSIS | Naseer Muhammed AG – SSIS automatically creates two columns on the Error Output from dataflow components – ErrorColumn and ErrorCode. ErrorColumn contains the column ID of the erroneous …… […]

  7. Vishal says:

    Hello,
    I am getting the Error : [SSIS.Pipeline] Error: No object exists with a lineage ID of 88.

    Can you please suggest the solution for this.

    Thanks.

    • Hi Vishal,

      the lineage id should be change after adding data flow component. so please look at lineage id 88 one step before. ex : there are three component OLE DB SRC, DER Column , OLE DB DESC. assume raising error on DER Column then please look into the OLE DB SRC to DER Column (Data path editor) you will get to know the line age id of column name.

      • Kimaoui says:

        Hi Naseer,

        I have same error as Vishal.
        Error: [SSIS.Pipeline] Error: No object exists with a lineage ID of 0.

        I get this error from OLE DB DEST Error Ouput.

        Can you explain in detail your solution please ?

        I’m stuck on a project because of this error.

      • toddlangdon says:

        Should this Error Column Name component work with destination component failures, as well? Your examples are with a data conversion component. I am redirecting failures on a destination component to an Error Column Name component and also getting the same error about no lineage existing. I am using SQL Server 2012 SP3 (yes, I know column name is available in SQL 2016). See my exception below:

        Error: 0xC00470B2 at Migrate Providers, SSIS.Pipeline: No object exists with a lineage ID of 1199.
        Error: 0xC0047062 at Migrate Providers, Error Column Name [41]: System.Runtime.InteropServices.COMException (0xC00470B2): Exception from HRESULT: 0xC00470B2
        at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumnCollection100.GetInputColumnByLineageID(Int32 lLineageID)
        at Clearpoint.SSIS.Components.ErrorColumnName.ColumnName.MaskData(Int32 Lineage)
        at Clearpoint.SSIS.Components.ErrorColumnName.ColumnName.ProcessInput(Int32 inputID, PipelineBuffer buffer)
        at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

  8. Sanandh says:

    Hi
    i want learn more about SSIS,SSRS , So i need your mob no

  9. Hoang Tran says:

    Thank you very much.

  10. Abhishek says:

    This can also be done in the following manner

    For SQL Server 2016 -> Microsoft added some new extra features in Error handling like LineageIdentificationString and Error Column Description..Make it preety easier to find error column

    https://blogs.msdn.microsoft.com/ssis/2015/11/27/error-column-improvements-for-ssis-data-flow-updated-for-ctp3-3/

    https://msdn.microsoft.com/en-us/library/ms345163.aspx

    For 2005/2008/2012/24

    There is a very good article written by Benny Austin to resolve this issue. Its preety much easy , lesser coding .

    https://bennyaustin.wordpress.com/2011/02/04/ssis-name-of-errorcolumn/

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s