Straight out of the box, you cannot use the Excel Datasource in SSIS on a 64-bits Windows platform , because the Excel Datasource uses a 32-bits OLEDB library. Microsoft has not released a 64-bits version of this library.
0) Use MADE 2010
There is 64 bits version for Office 2010, so for Excel 2010. It is called "Microsoft Access Database Engine 2010 Redistributable" of which there is a 32- and 64-bits version. We're interested in the 64-bits version, but this package can only be installed after removing all 32 bits Office applications!
a) Install MADE 2010 while NOT removing 32 bits Office applications:
AccessDatabaseEngine_x64.exe /passive
Notes:
- THE SYSTEM WILL AUTOMATICALLY REBOOT, SO SAVE WORK BEFOREHAND!
- Start Excel once to go through the configuration process. Start it a second time. It the configuration process starts again, then disable it by renaming this file to "setup_disabled.exe":
c:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\Office Setup Controller\setup.exe - run the repair option for the Microsoft Access database engine 2010 program inside "add/remove programs"
b) If that doesn't work, then also install the Data Connectivity Components:
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
1) Trick Windows to use 32-bits ODBC data sources
Try adding the SysWow64 folder to the beginning of your path:
Path=%systemroot%\SysWow64;...old values here...
When done via the command prompt via the SET-command, it will be undone when that window is closed.
2) Run SSIS in 32-bits mode
Use the 32-bit DTEXEC.EXE to execute the package containing the Excel Datasource. It is located in "Program Files (x86)\Microsoft Sql Server\90\Dts\Binn" (or for SSIS 2008: "Program Files (x86)\Microsoft Sql Server\10\Dts\Binn")
You can schedule this using "task scheduler" or SQL Agent.
3) Compile package as 32-bits
Package configuration properties -> Debugging -> Debugging Options -> Run64BtRuntime -> set to False.
4) Even dirtier trick: convert Excel spreadsheet to CSV beforehand
This can be done in several ways, with minimal coding required:
- If Excel is installed, then create a VBS script that uses CreateObject("Excel.Application") to create an Excel object. Then open the XLS file and save it as a CSV using this object.
- ... or a perl script using the "Spreadsheet::ParseExcel" module
- ... or a powershell script using "comobject Excel.Application"
- Install OpenOffice and program a small macro in it's OpenOffice basic language. Then create a VBS or DOS script that opens OpenOffice and calls the macro with parameters, one being the source XLS, and one being the target CSV.
- Without installing anything on the server running SSIS: write a small C# application that utilizes the Excel OLEDB data provider built into Windows to read data from the Excel sheet. Then write the results to a CSV file.
- ... or write a small C# application that utilizes the opensource Excel Data Reader library to read data from the Excel sheet. Then write the results to a CSV file. This library appears to be buggy however.
- Freeware commandline tool: XLS2CSV by Zoom Technology. Parses only the first sheet. The column separator and text quotes are configurable throught an ini file
- Freeware commandline tool: XLS2CSV by Tom Crow. Parses all sheets to separate CSV files
ODBC/DSN configuration can be found here:
- 32 bits ODBC connections: %windir%\syswow64\odbcad32.exe
- 64 bits ODBC connections: %windir%\system32\odbcad32.exe
- ADO also uses the OLEDB library.
- The OLEDB library is a piece of code that gets loaded and compiled into the calling application at runtime.
- If you use the MADE 2010 Redistributable package, then be aware of a small bug when connecting to Office 2010. You have to use "MICROSOFT.ACE.OLEDB.12.0" as the driver name in stead of the specified "MICROSOFT.ACE.OLEDB.14.0".
- Although previous Office versions need to be de-installed before installing MADE 2010, they can be reinstalled afterwards.