none
LineageID of an input column has an another value in runtime

    Question

  • Hi all,

    I upgraded my own data flow transformation component to SQL 2012. My component has one input and one asynchronous output.

    I found the following unexplained behavior: a value of the LineageID of an input column at the runtime differs from a value which is currently set (and I can check it in designtime using Advanced Editor). E.g. component's input has only one column with the name "Address" and its LineageID equals 10. But during debugging component at the runtime I see that input contains only one column with the name "Address" but its LineageID equals 11. How is it possible?

    So somebody could explain me what is it or give me an advice how to avoid this behavior. And in general how is it possible at all that LineageID for the same column has different values in the design time and the runtime?

    PS. Versions of this component for SQL 2005 and 2008 work without such problem.

    Igor

    Thursday, August 30, 2012 11:09 AM

Answers

  • I don't know if it's widely disseminated - it's internal stuff.  Here's a link from Marcin Policht where he says the same thing (lineage IDs are still/only used at runtime).  You can still see references to LineageID in developer documentation (note this is a 2012-specific section of MSDN/BoL).


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by igor_VCH Monday, September 03, 2012 8:18 AM
    Thursday, August 30, 2012 8:32 PM
  • Lineage IDs are not actually "gone" in SSIS 2012 - they're just hidden.  The team (correctly) decided that LineageIDs just complicated things, and that developers were just fine with linking things by column name.

    However, (to be simplistic) they only changed the UI, not the engine.

    When you're editing a package, you don't ever see LineageIDs.  If you look at the XML of a saved package, there are no LineageIDs.  However, when the package is loaded, LineageIDs are created and used in the background in order to improve performance of the execution, and probably to limit the amount of code they would have had to change to get rid of them entirely.  So what's happening to you is that the internal engine is deciding to give your column a LineageID of 10 on some occasions when it loads it - but a LineageID of 11 on other occasions.  Don't worry - it's always consistent once it's loaded, but you can't count on it being consistent between loads.

    What this means to you as a component developer is that an upgrade to 2012 is not as simple as 2005 to 2008... if you store LineageIDs in your component's properties.  You need to do the same thing the SSIS team did - change your component to store and manage metadata based on column name, not on LineageID.


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by igor_VCH Monday, September 03, 2012 8:17 AM
    Thursday, August 30, 2012 6:34 PM

