SSIS – Modifying column names in flat file

When column names are not in the first row of the source file – it can be a pain to change all the column names in the SSIS package – if you’ve only got a couple of columns of data – it’s ok but when you have 100’s of columns then it’s extremely painful.
One work around for this is to point your connection at the flat file source – then save the package and use view code to get the XML schema. At this point your columns are named column 0 to column n.

The following tip ONLY works if the column names are in the SAME order as your file – or any additional columns in the table are at the beginning / end of the list. Esnure results are set to text not grids!

selectName, Colidfrom syscolumns where id =object_id(‘targettable’) orderby colId asc

Use the alt key to select then column names (including trailing spaces to make sure you get “all” the name) from the pane and copy the selection. Do this for columns 0-9

Then go into the code view in Visual Studio and find Column 0 – Use alt  + shift to select columns 0-9 and replace with the contents from management studio.

Repeat the process for columns 10-99 in Management studio and visual studio then again for columns 100 – 999 (hopefully you won’t have to repeat for lines 1000 = 9999!

One minor quirk – the last column has a slightly different format – so manually correct that – or just perform this exercise excluding the last column – the last column has objecttype in line with the column list!

Finally select the Columns you have just amended in VS – again using alt + shift (or alt + mouse) and do a search replace to remove redundant spaces.

Having just had to create an SSIS package with 400 + columns – it’s a lot easier than doing it manually!