Working with the ACE provider in the tabular designer

The Office ACE provider is used to import from text files, Excel files, and Access files.

Just like for any data provider, you need to install the provider on both the client side (machine with BIDS) and on the server side (both the machine running the workspace DB instance and the machine running the deployment DB instance). The reasons for this are explained in the impersonation documentation. The BIDS client uses the data provider (and the current user’s credentials) for Preview and Filter, Edit Table Properties, and Partition Manager operations. The server requires the data provider for processing (both at import time and processing time).

There are two ways to get the ACE provider:

1. Install Office (which includes the ACE provider)
2. Download the ACE provider from the download centre

But before you run off and get the provider for the BIDS machine, you need to decide which version of the provider to get – 32 bit or 64 bit. The ACE provider doesn’t support side-by-side installation of the 32 bit and 64 bit providers. Since you can only pick one provider, choose wisely. The following table shows the appropriate choices for the BIDS machine:

You might wonder why there is a dependency on the location of the workspace database server. That is because the AS requires that the bitness of the provider matches the bitness of the workspace DB instance for processing.  The bitness of the AS instance always matches the bitness of the OS. Therefore, if your workspace DB is local, the bitness of the ACE provider on the BIDS machine must match the OS, and this trumps all other considerations.

You might also wonder how it is that BIDS, a 32 bit application, does not have the same restriction as the AS instance and can function when a 64 bit provider is installed. BIDS can cope because one of our developers did some black magic behind the scenes to solve this problem. Whenever you start the import or table preview process for text, Excel, or Access file on a machine with a 64 bit ACE provider installed, the tabular designer automatically launches a new child process called ASConnectionHelper. ASConnectionHelper is a 64 bit helper application that loads the 64 bit ACE provider. BIDS talks to ASConnectionHelper over a secure connection to get the data, and all is well. Each user on the machine gets a single ASConnectionHelper process per Visual Studio session, and the process is terminated when VS is terminated. This is a pretty nifty way to solve the bitness mismatch problem. (Note: don’t kill ASConnectionHelper while BIDS is running, that is bad.)

One other note about the bitness options described above. The table above shows a workaround to a problem encountered by some users. Imagine you have a 32 bit version of Office on a 64 bit box. Imagine that you can’t (or won’t) change the version of Office on your box to a 64 bit version. How can you import from text files in BIDS? The solution here is to use a remote workspace database server. On the remote server, install the appropriate version of the ACE provider for the OS. Then in BIDS, you can model happily using your 32 bit Office install and all will be well.

If that was all just too complex, let me give you some simple guidance. It is best to install a version of Office on your BIDS machine that matches the bitness of your OS. If you do that, everything will be fine. If you don’t do that, you can recover, but recovery is kind of annoying.

Related: a big ole warning from Microsoft support that says you really shouldn’t be using the ACE provider in server side application anyway. Also related - Dave Wickert’s overview of the ACE provider when using PowerPivot for SharePoint.