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.

Comments (7)

  1. Srikanth says:

    How can we find out the package name and path.I’ve looked out for various options but that did not work out.

    I want to  implement it as a data transformation component and use it across packages

    Thanks for the code

  2. Santhana Meyyur says:

    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!

  3. Santhana Meyyur says:

    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!

  4. christian_bahnsen says:

    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.

  5. Hk says:

    How to find the path of the package, when i deploy the package in a sql server.

  6. Sateesh Maduri says:

    How to find the path of the package, when i deploy the package in a sql server.

  7. Chinmoy Mohanty says:

    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

Skip to main content