{"id":12103,"date":"2022-09-05T06:53:00","date_gmt":"2022-09-05T04:53:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=12103"},"modified":"2022-10-11T14:46:40","modified_gmt":"2022-10-11T12:46:40","slug":"ssis-connection-string-to-load-excel-files","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/ssis-connection-string-to-load-excel-files\/","title":{"rendered":"SSIS connection string to load Excel files"},"content":{"rendered":"\n

How to build an SSIS connection string to connect to Excel files and load data into a database?<\/em><\/strong><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

Syntax for an OLEDB connection string to load Excel files with SSIS<\/h2>\n\n\n\n

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:<\/p>\n\n\n\n

Data Source=C:\\Dossier\\file.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\u2033;<\/pre>\n\n\n\n
The ConnectionString property in English contains the following:<\/p>\n\n\n\n
  1. Data Source<\/em>: for an Excel workbook this is the file path<\/li>
  2. Provider<\/em>: type of connection, here it is an OLEDB 12.0 connection<\/li>
  3. Extended Properties<\/em>: to add additional options<\/li><\/ol>\n\n\n\n
    Classic Excel connection error<\/h3>\n\n\n\n
    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.<\/p>\n\n\n\n
    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<\/a> on your machine. After installation, reboot the machine to avoid configuration errors.<\/p>\n\n\n\n
    \n
    Access Database Engine 2016 Excel driver for SSIS<\/a><\/blockquote>