Change Column Order

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.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.|||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.

|||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 :)|||

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.