Change column order in Flat File Destination

  • keymoo

    SSCrazy

    Points: 2975

    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.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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

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

    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

    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

    We need to delete the columns and recreate them again.

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

  • Slope

    Old Hand

    Points: 352

    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

    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

    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

    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

    SSC-Addicted

    Points: 441

    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

    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

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

  • durga.palepu

    Hall of Fame

    Points: 3450

    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

    Old Hand

    Points: 381

    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

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

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