Connection Managers in SQL Server 2005 Integration Services SSIS

By: Edgewood Solutions | Read Comments | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More

Problem
In SQL Server 2000 connections were made by dragging a connection type to the designer. The problem was that the number of connections was limited, and many of them had to be made using a scripting language like VBScript or JScript. The arrival of SQL Server 2005 included a brand-new SQL Server Integration Services that offers many ways to extract, tranform, and load data. Along with those new methods came a certain re-arrangement of objects as well as new ways to connect to the objects needed to move and manipulate data. Perhaps the most important is the new Connection Manager.

Solution
The Connection Manager is the gateway to all things data in SQL Server Integration Services. Whereas many connections had to be made manually using a scripting language in SQL Server 2000 Data Transformation Services, a variety of connection types are available in SSIS without scripting. When you open the Business Intelligence Development Studio (BIDS), create a new Integration Services project. When the SSIS Designer opens the Connection Manager is along the bottom of the designer. To create a new connection, right-click the area inside the Connection Managers section. A number of options are available inside the menu:

Connection type	Purpose of connection
OLEDB connection	Used to connect to any data source requiring an OLEDB connection (i.e., SQL Server 2000)
Flat file connection	Used to make a connection to a single file in the File System. Required for reading information from a File System flat file
ADO.Net connection	Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task
Analysis Services connection	Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
File connection	Used to reference a file or folder. The options are to either use or create a file or folder

In addition to these connections, other connections can be created by clicking New Connection. When you select it a new screen appears with the additional connection types:

Next Steps

• Investigate the possibility of migrating DTS packages to SSIS packages
• Review connection managers available in SSIS 2005
• Look at how to dynamically specify a flat file connection in SSIS
• Read more tips on DTS and SSIS on MSSQLTIPS

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code