SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change column order in Flat File Destination


Change column order in Flat File Destination

Author
Message
keymoo
keymoo
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 541
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
KenpoDBA
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 620
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
keymoo
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 541
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
KenpoDBA
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 620
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
keymoo
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 541
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
SrikanthSv
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 112
We need to delete the columns and recreate them again.

Regards
Venkat
http://sqlblogging.blogspot.com
Slope
Slope
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 143
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
Alberto-419816
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 76
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
tobias.schutzer.jensen
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 19
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
paul.chaplin
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 31
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search