Is there any way to reorder fields in an SSIS flat file source?

Question

I have an SSIS package using a tab delimited flat file source with a TON of fields. Recently the provider of the tab delimited flat file has decided to change the format of the flat file by sprinkling a couple dozen new fields at random into the file. Needless to say, this hosed the package.

Rather than rebuild another flat file source and redefine all the fields, types, and lengths all over again, is there a way to reorder the fields in the flat file source? Sure would have been nice if Microsoft allowed you to move the fields around in the Advanced Columns pane, but noooooo.

Any help is appreciated.

Solution 1

If you only need to add columns to your file, you can do that in the Flat File connection editor. In the advanced window, you can select the field next to the new one and click the chevron next to the New button. It will give you the choice insert before or insert after.

If you truly have to move things around, you'll need to edit the XML source. If you use the existing file definition as a guide, you can build the new one in Excel or T-SQL relatively easily. Easier than typing everything in all over again at least.

Solution 2

I had a similar issue: I needed to change the order of columns in my flat file destination. The time-saving approach I settled on:

1. Delete the FF destination and FF connection manager (note down file name/location!),
2. Clear the check boxes that enable output columns in the source component
3. Re-enable the columns in the order you want
4. Add a new FF destination and FF connection right from the FF destination's connection manager drop-down.
5. Review/sanity check column sizes in FF connection, as usual

Not a direct answer to the question, but I came here looking for advice on "how to rearrange flat file destination columns", perhaps this will help someone.

Comments

[JKMajcen]

SSIS is something else. In some ways I love it, in other ways its about the most half baked Microsoft product I've ever encountered.

[cdonner]

Editing XML seems to be the only option if you want to append a new column at the end. But it screwed up my row delimiter setting (changed to Mixed). So this needs to be checked afterwards.

[Mike Davis]

Thanks, this is exactly what I was looking for!