How to Find Out Which Column Caused SSIS to Fail?

Article
08/01/2008

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.

Comments

Anonymous
February 18, 2009
The comment has been removed
Anonymous
March 10, 2009
Right Click the Package name on the Solution Explorer, choose the option "View Code", you can see the package attributes as a XML file. Search for the Error Id - Eg., 72819. You can get the column id for this number and get the column name for this column id. Good Luck!
Anonymous
March 10, 2009
Right Click the Package name on the Solution Explorer, choose the option "View Code", you can see the package attributes as a XML file. Search for the Error Id - Eg., 72819. You can get the column id for this number and get the column name for this column id. Good Luck!
Anonymous
February 18, 2010
Thanks, that helps alot. I used a dataviewer on the Failure connection (to a Union All bitbucket). The dataviewer shows the error column and the error message. Knowing the error column, I used your View Code suggestion, did a Find using the column number, and voila, found the column name. Thanks again.
Anonymous
July 28, 2010
How to find the path of the package, when i deploy the package in a sql server.
Anonymous
March 20, 2011
How to find the path of the package, when i deploy the package in a sql server.
Anonymous
September 12, 2011
Use th following to get packageXML from SSIS deployed on SQL server GO /****** Object: StoredProcedure [dbo].[Proc_getSSISPackageXML] Script Date: 09/08/2011 02:51:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Chinmoy Mohanty -- Create date: 08 Sep 2011 --DESCRIPTION: Lists all SSIS packages deployed to the MSDB database. --COPIED FROM: http://blog.hoegaerden.be --Note: this query was written for SQL Server 2008. For SQL2005: -- o sysssispackagefolders => sysdtspackagefolders90 -- o sysssispackages => sysdtspackages90 -- ============================================= CREATE PROCEDURE [dbo].[Proc_getSSISPackageXML] @PackageName varchar(max), @PackageXML nvarchar(max) output AS BEGIN with ChildFolders as ( select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername, cast('' as sysname) as RootFolder, cast(PARENT.foldername as varchar(max)) as FullPath, 0 as Lvl from msdb.dbo.sysssispackagefolders PARENT where PARENT.parentfolderid is null UNION ALL select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername, case ChildFolders.Lvl when 0 then CHILD.foldername else ChildFolders.RootFolder end as RootFolder, cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max)) as FullPath, ChildFolders.Lvl + 1 as Lvl from msdb.dbo.sysssispackagefolders CHILD inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid ) --select F.RootFolder, F.FullPath, P.name as PackageName, -- P.description as PackageDescription, P.packageformat, P.packagetype, -- P.vermajor, P.verminor, P.verbuild, P.vercomments, --select --cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData select @PackageXML=cast((cast(cast(P.packagedata as varbinary(max)) as xml)) as nvarchar(max)) from ChildFolders F inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid where P.name= @PackageName or P.name LIKE '%'+@PackageName+'%' order by F.FullPath asc, P.name asc; END GO

Share via

How to Find Out Which Column Caused SSIS to Fail?

Comments

Additional resources