Excel Connection Manager in SSIS

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 screenshot below shows you the Excel file’s data, and we will use the same in this example.

Excel File 1

Once you created a new Project under SSIS. Let’s concentrate on the Connection Managers Folder.

Solution Explorer 2

Right-click on the Connection Managers folder in the solution explorer and select the New Connection Manager option from the context menu.

New Connection 3

When you click on the New option, an Add SSIS Connection Manager window will open. Use this to select the one from the list.

Excel Connection Manager in SSIS 4

Here we selected the SSIS Excel connection manager from the list. Once we set this, another window of Excel Connection Manager opened to configure the file path.

Excel Connection Manager in SSIS 5

Click the Browse button to open a new window to browse the drive.

Choose Excel File from local hard drive 6

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 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 that has a column names option.

Excel Connection Manager in SSIS 7

Click the ok button to finish configuring the Excel Connection Manager.

Excel Connection Manager in SSIS 9

From the above image, you can see that we successfully created Excel Connection Manager in SSIS.

Comments are closed.