Thursday, 5 November 2009

SSIS Packages and Excel Data on 64 bit machines

We do a fair amount of integration work between CRM and Excel using SSIS, and it's all got a bit harder with 64 bit machines. The main issues are:

There is no 64 bit OLEDB provider for Excel
So the packages cannot be executed by 64 bit code. This meant that the programmatic route for running packages with the classes in the Microsoft.SqlServer.Dts.Runtime namespace no longer works. The error you get is typically unhelpful - 'AcquireConnection method call to the connection manager "EXCEL: Source" failed with error code 0xC00F9304'. You can also get error code 0xC020801C.

The workaround is to run a package using the 32 bit version of dtexec.exe, which should be installed in Program Files (x86)\Microsoft SQL Server\100\DTS\Binn. This is also the fix if scheduling packages with the SQL Agent; your job steps need to be of type CmdExec instead of SSIS Package, and you have to learn the command line syntax of dtexec.

Overall, something of a pain, but I've not yet found anything I can't do with dtexec.exe and its command line parameters.

The Registry settings for the 32 bit OLEDB provider for Excel have moved
There are a several registry settings that you may need to make the OLEDB provider for Excel work, as described here and here. When running a package in 32 bit mode on a 64 bit server, you find that it uses registry keys in a different location.

Values that were in HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel are now in HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.

Note also that the paths to the providers have changed, so for example "Win32"="C:\Windows\system32\msexcl40.dll" becomes "Win32"="C:\Windows\syswow64\msexcl40.dll"

Although my emphasis here has been on Excel providers, and SSIS, the issues and resolutions can apply to other OLEDB providers and environments, as there are several OLEDB providers that are not expected to have 64 bit releases (the Exchange OLEDB provider, and the FoxPro one are 2 that I'm aware of)

1 comments:

Ivan said...

David,

The leading provider of third-party components CozyRoc has just released components for handling Excel workbooks. They can be used in both 32bit and 64bit mode and doesn't have the issues of the Microsoft Jet based Excel provider. You can find more about these new components by visiting CozyRoc's web page: http://www.cozyroc.com

P.s.
The toolkit includes also new components for integration with Microsoft Dynamics CRM. Just thought you might be interested in this information as well.