To connect an Integration Services package to a Microsoft Office Excel workbook requires an Excel connection manager.
You can create these connection managers from either the Connection Managers area in SSIS Designer or from the SQL Server Import and Export Wizard.
Connectivity components for Microsoft Excel and Access files
You may have to download the connectivity components for Microsoft Office files if they're not already installed. Download the latest version of the connectivity components for both Excel and Access files here: Microsoft Access Database Engine 2016 Redistributable.
The latest version of the components can open files created by earlier versions of Excel.
If the computer has a 32-bit version of Office, then you have to install the 32-bit version of the components, and you also have to ensure that you run the package in 32-bit mode.
If you have an Office 365 subscription, make sure that you download the Access Database Engine 2016 Redistributable and not the Microsoft Access 2016 Runtime. When you run the installer, you may see an error message that you can't install the download side-by-side with Office click-to-run components. To bypass this error message and install the components successfully, run the installation in quiet mode by opening a Command Prompt window and running the .EXE file that you downloaded with the
/quiet switch. For example:
C:\Users\<user name>\Downloads\AccessDatabaseEngine.exe /quiet
Create an Excel connection manager
To create an Excel connection manager from the Connection Managers area
In SQL Server Data Tools (SSDT), open the package.
In the Connections Managers area, right-click anywhere in the area, and then select New Connection.
In the Add SSIS Connection Manager dialog box, select Excel, and then configure the connection manager.
For information about the configuration options that are available for this connection manager, see Excel Connection Manager Editor.
To create an Excel connection from the SQL Server Import and Export Wizard
Start the 32-bit version of the SQL Server Import and Export Wizard.
On the Choose a Data Source page, for Data Source, select Microsoft Excel, and then configure the Excel connection.
For information about the configuration options that are available for this connection type, see Excel Connection Manager Editor.