Excel connections in SSIS 64-bit

This is a new one on me…when running SSIS in a 64-bit environment, Excel connections (and Access as well I presume) do not work, as the OLE DB Provider for Jet is not supported.

At a current client, they are moving the server platform from Windows 2003 Server to Windows 2008 Server 64-bit.  I had developed some ETL packages for them, pulling data from Excel and CSV sources.  The packages get executed when the files have been unzipped and downloaded to a specified folder.  This is done via simple batch files, which are along the lines of the following:

dtexec /f "F:SSIS PackagesReportingImportClientCSV.dtsx"

When I moved all the packages to Windows 2008, the CSV packages ran no problem.  But when I tried to execute one that imported from Excel, I received the following error:

Code: 0xC00F9304
   Source: ImportClientXLS Connection manager "Excel Connection Manager"

   Description: 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.

In order to use the OLE DB for Jet providers, the packages must be run in 32-bit mode.  This is done by using the x86 version of dtexec and specifying the "/X86" switch.  I amended the batch file in question to look similar to this:

"C:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe" /f "F:SSIS PackagesReportingImportClientXLS.dtsx" /X86

Voila!  The package ran successfully.

This link from Microsoft details the limitations when running SSIS in 64-bit environments: 64-bit Considerations for Integration Services

16 comments

  1. russell young

    how to exec ssis package in stored procedure in 32-bit mode. i tried this but stil get an error message dtexec /X86 /SQ ups /ser ‘bdvsql2008-01’

  2. Alan

    Hi Russell, if you are trying to run a package from a stored proc, you could use xp_cmdshell (if you have permissions) to exec the above statement. Alternatively, set up a job for the package (with no schedule) and run it using sp_start_job.

  3. Dave

    Hi,I am running an SSIS package from a stored procedure using xp_cmdshell. This worked fine on a 32-bit server, but it’s not working on our new 64-bit server so I tried adding the /X86 flag as you described.My SP includes a line like this:EXEC master..xp_cmdshell ‘dtexec /X86 /F “C:package.dtsx”, NO_OUTPUTThe error is the same as the one in the original post, despite inclusion of the /X86 flag.I appreciate any guidance you can provide. Thanks for your help!DaveP.S. I’ve tried posting this a few times but it doesn’t seem to be going through. Sorry if it’s a duplicate.

  4. Dave

    My SP is working now — I had neglected to make the change to use the full filepath using the Program Files (x86) folder (as shown in the post). Once I did that change, I no longer get the error.

  5. Munawar

    HelloI’m facing the same issue.I did as suggested by you.Code sniipet:EXEC xp_cmdshell ‘SET PATH=%PATH%C:Program Files (x86)Microsoft SQL Server100DTSBinn’;EXEC xp_cmdshell ‘dtexec /X86 /f “\Network18Pakage.dtsx / SET Package.Variables[User::ETLDate].Properties[Value];”09/02/2011″‘;But still I get the same error..One thing I want to add, I dont have MS-Office installed on my server (Windows Server 2008).

  6. PRAVEEN

    Hello This can be over come by changing the project properties. Select Project, select Project propeties. Select the debug tab and then in the run64bitruntime select false.

  7. Jayesh

    you can also do it while developing in BIDS. right click the project and go to properties, then debugging tab. and set “Run64BitRuntime” property to False. This worked for me to void this error in development and debugging time šŸ™‚

Leave a Reply