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
In this example We are going to show, How to create or configure Excel Connection Managers in SSIS (SQL Server Integration Services)
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. If you look at the Solution explorer we have three folders. For now 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 form will be opened. Use this to select the connections managers from the list it is provided.
Here we selected the Excel connection manager from the list. Once we selected the EXCEL connection manager then another window form of Excel Connection Manager will be opened to configure the file path.
Click on the Browse button from the above screenshot, it will open 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 changed 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 etc. So Please check mark the First row has column names option.
Click ok button to finish configuring the Excel Connection Manger.
From the above screenshot you can observe that, We successfully created Excel Connection Manager in SSIS.
Thank you for Visiting Our Blog