Integration Services (SSIS) Connections
Microsoft SQL Server Integration Services packages use connections to perform different tasks and to implement Integration Services features:
Connecting to source and destination data stores such as text, XML, Excel workbooks, and relational databases to extract and load data.
Connecting to relational databases that contain reference data to perform exact or fuzzy lookups.
Connecting to relational databases to run SQL statements such as SELECT, DELETE, and INSERT commands and also stored procedures.
Connecting to SQL Server to perform maintenance and transfer tasks such as backing up databases and transferring logins.
Writing log entries in text and XML files and SQL Server tables and package configurations to SQL Server tables.
Connecting to SQL Server to create temporary work tables that some transformations require to do their work.
Connecting to Analysis Services projects and databases to access data mining models, process cubes and dimensions, and run DDL code.
Specifying existing or creating new files and folders to use with Foreach Loop enumerators and tasks.
Connecting to message queues and to Windows Management Instrumentation (WMI), SQL Server Management Objects (SMO), Web, and mail servers.
To make these connections, Integration Services uses connection managers, as described in the next section.
Connection Managers
Integration Services uses the connection manager as a logical representation of a connection. At design time, you set the properties of a connection manager to describe the physical connection that Integration Services creates when the package runs. For example, a connection manager includes the ConnectionString property that you set at design time; at run time, a physical connection is created using the value in the connection string property.
A package can use multiple instances of a connection manager type, and you can set the properties on each instance. At run time, each instance of a connection manager type creates a connection that has different attributes.
SQL Server Integration Services provides different types of connection managers that enable packages to connect to a variety of data sources and servers:
There are built-in connection managers that Setup installs when you install Integration Services.
There are connection managers that are available for download from the Microsoft website.
You can create your own custom connection manager if the existing connection managers do not meet your needs.
Built-in Connection Managers
The following table lists the connection manager types that SQL Server Integration Services provides.
| Type | Description | Topic |
|---|---|---|
| ADO | Connects to ActiveX Data Objects (ADO) objects. | ADO Connection Manager |
| ADO.NET | Connects to a data source by using a .NET provider. | ADO.NET Connection Manager |
| CACHE | Reads data from the data flow or from a cache file (.caw), and can save data to the cache file. | Cache Connection Manager |
| DQS | Connects to a Data Quality Services server and a Data Quality Services database on the server. | DQS Cleansing Connection Manager |
| EXCEL | Connects to an Excel workbook file. | Excel Connection Manager |
| FILE | Connects to a file or a folder. | File Connection Manager |
| FLATFILE | Connect to data in a single flat file. | Flat File Connection Manager |
| FTP | Connect to an FTP server. | FTP Connection Manager |
| HTTP | Connects to a webserver. | HTTP Connection Manager |
| MSMQ | Connects to a message queue. | MSMQ Connection Manager |
| MSOLAP100 | Connects to an instance of SQL Server Analysis Services or an Analysis Services project. | Analysis Services Connection Manager |
| MULTIFILE | Connects to multiple files and folders. | Multiple Files Connection Manager |
| MULTIFLATFILE | Connects to multiple data files and folders. | Multiple Flat Files Connection Manager |
| OLEDB | Connects to a data source by using an OLE DB provider. | OLE DB Connection Manager |
| ODBC | Connects to a data source by using ODBC. | ODBC Connection Manager |
| SMOServer | Connects to a SQL Server Management Objects (SMO) server. | SMO Connection Manager |
| SMTP | Connects to an SMTP mail server. | SMTP Connection Manager |
| SQLMOBILE | Connects to a SQL Server Compact database. | SQL Server Compact Edition Connection Manager |
| WMI | Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server. | WMI Connection Manager |
Connection Managers Available for Download
The following table lists additional types of connection manager that you can download from the Microsoft website.
Important
The connection managers listed in the following table work only with Microsoft SQL Server 2012 Enterprise and Microsoft SQL Server 2012 Developer.
| Type | Description | Topic |
|---|---|---|
| ORACLE | Connects to an Oracle <version info> server. | The Oracle connection manager is the connection manager component of the Microsoft Connector for Oracle by Attunity. The Microsoft Connector for Oracle by Attunity also includes a source and a destination. For more information, see the download page, Microsoft Connectors for Oracle and Teradata by Attunity. |
| SAPBI | Connects to an SAP NetWeaver BI version 7 system. | The SAP BI connection manager is the connection manager component of the Microsoft Connector for SAP BI. The Microsoft Connector for SAP BI also includes a source and a destination. For more information, see the download page, Microsoft SQL Server 2008 Feature Pack. |
| TERADATA | Connects to a Teradata <version info> server. | The Teradata connection manager is the connection manager component of the Microsoft Connector for Teradata by Attunity. The Microsoft Connector for Teradata by Attunity also includes a source and a destination. For more information, see the download page, Microsoft Connectors for Oracle and Teradata by Attunity. |
Custom Connection Managers
You can also write custom connection managers. For more information, see Developing a Custom Connection Manager.
Related Tasks
For details about how to add or delete a connection manager in a package, see Add, Delete, or Share a Connection Manager in a Package.
For details about how to set the properties of a connection manager in a package, see Set the Properties of a Connection Manager.
Related Content
Video, Leverage Microsoft Attunity Connector for Oracle to enhance Package Performance, on technet.microsoft.com
Wiki articles, SSIS Connectivity, on social.technet.microsoft.com
Blog entry, Connecting to MySQL from SSIS, on blogs.msdn.com.
Technical article, Extracting and Loading SharePoint Data in SQL Server Integration Services, on msdn.microsoft.com.
Technical article, You get "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER" error message when using Oracle connection manager in SSIS, on support.microsoft.com.