SSIS Package Fails After Move to 64-bit

3
votes

We've got a a series of SQL Server Integration Services packages that copy data from a few MS Access databases into a SQL Server 2008 database. There is one parent package that calls the various sub-packages, and that parent package is initiated by a user that runs a .bat file that executes the package like so:

dtexec /f "\\networkshare\package.dtsx" /CHECKPOINTING OFF /REPORTING EWCDI

This has worked fine for several years. Our IT department has begun upgrading our 32-bit Windows XP workstations to 64-bit Windows 7 and since they've upgraded the workstations of these users, the package has been failing, giving the error

-1071607037,0x,SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MICROSOFT.JET.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

My workstation has not yet been upgraded from Windows XP and I'm still able to run the packages but my ability to postpone the upgrade is running out and I need to figure out a solution as soon as possible. I've found many articles and posts related to this in my efforts to resolve the issue. Among the things I've tried are:

  • After ensuring that the users had the Client Tools and Business Intelligence Development Studio installed and that the path is valid, changing the contents of the .bat file to specifically reference “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” in the hope that the 32-bit JET provider would be used
  • Researched the Run64BitRuntime setting but this appears to only have an effect while debugging and won't help me
  • Researched adding the /X86 flag to the command line but according to the MSDN article on dtexec, this only has an effect if the SQL Server Agent is running the task
  • The last thing I've tried was to install the Microsoft Access Database Engine 2010 Redistributable and change the connection string from "Provider=Microsoft.Jet.OLEDB.4.0;" to "Provider=Microsoft.ACE.OLEDB.12.0;". I can't seem to get off the ground with this one. If I try to create a new connection in BIDS and set the provider to "Microsoft Office 12.0 Access Database Engine OLE DB Provider" and test the connection, I get the error "Test connection failed because of an error in initializing provider. Unspecified error".

I'm just about at a loss for what else I can try and looking for any help at all, even if it's trying the things I've already tried, maybe I've configured something wrong while trying them originally, not sure.

Any help would be immensely appreciated!

3
ms-accessssisjetms-jet-ace
How are the sub packages being called? The parent package might be running in 32 bit space but, depending on how the subs are invoked, they could be running in 64 bit space. – billinkc
You can get the 64 bit ACE drivers from microsoft.com/en-ie/download/details.aspx?id=13255 They are backwardly compatible. – Fionnuala
@billinkc The sub packages are called from the parent package with an Execute Package task. I'm not sure where I saw it but if I'm remembering correctly, I saw an article yesterday that seemed to indicate that things like 32/64-bit would flow down from a parent package – Supermercado
@Remou That page is actually where I downloaded the driver before trying to change the provider in my connection string to Microsoft.ACE.OLEDB.12.0. However, I can't seem to get that to work after installing it. – Supermercado
You are correct. I was thinking of the run in same process space flag. So, when you run it using the dtexec in x86, you get the same error? Any chance you are using a DSN too? – billinkc

3 Answers

3
votes

In SQL Agent job or by just executing the package by itself there is a tab called "Execution options", you can select "Use 32 bit runtime" option

2
votes

By default, SQL Server puts the 64-bit version of DTEXEC in the path. The 32-bit version should be located somewhere like C:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn and would need to be called directly. I had the same issue with the ACE drivers and Excel files.

See this for more information.

2
votes

I was able to run it successfully by changing debugging setting in project property page. Property to change is Run64BitRuntime -> set this to false.