SSIS: Excel Import in a 64 bit Environment......

Another handy tip for everyone out there deals with handling an Excel file import on a 64bit environment. Now the problem that arises is that there is no 64bit compatible JET provider to use. This causes a slight problem when you are trying to do something like ‘upload an Excel file’ which needs the JET provider to function. However, this does not negate you from running the process in the 32 bit environment (WOW) on your 64 bit machine. It all depends on the scenario.

1. Running project from the Visual Studio Designer: You need to look at your project properties. There is an option ‘Use64bitRuntime’ which is the determining factor whether Design Studio will use the 64 or 32-bit runtime during your debugging session.

2. Running the project from the command line: You just need to use the 32-bit version of DTEXEC.EXE which is available in the Progam Files(x86) directory.

3. Running the project in a SQL Agent Job: This depends on what version of SQL Server you have installed. If you have a 32-bit version installed on your 64-bit machine then it will use the 32-bit runtime and you will be fine. If instead you have the 64-bit version of SQL Server installed then you need to change over to the command line task and run the SSIS package pointing to the 32-bit DTEXEC.EXE that I mentioned above.

Hopefully, this one will save you some long days and sleepless nights….

Cheers,
AJ

Powered by ScribeFire.