SSIS Import Excel Document Using Column Position, Not Name

Question

I was wondering if it was possible to import Excel documents using SSIS by referencing a column by its position? For example, import columns A,D,M,AA, etc. I ask because I need to load in several Excel documents from a third party. Each document contains the same data type in the corresponding columns, except the column names are different for each document.

Thanks!

Answer 1

Yes but you won't be using the Excel driver and connection manager. Instead, you will use the OLE DB driver and write a SQL Query against the file. For anything but the most basic Excel files, this is my go-to approach for importing data out of Excel.

Various incarnations of my approach

• Excel Source as Lookup Transformation Connection
• script task in SSIS to import excel spreadsheet
• Import a single Excel cell into SSIS