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.

Answer 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.

Answer 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.

Answer 3

In the connection managers below of your IDE you can double click your file name and edit everything you want.

Answer 4

This is still a "feature" of SSIS. To work around this I create a flat file connection called "NULL" with a single column named "NULL". Use the "New" button to add the column. I change the default column name from "Column 0" to "NULL". This column name must not match any column name in the list to be re-populated. If you have a real column named "NULL", pick something else for the column name that's not in use. You can keep the "NULL" flat file connection in the project for later use. (I expect to need it a few more times in this project.)

For this example, I use a flat file destination. Change the Flat File Destination to use the NULL connection.

Check the mapping to see there are no columns mapped. Saving this resets the metadata stored for the mapping.

Finally, change the Flat File Destination back to the correct connection to get a new mapping without metadata interference.

My example is a flat file destination. It should work for a flat file source for resetting the metadata. It is similar to the trick of changing a query to "select 1 as [NULL]" and back to purge metadata when using a ODBC source or such.

Answer 5

I haven't seen an solution for that problem. SSIS isn't very strong in changing metadata. You could try to do it in notepad, but that is very tricky and very buggy. I would not recommand that to you.

Answer 6

you could probably try something, but i havent tested.. use expressions to set everything for your flat file source? turn design time validation off