Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change column order in Flat File Destination Expand / Collapse
Author
Message
Posted Monday, June 30, 2008 4:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:52 AM
Points: 107, Visits: 472
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.



Post #525799
Posted Monday, June 30, 2008 8:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 320, Visits: 359
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #526007
Posted Monday, June 30, 2008 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:52 AM
Points: 107, Visits: 472
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.


Post #526071
Posted Monday, June 30, 2008 10:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 320, Visits: 359
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #526078
Posted Tuesday, July 1, 2008 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:52 AM
Points: 107, Visits: 472
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.



Post #526389
Posted Tuesday, July 1, 2008 2:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:35 AM
Points: 61, Visits: 112
We need to delete the columns and recreate them again.


Regards
Venkat
http://sqlblogging.blogspot.com
Post #526400
Posted Thursday, August 7, 2008 6:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 10:55 AM
Points: 8, Visits: 139
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.

Post #548769
Posted Wednesday, March 14, 2012 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 11:58 AM
Points: 2, 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...
Post #1266918
Posted Monday, September 16, 2013 1:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 1:21 AM
Points: 1, 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.
Post #1494950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse