THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

SSIS 2016 CTP 3 and Data Flow Column LineageID

Back When We Used to Carve Our Own Chips Out of Wood…

Back in the old days (2005), SSIS Data Flow LineageIDs were created at design-time. Here’s a screenshot of an SSIS 2005 package’s XML:

SSIS2016Lineage03

When an error occurred, the ID property of the column in error was supplied to the ErrorColumn column field of the Error Output, as shown here:

SSIS2016Lineage04

Although it wasn’t simple, it was possible to use the value of the ID supplied in the ErrorColumn value to identify the offending column in a Data Flow Task. It was a manual process that involved:

· Opening the package’s XML

· Finding the column ID value shown in the ErrorColumn field of the Error Output

· Reading the LineageID value for that same column

· Tracing the LineageID back to its original assignment in the Data Flow Task:

SSIS2016Lineage05

The LineageID “49” maps to the “name” column in the image at the top of this post. The “name” column raised an error because someone (me) made the destination column way too small to hold any name values.

Back in the day, some folks came up with some pretty clever ways to automate identifying the name of the column causing SSIS Data Flow errors.

In Later Versions of SSIS…

The LineageID property of Data Flow columns changed in later versions of SSIS. In SSIS 2012, the LineageID property is there, but the value looks more like an SSIS package path than its SSIS 2005 counterpart.:

SSIS2016Lineag1a

The LineageID attribute in SSIS 2016 CTP 3.3, however, appears similarly:

SSIS2016Lineage_LineageID

A New Beginning…

SSIS 2016 CTP3.3 offers a solution. First, there are now two new columns in the SSIS Data Flow Component Error Output – ErrorCode – Description and ErrorColumn – Description:

SSIS2016Lineage06a

The new columns provide extremely useful (plain language) error metadata that will, in my opinion, greatly reduce the amount of time required to identify data-related load failures in the Data Flow Task.

But that’s not all. If you configure a log to capture the DiagnosticEx event, you will receive a message that provides the Data Flow column ID. To have a look, add a new log to an SSIS package that contains a configured Data Flow Task. On the Details tab, select the DiagnosticEx event:

SSIS2016LineageDiagnosticEx

When the package runs, a DiagnosticEx event will record XML describing the DTS:PipelineColumnMap. Viewing the XML in either a text file or SQL Server is no fun, but if you copy the XML and paste it into an XML file in Visual Studio (SSDT), you can format it nicely, as shown below:

SSIS2016Lineage07a

It’s possible to call a new method (GetIdentificationStringByID<) on the ComponentMetadata class in a Script Component, passing it the LineageID of a Data Flow column, and get the IdentificationString of the column. You can learn more about the GetIdentificationStringByID method – and everything I’ve written in this post – by reading Bo Fan’s (excellent) blog post on the subject here.

:{>

Published Monday, February 08, 2016 2:57 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Koos van Strien said:

Andy, you state that "The LineageID attribute in SSIS 2016 CTP 3.3, however, appears differently:" - but in both screenshots I see almost the same string (except text color and different column name). Can you point out the difference?

February 17, 2016 5:11 AM
 

andyleonard said:

Hi Koos,

  Busted. I wrote this article for a previous version of the CTP. When I realized the functionality wasn't working (yet - at least not in VS2015), I sent a version of the post to Microsoft's SSIS Development Team and asked what I was doing wrong. They told me I'd found a bug and promised to fix it. Which they did.

  I was (and remain) impressed. I even blogged about it here (http://sqlblog.com/blogs/andy_leonard/archive/2016/02/08/microsoft-is-listening.aspx).

  But I forgot to update all my earlier text. Apologies!

:{>

February 24, 2016 8:37 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement