How to build an SSIS connection string to connect to Excel files and load data into a database?
To connect, read and load data from Excel files with SSIS, it is necessary to build an OLEDB connection string. There are several ways to do this, a connection with the Excel Connection component of SSIS, using an ADO driver, or using an OLEDB connection.
Syntax for an OLEDB connection string to load Excel files with SSIS
Some settings are mandatory to build the OLEDB connection string for Excel. So here is how to build an OLEDB connection string to load Excel files from an SSIS package. First, to build an Excel connection string, use this example and adapt it to the project context. The OLEDB connection string for a Microsoft Excel file is therefore of the following type:
Data Source=C:\Dossier\file.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1″;
The ConnectionString property in English contains the following:
- Data Source: for an Excel workbook this is the file path
- Provider: type of connection, here it is an OLEDB 12.0 connection
- Extended Properties: to add additional options
Classic Excel connection error
Secondly, if the following Excel file connection error is displayed, the Excel Access Database Engine 2016 driver is not installed on the machine running the SSIS package.
The error in English: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. The installation file is called AccessDatabaseEngine.exe, it is mandatory to install the Office components on the local machine or on the server to connect to the Office file. Here is a complete tutorial to download and install the Excel 2016 driver for SSIS on your machine. After installation, reboot the machine to avoid configuration errors.
For your information, the driver is also called Microsoft Access 2016 Database Engine redistributable. Furthermore, this version of the driver is compatible with the following Microsoft operating systems, older systems are not listed here:
- Windows Server 2012 R2, Windows Server 2008 (Service Pack 2 and R2 versions), Windows Server 2003 R2 (in 32-bit and 64-bit versions).
- Windows 10, Windows 8 and Windows 7.
Finally, to get more information, visit directly the official driver download page on Microsoft.com.
Be the first to comment