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.

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.

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.

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.

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.

Connect the Error Output to Error input component.

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.

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.

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

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.