How to Find Out Which Column Caused SSIS to Fail?

In my previous post, I explained how you can find out the error description for the error id that is returned by SSIS.

Of course, that information is only half helpful, debugging the issue also involve knowing which column caused that error. The Error Column returns an integer value which is also a little bit confusing, how can I know which column is represented by this integer value?

The Error Column corresponds to the value of the lineageId attribute inside the SSIS package. As you already know, SSIS package is an xml file. The node that contains lineageId attribute may or may not has a name attribute. The value of the name attribute is the column name.

To query the lineage id and the column id, you can use this code using LINQ.

 private Dictionary<long, string> GetLineageIdAndColumnMapping(string SSISFilename)
{
    XDocument xdoc = XDocument.Load(SSISFilename);
    Dictionary<long, string> LineageColumn = new Dictionary<long, string>();

    var LineageNodes = from Nodes in xdoc.Descendants()
                       where Nodes.Attribute("lineageId") != null &&
                             Nodes.Attribute("lineageId").Value != String.Empty &&
                             Nodes.Attribute("name") != null &&
                             Nodes.Attribute("name").Value != String.Empty
                       select new
                       {
                           LineageId = Convert.ToInt64(Nodes.Attribute("lineageId").Value),
                           ColumnName = Nodes.Attribute("name").Value
                       };

    foreach (var Item in LineageNodes)
        LineageColumn.Add(Item.LineageId, Item.ColumnName);

    return LineageColumn;
}

If you are not using .Net framework 3.5, then this XPath code will also do the trick.

 private Dictionary<long, string> GetLineageIdAndColumnMapping(string SSISFilename)
{
    XmlDocument doc = new XmlDocument();
    doc.Load(SSISFilename);
    Dictionary<long, string> LineageColumn = new Dictionary<long, string>();

    foreach (XmlNode node in doc.SelectNodes("//*[@lineageId != '' and @name != '']"))
        LineageColumn.Add(Convert.ToInt64(node.Attributes["lineageId"].Value),
                          node.Attributes["name"].Value);
    

    return LineageColumn;
}

That code above, will give a dictionary of lineage id and the actual column name, now using the value from the ErrorColumn field, you can lookup the actual column name.

Someone might say, the XPath is shorter, it is better. Well, there are more than one metrics to evaluate a piece of code. LINQ enables code reviewer who are not familiar with XPath to quickly review the code, understand what is going on, and comment on it.

The beauty of LINQ, your knowledge is almost transferable among different data sources.