locked
Change Column Order RRS feed

  • Question

  • How does one go about changing the column order of the data moving thru an SSIS package?  It'd be nice to able to do this at any point in the package for readability, but where I really need it is in the Flat File Output.  I need to deliver the columns in a specific order, and I can't figure out how to do that.

    This seems to be such a basic question, but I can't find it in Search anywhere.  My apologies if it's already been answered.
    Monday, November 7, 2005 9:37 PM

Answers

  • The column order of a flat file is defined within the connection. Changing column order within the pipeline itself would be a bad ideas as this require moving data around, which costs. A way to do this as a view for readability may well be nice, but may end up being confusing as well.

    The default behaviour of the Flat File Destination means you create a new connection within the UI, and that connection will happily define the columns according to the buffer, which is just perfect 99% of the time. For your case you have two workarounds-

    1 - Add your Flat File Connection, set file and delimiters. Select the Advanced tab, and add your columns as you want in the correct order. This connection can then be selected, and you map the columns. You could do this within the Flat File Destination, as this will create all columns to start with, but cannot change order, which is a shame, so you will have to remove and insert columns to get the correct order overall.

    2 - A simple method is to create a flat file that represents what you want to produce from SSIS. Then you can add a new Flat File Connection, and select the sample file, and allow the columns to be generated from this file. You may still wish to fine tune this through the Advanced tab, but it should do the bulk of the work.

    Why not log some Feedback on MSDN for the ability to change column order within the Flat File connection as that is really what you want I belive.


    Tuesday, November 8, 2005 1:09 PM

All replies

  • The column order of a flat file is defined within the connection. Changing column order within the pipeline itself would be a bad ideas as this require moving data around, which costs. A way to do this as a view for readability may well be nice, but may end up being confusing as well.

    The default behaviour of the Flat File Destination means you create a new connection within the UI, and that connection will happily define the columns according to the buffer, which is just perfect 99% of the time. For your case you have two workarounds-

    1 - Add your Flat File Connection, set file and delimiters. Select the Advanced tab, and add your columns as you want in the correct order. This connection can then be selected, and you map the columns. You could do this within the Flat File Destination, as this will create all columns to start with, but cannot change order, which is a shame, so you will have to remove and insert columns to get the correct order overall.

    2 - A simple method is to create a flat file that represents what you want to produce from SSIS. Then you can add a new Flat File Connection, and select the sample file, and allow the columns to be generated from this file. You may still wish to fine tune this through the Advanced tab, but it should do the bulk of the work.

    Why not log some Feedback on MSDN for the ability to change column order within the Flat File connection as that is really what you want I belive.


    Tuesday, November 8, 2005 1:09 PM
  • I had the exact same question...and I too couldn't believe that it wasn't simple to re-order the outputs being written to a flat file.

    I am pulling data from a database using a SQL Query then adding columns with the Derived Column transform.  I want the derived columns to go at the beginning of the output file, not the end.

    I like your idea of reverse engineering a sample file...I will give that a shot.

    I am currently evaluating ETL tools for converting data from various database types and structures to complex (multiple record types) flat files.  Overall, I am pretty impressed with SSIS although I have noticed that some tasks are not as straightforward as they are in Sagent and Informatica.

    Tuesday, November 8, 2005 9:38 PM
  • If you are not already aware there are a couple of whitepapers that may help your evaluation-

    Microsoft SQL Server: Forrester Report: Microsoft Addresses Enterprise ETL
    (http://www.microsoft.com/sql/technologies/integration/foresterreport.mspx)


    Well that's one. The other was a review of SSIS compared to Informatica, by Conchango. I can't find it now in the wake of the site updates for RTM. Hopefully it will reappear or someone will post a link. Searching microsoft.com only gives a broken link.

    Don't forget you get a free RDBMS, OLAP and Reporting System when you buy SSIS :)
    Tuesday, November 8, 2005 9:47 PM
  • Very funny...you never know when that RDBMS may come in handy ; )

    Thanks for the Forrester link. 

    I found the Conchango review....thanks for the lead.
    http://download.microsoft.com/download/1/0/3/103fd39e-3ca4-4db7-a087-1263dc6ed0b1/CompIntTools.pdf

    Our enterprise product is built pretty much from front to back on Microsoft technology so SSIS will be hard to beat if it can get the job done efficiently.

    Tuesday, November 8, 2005 10:28 PM
  • I realize this post is pretty old but I am running into the same problem. Unfortunately, my output file, a CSV, contains 130 columns and I'm not looking forward to manually adding each column in one at a time setting the data type of each. All of the 130 output columns are derived columns so I guess I could re-create all of the derived columns. Man what a pain! I was hoping the 2008 version would make things a little less tedious but it has not been the case. Did you find a solution that worked for you that did not involve re-creating the columns in the order you wanted them? I'm spending so much time creating an import file that I probably could have manually added the data in by now. Arrrrg!

    Monday, September 29, 2008 11:31 PM
  • now your post have also become older and i am a new comer and i still ask the same question


    is there a more human way of reordering the columns in the flat file??????????
    Wednesday, January 21, 2009 11:01 AM
  • If you are really careful, you can simply open the dtsx file in a text editor, find the DTS:FlatFileColumn with your column name in it and move it to the desired (physical) location. Worked for me!

    EDIT: of course if you are using Flat File Connection which is where you would mostly need to rearrange the order of columns

    Question for Microsoft - adding Move Up & Down button is not such a big job nowadays, ah?

    • Proposed as answer by RftFire Monday, November 25, 2013 12:47 PM
    Wednesday, February 23, 2011 11:36 AM
  • Thanks Nikolay Evseev

    This worked for me as well..

    Just need to make sure to check the delimiter property as well if you using a .csv file and swap around the last column in the list

    Monday, November 25, 2013 12:27 PM