All replies

  • LineageIDs are gone in SSIS 2012.

    Where do you see that?


    Arthur My Blog

    Thursday, August 30, 2012 1:51 PM
  • Lineage IDs are not actually "gone" in SSIS 2012 - they're just hidden.  The team (correctly) decided that LineageIDs just complicated things, and that developers were just fine with linking things by column name.

    However, (to be simplistic) they only changed the UI, not the engine.

    When you're editing a package, you don't ever see LineageIDs.  If you look at the XML of a saved package, there are no LineageIDs.  However, when the package is loaded, LineageIDs are created and used in the background in order to improve performance of the execution, and probably to limit the amount of code they would have had to change to get rid of them entirely.  So what's happening to you is that the internal engine is deciding to give your column a LineageID of 10 on some occasions when it loads it - but a LineageID of 11 on other occasions.  Don't worry - it's always consistent once it's loaded, but you can't count on it being consistent between loads.

    What this means to you as a component developer is that an upgrade to 2012 is not as simple as 2005 to 2008... if you store LineageIDs in your component's properties.  You need to do the same thing the SSIS team did - change your component to store and manage metadata based on column name, not on LineageID.


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by igor_VCH Monday, September 03, 2012 8:17 AM
    Thursday, August 30, 2012 6:34 PM
  • You need to do the same thing the SSIS team did - change your component to store and manage metadata based on column name, not on LineageID.
    do you have an example/link about this?

    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Thursday, August 30, 2012 7:42 PM
  • I don't know if it's widely disseminated - it's internal stuff.  Here's a link from Marcin Policht where he says the same thing (lineage IDs are still/only used at runtime).  You can still see references to LineageID in developer documentation (note this is a 2012-specific section of MSDN/BoL).


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by igor_VCH Monday, September 03, 2012 8:18 AM
    Thursday, August 30, 2012 8:32 PM
  • Thank you Todd,

    your posts were very helpful and they have clarified this thing for me. Of course I saw the ways how to around this situation but more important was to understand what is a problem essence. So thank you again.

    Friday, August 31, 2012 7:28 AM
  • Thanks Todd

    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Friday, August 31, 2012 12:59 PM
  • So what does this mean for building packages programatically?  I have an application that builds a derived column component.  It dynamically builds the expressions for output columns by looking up IDTSVirtualInputColumn100 int the VirtualInputColumn collection by name.  Then it calls SetUsageType to make the column available as an input and then builds the expression using the lineage id of the virtual column.  I noticed that when I open the resulting package in the designer.  I get errors because the lineage ids are not the same as they were when the application built the package.  Does this mean that I should not bother adding the custom property named "expression"?  or should I build it using the column name instead of the lineage id?

            public override void ConfigureColumn(IDTSComponentMetaData100 cmpDerivedCol)
            {
                CManagedComponentWrapper wDerivedCol = cmpDerivedCol.Instantiate();
                wDerivedCol.ProvideComponentProperties();
                IDTSInput100 derivedColInput = cmpDerivedCol.InputCollection[0];
                IDTSVirtualInput100 vDerivedColInput = derivedColInput.GetVirtualInput();
                IDTSOutputColumnCollection100 outCols = cmpDerivedCol.OutputCollection[0].OutputColumnCollection;

                IDTSVirtualInputColumn100 vCol = vDerivedColInput.VirtualInputColumnCollection[m_derivedColumn.SourceColumn.Name];

                IDTSInputColumn100 inCol = wDerivedCol.SetUsageType(derivedColInput.ID, vDerivedColInput, vCol.LineageID, DTSUsageType.UT_READONLY);

                IDTSOutputColumn100 outCol = outCols.New();

                outCol.Name = m_decodedColumn.Name;
                outCol.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                outCol.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;

                IDTSCustomProperty100 prop = outCol.CustomPropertyCollection.New();

                prop.Name = "Expression";
                prop.Value = string.Format("(DT_BOOL)(REPLACE(REPLACE(#{1} , \"Y\", \"1\" ), \"N\", \"0\" ))", 1, vCol.LineageID.ToString());

                prop = outCol.CustomPropertyCollection.New();
                prop.Name = "FriendlyExpression";
                prop.Value = string.Format("(DT_BOOL)(REPLACE(REPLACE({1} , \"Y\", \"1\" ), \"N\", \"0\" ))", 1, vCol.Name);

                outCol.SetDataTypeProperties(DataType.DT_STR, 1, 0, 0, 1252);
            }

    Friday, September 07, 2012 1:58 PM
  • I don't know the answer to that question - but does your package execute properly?

    You might want to try setting the ContainsID property to True...


    Todd McDermid's Blog Talk to me now on

    Friday, September 07, 2012 4:18 PM
  • Could not get it to run because of the validation errors.  The app does not currently have code that sets the delay validation property for any components.  I'll try setting it manually but honestly I didn't spend any time trying to get it to run yet.  I've been switching up to use the column name instead of lineage id.  so far that seems to have worked.  can I assume that the same is true when setting the Destination Column custom property of the unpivot component?  It used to use the column ID.  Now that doesn't seem to work.  I am going to switch that to use the name as well.  I will update you and let you know what happens.

    Thanks for the tip with the containsId property.  That may come in handy also.

    Tom

    Friday, September 07, 2012 8:38 PM
  • To help figure out how to program it using the API, I'd first design it in the UI, then examine the XML output.  Then you poke and prod at the API until you can get the same XML...

    Todd McDermid's Blog Talk to me now on

    Saturday, September 08, 2012 4:38 PM
  • Todd sorry it took so long to respond.  That is pretty much the approach I used when I originally designed the app.  I got everything working for the most part although I have a strange issue occurring now that I will probably start a new thread about.

    Tom

    Friday, September 14, 2012 7:12 PM
  • You need to set the ContainsID property on your custom component property to True. This tells the SSIS data flow engine that the lineage ID value needs to be remapped when the package is saved/loaded.

    Wednesday, March 20, 2013 2:31 PM
  • I remember one Microsoft presentation related to this change.

    Removing LineageID was required to enable Team development in SSIS. In previous versions, when you try to merge changes from different source code branches to the same package you often got LineageID conflicts.

    Now it is possible to make such merges.

    Wednesday, March 20, 2013 5:31 PM
  • can some onehelp us to solve the issue

    Thursday, August 22, 2013 6:08 AM
  • I heard that on Derived Column, just set the FriendlyExpression property to the [columnname] string and when the packages gets persisted (saved), the save will fill in the lineage in the Expression property to have the path of that column. I didn't try it yet, but sounds promising. 

    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    Friday, March 21, 2014 7:07 PM
  • For these kinds of errors
    {
    -1073450845     Attempt to find the input column with lineage ID 1failed with error code 0xC00470B2. The input column was not found in the input column collection.

    -1073450856     Attempt to parse the expression "#1" failed and returned error code 0xC00470A3. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

    -1071615996 The property "Expression" is empty. The property cannot be empty
    }

    Maybe you have your derived column transform programmatically set as
    {
     Friendly Expression = <[Source Column1]>
     Expression = <#SourceColumnLineage ID as number>
    }

    Instead, when you are coding the derived column transformation, specify the column name for both the Expression and Friendly Expression property values:
    {
     // For expression:
     prop.Value = string.Format("(DT_STR,255,1252){0}", columnName);

     // For friendlyExpression
     prop.Value = string.Format("{0}", columnName);
    }


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    Friday, May 23, 2014 8:47 PM