An Excel Connection Manager in SSIS is used to connect SQL Server Integration Services Packages with the Excel files or Excel workbooks.
Configuring Excel Connection Manager in SSIS
The below screenshot shows you the data present in the Excel File, and we are going to use the same in this example
Once you created new Project under SSIS. Lets concentrate on the Connection Managers Folder.
Right-click on the Connection Managers folder present in the solution explorer and select the New Connection Manager option from the context menu.
When you click on the New Connection Manager option, an Add SSIS Connection Manager window will open. Use this to select the connections managers from the list.
Here we selected the Excel connection manager from the list. Once we selected the SSIS EXCEL connection manager, another window of Excel Connection Manager opened to configure the file path.
Click on the Browse button will open a new window to browse the drive.
From the above screenshot, you can observe that we selected the COPY PRODUCTS.xls file. And the file is in Microsoft Excel 97-2003 format. So we didn’t change the Excel Version.
If you observe the data present in the COPY PRODUCTS.xls file, its first row has the column names like color, Product Name, and Price. So Please checkmark the First row has column names option.
Click the ok button to finish configuring the Excel Connection Manager.
From the above image, you can see that we successfully created Excel Connection Manager in SSIS.