SQL Server Integration Services (SSIS) Connection Managers


By:
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:

pen project

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

olution explorer open package

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:

mpty connection managers

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:

dventureworksdw connection manager

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:

xcel connection manager

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






Comments For This Article




Sunday, August 13, 2017 - 10:45:14 PM - ayub Back To Top (64806)

hi  my job failed in test and succeded in prod ....the error showing when i open my package ----connection manager is of how to on the connction manager 















get free sql tips
agree to terms