Tuesday, February 2, 2010

Cannot Execute SSIS packages with Excel in 64 bit version of SQL Server

When you try to execute SSIS package which has a Excel Destination/Source in SQL Server 64 bit version, you will get following error.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

As error suggest Excel Connection Manager does not support 64 bit version. Depending on the environment you can execute this SSIS packages.

SQL Server Agent Job

In SQL Server 2008, there should be a checkbox on the Execution options tab Job Step page to run the package in 32-bit mode as shown in following image.

clip_image002

In SQL Server 2005, you have to change the Job Step from an Integration Services Step to an Operating System step, and specify a DTEXEC command line instead. The DTEXEC command line needs to specify the DTEXEC.EXE from the Program Files (x86) folders, NOT the DTEXEC.EXE from the Program Files folders which is the 64 bit version.

DTEXEC

If you're executing the package using, as said before, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the Program Files (x86) SQL Server folder.

Visual Studio

You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False, as shown in the following image.

clip_image004

No comments:

Post a Comment