Learn more about SQL Server tools

   
   























Latest from MSSQLTips















SQL Server Integration Services (SSIS) Connection Managers

(Connection Managers)


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.







 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.