May 3, 2018

Handling excel connections in SSIS

Looking to integrate data from Excel through SQL Server Integration Services.Well there are a couple of things to keep in mind and this is a brief post to assist you with navigating these muddy waters.As you know, one of the first things to do is to add an Excel connection manager within the Data Flow task.The Connection Manager for Excel will be based on a specific excel version. The versions being:

Excel 2007-2010; or
Excel 2013; or
Excel 2016

By now you probably realise that the Excel connection manager is using the ACE Oledb providers and as such this is installed and registered when installing the Access Runtime. Below is a table matching the Excel version with the ACE.oledb provider (as it relates to the relevant Access Runtime that needs to be installed):

However there are some key points here to keep in mind when implementing Excel integration:

The ACE.oledb provider version needs to match the version that you are declaring in the connection manager. E.g. if you are declaring an Excel 2016 version then you must have the Acess 2016 Runtime installed
Visual Studio IDE is well known to be a 32-bit runtime environment and so you must install the 32-bit version of Access Runtime (which installs the 32-bit ACE.oledb provider)
You can’t install both the 32-bit and 64-bit Access Runtimes on the same machine and so you are likely to come up with issues if you already have 64-bit Office installed and are trying to install the required 32-bit runtime.
In general you should be fine using any of the ACE.oledb.XX providers to access Excel 2007-2010/2013/2016 .xlsx files. What you will need to do is simply declare in the Excel Connection Manager that the version is the one relating to the runtime that you have installed. For example, if you have Access 2010 Runtime installed then choose Microsoft Excel 2007-2010 (rather than Microsoft Excel 2016) as the Excel version in the Excel Connection Manager to connect to say an Excel 2016 workbook.
If you are getting conflict with Access run-time install, then you can always install the 32-bit version of the Runtime version that does not relate to the Office version you have installed already… e.g. if you already have the Access 2016 Runtime already installed (perhaps as part of Office 2016 install) then install the Access 2010 Runtime (32-bit) and use the Excel 2007-2010 version for the Excel Connection Manager