Change column order in Flat File Destination

Question

Change column order in Flat File Destination

keymoo

SSCrazy

Points: 2975
More actions
June 30, 2008 at 4:55 am

#129800

Hi there,
I have several Flat File destinations and I need to change the output column order in each. I've opened the connection manager, clicked on the Advanced tab and attempted to move the columns around. This doesn't seem to be the way to do it. Could someone enlighten me!?
I could delete them all and re-add them but I've already defined my data types and don't want to have to go through that pain again.
Thanks.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Yeah, that's not how that works. You can't just move cols around in a physical flat file. If you want to move them, you have to create a new file with the cols in the order you want them.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

keymoo SSCrazy Points: 2975 More actions · Answer 2

Oh man that REALLY sucks. What if I have 90 columns and my users require the columns in a different order? I have to recreate the whole thing? This can waste hours of time. :angry:

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

Let me run it through some more gray matter to see what I can come up with, but I doubt you'll find too much love there. I'll get back to you on this. If you don't hear back from me by wed, ping me again.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

keymoo SSCrazy Points: 2975 More actions · Answer 4

Thanks crever.

I guess one way that might be possible would be to open the package file in a text editor and hack the XML. It feels very dirty doing this (and I haven't attempted it yet), so I'm still holding out for something a bit more elegant.

This must be a very common problem for developers, I'm surprised this feature isn't there - perhaps I'm looking in the wrong place.

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 5

We need to delete the columns and recreate them again.

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

Slope Old Hand Points: 352 More actions · Answer 6

Here is what worked for me -

* Delete the Flat File Destination and the Flat File Connection Manager.

* Create a file with the column headings you want in the order you want (no records needed - just headings).

* Right-Click in the Connection Managers pane and select "New Flat File Connection..."

* Browse to the file that contains your column headings.

* Check the "Column names in the first data row" checkbox.

* Click on the Columns tab. You should see the columns as they appear in the text file.

* Click OK to close Flat File Connection Manager Editor.

* Add a new Flat File Destination and connect the OLE DB Source to it.

* Double-Click the Flat File Destination.

* Select the Flat File Connection Manager that you just created.

* Click on the Mappings tab. The lines between the Available Input Columns box and the Available Destination Columns box are now all criss-crossed but the destination columns are in the right order.

Alberto-419816 SSC Enthusiast Points: 128 More actions · Answer 7

Hi Guys. New to this thread

The problem I have with this approach is that my required flat file should not have any headers.

Hmm..I wonder if I set to produce the headers and use this approach then remove the headers once I get the columns in the correct order...

tobias.schutzer.jensen SSC Enthusiast Points: 111 More actions · Answer 8

There is a much easier way! Just open the dtsx-file in you favourite xml-editor and find the DTS:FlatFileColumn-tag corresponding your FlatFileConnection. Just reorder them there and open your file again in Visual Studio.

paul.chaplin SSC Enthusiast Points: 138 More actions · Answer 9

This is an old thread, and I'm actually working in SSIS (now Visual Studio) 2012 - but I would say this last method (rearranging the order in the code - you can do this within SSIS of course by choosing the "code" view - and of course would backup the package before messing with it like this) is by far the least cumbersome, if it's just rearranging work.

It seems ridiculous that there's not an in-built way of doing this (move column up...), but there you go

s.kandepi3 Mr or Mrs. 500 Points: 581 More actions · Answer 10

Did u try deleting the column which is not in order and inserting it before/after in the advanced editor window.. it worked for me as i had few columns. But with 90 columns i don't think this is the best way

barnold311 SSC Enthusiast Points: 103 More actions · Answer 11

Be sure to swap out the DTS:ColumnDelimiter line for the new last column / old last column if editing the XML.

yevad76-976273 SSC Enthusiast Points: 113 More actions · Answer 12

you can always use biml. sql server central has a great stairway on it.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 13

Slope - Thursday, August 7, 2008 6:14 PM
Here is what worked for me -* Delete the Flat File Destination and the Flat File Connection Manager.* Create a file with the column headings you want in the order you want (no records needed - just headings).* Right-Click in the Connection Managers pane and select "New Flat File Connection..."* Browse to the file that contains your column headings.* Check the "Column names in the first data row" checkbox.* Click on the Columns tab. You should see the columns as they appear in the text file.* Click OK to close Flat File Connection Manager Editor. * Add a new Flat File Destination and connect the OLE DB Source to it.* Double-Click the Flat File Destination.* Select the Flat File Connection Manager that you just created.* Click on the Mappings tab. The lines between the Available Input Columns box and the Available Destination Columns box are now all criss-crossed but the destination columns are in the right order.

This helps me to resolve my issues with column ordering.
Thanks,
Durga Prasad.

Chinnappan Balan SSC-Addicted Points: 461 More actions · Answer 14

Better use the input column with Alias name like below, It will bring the extract in the order you wanted.

select
Name as Col01
, Age as Col02
,City as Col03