How to use the column names specified in Excel fil

Question

Could anyone tell me how to change default column names (F1, F2, F3 etc..) when importing data from Excel source to an OLEDB destination in SSIS 2005.

Example: Data in my Excel spreadsheet looks as following

Title         | ProvinceID | DistrictID |
--------------|------------|------------|
Construction  |      12    |      32    |

The problem is that when I import data from sheet it gets the data but column header names are displayed as F1,F2 and F3, I want the column names to be as defined in the Excel spreadsheet. Highly appreciate your assistance

Answer 1

Solution:

You can do this by checking the box First row has column names while configuring the Excel Connection Manager.

Example:

Here is an example that illustrates this using SSIS 2008 R2.

• Let's assume that you have an Excel file as shown below with the first row containing the column names.

• When you configure an Excel Connection Manager, you need to select the Excel file. On the Excel connection manager, you will notice that there is a checkbox named First row has column names. Check this box if you want to use the column names specified in the first row of your Excel file to be visible in SSIS package.

• So, when you configure an Excel Source within a Data Flow task, you will notice the column names specified in the first row of the Excel file being displayed correctly. Following screenshots show how the column names are displayed when an Excel Connection Manager is used in an Excel Source.

Reason for your issue:

• The reason why you are seeing the column names as F1, F2 and F3 is because you probably didn't check the box First row has column names on the Excel Connection Manager. In that case, it will appear as shown here on the Excel source.

Hope that helps.