SSIS – Consuming Microsoft Access or Microsoft Excel Data Sources in 64-bit Environments

The problems discussed in this post are far from new however they are problems still discussed frequently on various forums and problems which, for some reason or another, are still difficult to debug. 

Please Note: The content of this post applies to developing and deploying in a 64-bit environment therefore some of the workarounds and recommendations in this post will not apply to 32-bit environments.  This post does however explain how Business Intelligence Development Studio (BIDS), Integration Services and SQL Server Agent interact with SSIS packages which need to consume 32-bit and 64-bit providers so it may still answer some of your questions.

Problem

The main problem is that there is no 64-bit version of the Microsoft Jet 4.0 OLE DB Provider therefore SSIS packages which use this provider to consume Microsoft Access or Microsoft Excel data sources fail to execute when:

  1. Developed and debugged in a 64-bit environment
  2. Deployed to a 64-bit environment

There are also two other issues related to using BIDS in a 64-bit environment which add to the confusion and frustration of developing and debugging SSIS packages that consume Microsoft Access and Microsoft Excel data sources:

  1. BIDS is a 32-bit application.  What this means is that when BIDS is installed in a 64-bit environment and used to develop SSIS packages, BIDS will not recognise any 64-bit providers installed in the environment therefore you will not be able to consume such providers in your packages
  2. Although BIDS is a 32-bit application, it uses the 64-bit SSIS Runtime (by default) to execute and debug packages therefore packages that consume 32-bit only providers fail.

Solution

The solution would be for Microsoft to develop a 64-bit version of the Microsoft Jet 4.0 OLE DB Provider and to create a 64-bit version of BIDS.  Well they have (sort of) in relation to creating a 64-bit provider and this post provides two examples of using it as an alternative to using the Microsoft Jet 4.0 OLE DB Provider.

The Microsoft Office 12.0 Access Database Engine OLE DB Provider (a.k.a. Microsoft ACE OLE DB 12.0 Provider) is a 64-bit (and 32-bit) provider which can be used to consume Microsoft Access and Microsoft Excel data sources in SSIS packages executing on 64-bit environments.  Don’t get too excited though as using this provider can be just as frustrating as using the Microsoft Jet 4.0 OLE DB Provider (You’ll see why shortly).

There are four solutions (I’ve tried) that can be implemented to get around development issues relating to consuming Microsoft Access or Microsoft Excel data sources in SSIS packages:

  1. Use the 32-bit Microsoft Jet 4.0 OLE DB provider in both development and deployment environments
  2. Use the 32-bit Microsoft ACE OLE DB 12.0 Provider in both development and deployment environments
  3. Use the 32-bit Microsoft Jet 4.0 OLE DB provider in development and the 64-bit Microsoft ACE OLE DB 12.0 in deployment (My recommendation)
  4. Use the 32-bit Microsoft ACE OLE DB 12.0 provider in development and the 64-bit Microsoft ACE OLE DB 12.0 in deployment

Each of these solutions have their own pros and cons so hopefully this post will assist you with debugging and help you determine which solution to implement for your requirement.

Setting Up BIDS

Please Note: The following steps are required for all solutions discussed in the post.

As stated above, BIDS is a 32-bit application so it will not recognise any 64-bit providers therefore you cannot use any 64-bit providers during development.  That said, when you execute (debug) a package in BIDS on a 64-bit OS, BIDS will use the 64-bit SSIS runtime by default, not the 32-bit runtime.  Given this, if a package uses the Microsoft Jet 4.0 OLE DB Provider or the 32-bit Microsoft ACE OLE DB 12.0 Provider to consume a Microsoft Access or Microsoft Excel data source, it will fail with an error similar to the following:

Microsoft Jet 4.0 OLE DB Provider

[Connection manager "Cruises"] Error: 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".

Microsoft ACE OLE DB 12.0 Provider

