SQL Server Integration Services (SSIS) Connection Managers

By: Ray Barley

Overview

This section is our second step in creating a simple SSIS package from scratch.  SSIS packages typically interact with a variety of data sources.  All of the most common data sources are supported right out of the box.  There is a Connection Managers area on the design surface where you can specify each data source that you will access.  In this section we will add two data sources to our package - one to access the AdventureWorksDW database and another to access our Excel spreadsheet.

Explanation
To begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Expand the SSIS Packages node under the Tutorial-Sample-1 project in Solution Explorer and you will see the following:

Double click on the SSIS package CreateSalesForecastInput.dtsx to open the package.  You should see the Connection Managers area of the designer in the middle of the screen near the bottom as shown below:

We are going to add a connection manager for the AdventureWorksDW database and another for the Excel spreadsheet that we will create and use as the sales forecast input.  To add the AdventureWorksDW connection manager simply right click inside the Connection Managers area then choose New OLEDB Connection from the popup menu.  The Configure OLEDB Connection Manager will be displayed; click the New button to display the Connection Manager dialog and fill it in as follows:

In my case the AdventureWorksDW database is on my local machine; change the Server name property as necessary for your environment.  If possible choose Use Windows Authentication to avoid having to specify a user id and password; this would be sensitive information that should be encrypted.  Click the Test Connection button to make sure you can connect to the database.  Click OK to complete this step.

To add a connection manager for our Excel spreadsheet, right click inside the Connection Managers area then choose New Connection from the popup menu, then select EXCEL from the Add SSIS Connection Manager dialog.  The Excel Connection Manager dialog will be displayed; enter a file name as shown below:

We are now finished adding the necessary Connection Managers to our package.