How to add column names to the flat files without column headers using SSIS.

Question

I have multiple flat files containing 126 columns , but each of them is without column names. How should i add column names to these files using SSIS. These files are needed to be imported using SSIS so that i can perform transformation on these files.

Answer 1

Do you want to create a new file which has column names or just assign field names to the columns for use in the rest of the package?

Whichever way, if the input file does not contain column names then set them up as follows…

1. Create a dataflow task and in the dataflow task create a flat file source.
2. Configure the flat file source and create a new Flat File Connection Manager
3. Browse to the input file you want and un-tick the Column Names In First Row
4. Select Advanced and change all of the default names (Coulmn 0, Coulmn 1 etc) into the field names (and types) you want.
5. Click OK

If you need to create a new file that has the column names in it, just create a flat file destination and this time have the Column Names In First Row turned on, wire it up to the input you created and save it to a new file

Answer 2

One way to do this (maybe not the quickest way) is by using the Advanced Editor.

1. Right click on the Excel Source component and select Show advanced editor.

2. In the new window, you need to go to the Input and Output Properties. You should have this by now:

3. Click on a column under Output Columns (F1,F2,...)

4. In Common properties, edit the Name to what you want.

I added a derived column component as my next step and this is what I see under the aviable columns:

As you can see, F1 (which I edited in step 2) has a new column name now.

Edit: I somehow assumed you needed this for Excel. Anyways, I hope it helps.