[Connection manager "Cruises"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft ACE OLE DB 12.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".

Fixing this error is very simple.  All you need to do is tell BIDS to use the 32-bit SSIS Runtime during debugging by:

  1. Right clicking on your project in BIDS and selecting Properties
  2. When the Property Page dialog launches, select the Debugging configuration property page (on the left).  BTW, notice that the title of this dialog is “32-bit Runtime Property Pages”.  A clear indication that BIDS is a 32-bit application. 
  3. Set the Run64BitRuntime property to false

image

Now your packages will execute from within BIDS without 64-bit provider errors.  That said, there are a couple of things you need to note about this setting:

  1. The Run64BitRuntime property is project specific therefore you need to set it for every project
  2. This setting is not stored in a SSIS package therefore changing this in your development environment does not affect the deployment environment and will not guarantee that your package will execute on a 64-bit OS.

Solution 1:  32-bit Microsoft Jet 4.0 OLE DB Provider in Both Environments

This solution is the one implement most frequently.  It involves telling BIDS to use the 32-bit SSIS Runtime during development (see “Setting Up Bids” above) and telling Integration Services on the deployment machine to use the 32-bit SSIS runtime during execution.  This is where things can get a little messy.

As stated above, setting the Run64BitRuntime to false during development does not affect how the package is executed when deployed.  Given this, a package run on a 64-bit OS may fail if:

  1. The package is executed using the DTEXEC Utility via a command prompt.
  2. The package is executed via a SQL Server Agent job

Please Note: The DTEXECUI Utility (not to be confused with the DTEXEC Utility discussed in this post) is a 32-bit graphical tool therefore packages run using this utility in a 64-bit environment run in Windows on Win32 (WOW).  Therefore, packages that consume the Microsoft Jet 4.0 OLE DB Provider or the 32-bit Microsoft ACE OLE DB 12.0 Provider (not the 64-bit provider) will execute correctly using this tool as long as the required providers have been installed.

By default, Integration Services installs the 64-bit version of the DTEXEC utility on a 64-bit OS during setup. If you need to execute packages in 32-bit mode, you will need to install the 32-bit version of the DTEXEC utility. This can be achieved by installing the Client Tools or BIDS during setup (or by running setup again at a later stage).  This is where some people come unstuck.

If you work for an organisation that doesn’t allow the Client Tools or BIDS to be installed on deployment servers,  you will need to look at using Solution 3 or 4.  If you can install Client Tools or BIDS, this still doesn’t mean all is sweet and that you’ll experience error free deployments.

By default, a 64-bit OS that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt by default. This is because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. 

If you are executing a package via a command prompt and nothing has been done to change the order of the directory paths within the PATH environment variable, your package will run just fine.  If the order of the directory paths within the PATH environment variable have changed, your package will fail unless you specify the full path to the 32-bit DTEXEC Utility.

If a package is executed via a SQL Server Agent job, it will execute using the 64-bit runtime (by default) and fail.  This is because the SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility which results in the 64-bit version being selected.  To get around this issue, you can:

  1. Check the “Use 32 bit runtime” option of the SQL Server Integration Services Package job step (found on the Execution Options tab) , or
  2. Use an Operating System (CmdExec) job step (instead of a SQL Server Integration Services Package job step) to execute your package as this enables you to specify the location of the 32-bit DTEXEC Utility (This blog post on BI Polar provides the steps required to do this), or
  3. Change the registry to point to the 32-bit version of DTEXEC (Not!),  Do not do this as it will affect all packages executed on the machine and make you Public Enemy No. 1 within your organisation, or
  4. Use solution 3 or 4.

If you want to use this solution, I recommend that you implement Step 1 as Step 2 requires you to configure command line parameters to execute your package which just adds complexity to the situation.

image

Solution 2:  32-bit Microsoft ACE OLE DB 12.0 Provider in Both Environments

This solution is similar to Solution 1 therefore all the problems (and workarounds) associated with development and deployment in Solution 1 apply to this solution as well.  There is however an additional problem associated with using this provider:

You cannot install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable on a 64-bit OS if the 64-bit version of Microsoft Office or Microsoft ACE OLE DB 12.0 Provider are installed.

image

If your development and/or deployment environments are configured as above (i.e. you have the 64-bit version of Microsoft Office or Microsoft ACE OLE DB 12.0 Provider installed on your machine), you can either:

  1. Uninstall the 64-bit versions of Microsoft Office and Microsoft ACE OLE DB 12.0 and install their equivalent 32-bit versions, or 
  2. Use Solution 1, 3 or 4

The reason why Step 2 states use Solution 1 is because I don’t see the point of uninstalling the 64-bit versions of Microsoft Office and/or Microsoft ACE OLE DB 12.0 Provider just so you can install the 32-bit version of the Microsoft ACE OLE DB 12.0 Provider.  It would be far easier to use the Microsoft Jet 4.0 OLE DB Provider instead.

Solution 3:  32-bit Microsoft Jet 4.0 OLE DB Provider in Development and the 64-bit Microsoft ACE OLE DB 12.0 in Deployment

This solution is the one I use and recommend.  It involves telling BIDS to use the 32-bit SSIS runtime during development (see “Setting Up Bids” above) and changing the provider used to consume Microsoft Access and Microsoft Excel data sources at runtime using a SSIS Package Configuration file.

So how do we achieve this?

Once you have configured BIDS to use the 32-bit SSIS Runtime, use the Microsoft Jet 4.0 OLE DB Provider to create a connection to your Microsoft Access or Microsoft Excel data source.  Once this is done, create a package configuration file by following these steps:

  1. Launch the Package Configurations Organizer Wizard and add a XML Configuration File
  2. Follow the steps in the wizard until you reach the Select Properties to Export page
  3. Locate the Microsoft Jet 4.0 OLE DB connection in the Connection Managers node.  Notice that the value property contains the text Microsoft.Jet.OLEDB.4.0.  This is the text that gets modified at deployment.
  4. Check the box next to the ConnectionString property of your connection
  5. Finish the rest of the wizard.

image

The next step is to tell BIDS to create a Deployment Utility for your package.

  1. Right click on your project in BIDS and select Properties
  2. When the Property Page Dialog launches, select the Deployment Utility configuration property page (on the left)
  3. Set the CreateDeploymentUtility property to true
  4. Build your project

image

Now that you’ve created a deployment utility for your package, you can change the provider setting of your package connection during deployment by following these steps:

  1. Ensure that the 64-bit Microsoft.ACE.OLEDB.12.0 provider has been installed in your deployment environment.
  2. Copy the files from the bin\Deployment directory of your SSIS Project to the deployment machine
  3. Execute the deployment utility by double clicking on the Integration Services Deployment Manifest file
  4. Follow the wizard steps until you reach the Package Configurations page
  5. Expand the nodes in the Configurations list (at the bottom on the page) until you find the connection you need to modify
  6. Locate the Value column of the connection properties and replace Microsoft.Jet.OLEDB.4.0 with Microsoft.ACE.OLEDB.12.0 (Remember to include the full-stops)
  7. Complete the rest of the deployment steps

image

That’s it.  When the package is executed, it will use the 64-bit version of the Microsoft ACE OLE DB 12.0 provider instead of trying to use the non-existent 64-bit Microsoft Jet 4.0 OLE DB Provider.

Please Note: The DTEXEC issues identified in Solution 1 may cause the package to fail if the version of DTEXEC being used to execute the package is the 32-bit version.  Please follow the steps outlined in the DTEXEC Utility documentation to ensure that the correct version of the utility is used to execute your package.

Please Also Note: Using the DTEXECUI graphical tool to execute your package will cause the package to fail.  This is because this utility is a 32-bit only utility and no 32-bit Microsoft.ACE.OLEDB.12.0 provider exists in the deployment environment (Refer to Solution 2 for details on Microsoft.ACE.OLEDB.12.0 provider installation requirements.)

Solution 4:  32-bit Microsoft ACE OLE DB 12.0 Provider in Development and the 64-bit Microsoft ACE OLE DB 12.0 in Deployment

At first glance this solution seems better than all the rest however this solution will experience the same problems as Solution 2 i.e.

You cannot install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable on a 64-bit OS if the 64-bit version of Microsoft Office or the Microsoft ACE OLE DB 12.0 Provider are installed.

If your development environment has the 64-bit version of the Microsoft Office or Microsoft ACE OLE DB 12.0 Provider installed, you will not be able to install the 32-bit version of the Microsoft ACE OLE DB 12.0 Provider unless you uninstall these products first.  This is why I don’t use this solution as I have the 64-bit version of Microsoft Office installed on my development machine.

If your development machine doesn’t have the 64-bit version of the Microsoft Office or Microsoft ACE OLE DB 12.0 Provider installed, this is the solution you should implement.

Conclusion

As you can see, there are a number of variables that can affect the successful development and deployment of SSIS packages in a 64-bit environment.  Hopefully some of the issues and workarounds I’ve identified will assist with debugging your solutions.

About these ads

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff is also a MCT specialising in SQL Server technologies and holds MCPD, MCITP, MCDBA and MCSD certifications.

4 Responses to SSIS – Consuming Microsoft Access or Microsoft Excel Data Sources in 64-bit Environments

  1. Pingback: Mr Wharty's Ramblings

  2. Pingback: OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses « Mr. Wharty's Ramblings

  3. Pingback: Reading Access 2010 .mdb files from SQL Server directly - elbsolutions.com Project List & Blog

  4. Pingback: Toko Buku Gramedia

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 599 other followers

%d bloggers like this: