Tuomas Kesti

How to run an SSIS package with Excel data source or destination in 64-bit environment?

/in , /by

So, I had a following scenario for one of our customers:

  • Need to execute an SSIS package with Excel and Dynamics 365 data sources and push the data over to Azure SQL DB
  • In the dev environment, the BIDS is 32-bit

I had actually a few different types of challenges in deploying the package to the production server from development environment. It took me a while to find out a solution to these, so I thought that it might be helpful for others struggling with the same issues to write out a small blog post.

How the data source and destination sensitive information gets deployed with the SSIS package?

This is configured in the dev environment in BIDS. It is basically a project option that needs to be set (EncryptSensitiveWithPassword):

You need to make sure also that the SSIS package level option is set to the same option. What this does is that it includes the sensitive information (for example the data source and destination connection string passwords) to the SSIS package but all that is protected with a password. Then in the execution server side, where you execute this package for example with SQL Agent job, you need to provide this password to be able to see or modify the connection options.

What does the project level connection manager mean in SSIS?

The next challenge that I had in this one was that I had a project level connection manager of Dynamics 365 specified in the SSIS project. This means that data connections using this type of connector do not get included to the SQL Agent job when you specify the SSIS package to be executed. What you need to do is that you specify the connection manager to be a package level instead of project level. This is done in the BIDS by right-clicking the connection manager and setting the “Convert to Package Connection” option. By doing this, all the connections using this connection manager are also used in the execution server side.

How to manage with excel data connections in 64-bit server environment?

When I deployed the SSIS package to our production server and created a SQL Agent job which is going to execute the package in a scheduled manner, it started to throw errors of these excel data sources. In detail the error was “The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered”:

The resolution is that you need to install Microsoft Access Database engine to the server and then set the SQL Agent job to be run in 32-bit mode. You can find the Access DB engine download package here: https://www.microsoft.com/en-us/download/details.aspx?id=13255

And at least in our case, we needed to install the 32-bit version of the Access DB engine to make this work. I believe it is due to the fact that as the BIDS is 32-bit, then it builds the SSIS package to be 32-bit as well. Another step to success was to set the “Use 32-bit runtime” option:

With these options set, the package was executed successfully and data flows from excel files to Dynamics 365.

By the way, absolutely the easiest way to implement these types of scenarios against Dynamics 365 is to use the KingswaySoft Dynamics 365 SSIS Integration Toolkit. I have used it in several projects and it is by far the best Dynamics migration/integration tool I have used so far if you want to develop a no-code migration against Dynamics 365. So, I strongly recommend that.