none
How to remove a column in data flow? RRS feed

  • Question

  • How to remove a column in data flow? For example, if an input column to a Lookup Transformation is used as the key match to the reference table, this column may not be needed after the Lookup, and therefore should be removed for the performance? But how, when i open the Advanced editor, only the new generated column there.

    Also, is LoopUp task a asynchronous component? because it can generate a new LineAgeID.

    http://consultingblogs.emc.com/jamiethomson/archive/2005/02/11/SSIS_3A00_-Asynchronous-and-synchronous-data-flow-components.aspx

    "In a way, they act as both destination and source, thus they generate
    different LineageID for the output columns. Since the mappings in the
    downstream is done using the LineageID you need to restore these mappings."

    Thursday, June 17, 2010 2:24 PM

Answers

  • The best way to deal with unwanted columns in your pipeline is to just not use them. You really shouldn't need to mess with the Advanced editor.

    The next time your pipeline goes through a transform where you can select the output columns, just leave those un-used ones unchecked. Those transforms include, but are not limited to, Union All, Pivot, Un Pivot, Merge, Merge Join and others.

    You probably won't gain performance by trying to tweak the Advanced Editor.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, June 17, 2010 2:44 PM
  • Todd (the other one) is correct.  You do not need to remove columns.  If you leave them unused, SSIS will know it, and MAY choose to remove them itself at some point from the internal memory structures during runtime to improve performance.

    The Lookup is a synchronous operation.  Synchronous doesn't mean it can't create columns.  Synchronous means that it operates on the buffer it is presented with, and doesn't need to copy data to a new buffer - either to reformat the data flow, reorder the data flow, or produce more or fewer rows than it's given.


    Todd McDermid's Blog Talk to me now on
    Thursday, June 17, 2010 2:55 PM
    Moderator

All replies

  • The best way to deal with unwanted columns in your pipeline is to just not use them. You really shouldn't need to mess with the Advanced editor.

    The next time your pipeline goes through a transform where you can select the output columns, just leave those un-used ones unchecked. Those transforms include, but are not limited to, Union All, Pivot, Un Pivot, Merge, Merge Join and others.

    You probably won't gain performance by trying to tweak the Advanced Editor.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, June 17, 2010 2:44 PM
  • Todd (the other one) is correct.  You do not need to remove columns.  If you leave them unused, SSIS will know it, and MAY choose to remove them itself at some point from the internal memory structures during runtime to improve performance.

    The Lookup is a synchronous operation.  Synchronous doesn't mean it can't create columns.  Synchronous means that it operates on the buffer it is presented with, and doesn't need to copy data to a new buffer - either to reformat the data flow, reorder the data flow, or produce more or fewer rows than it's given.


    Todd McDermid's Blog Talk to me now on
    Thursday, June 17, 2010 2:55 PM
    Moderator