Microsoft SQL Server Integration Services packages use connections to perform different tasks and to implement Integration Services features:
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.
ConnectionStringproperty that you set at design time; at run time, a physical connection is created using the value in the connection string property.
SQL Server Integration Services provides different types of connection managers that enable packages to connect to a variety of data sources and servers:
The following table lists the connection manager types that SQL Server Integration Services provides.
|ADO||Connects to ActiveX Data Objects (ADO) objects.|
|ADO.NET||Connects to a data source by using a .NET provider.|
|CACHE||Reads data from the data flow or a cache file (.caw), and can save data to the cache file.|
|DQS||Connects to a Data Quality Services server and a Data Quality Services database on the server.|
|EXCEL||Connects to an Excel workbook file.|
|FILE||Connects to a file or a folder.|
|FLATFILE||Connect to data in a single flat file.|
|FTP||Connect to an FTP server.|
|HTTP||Connects to a webserver.|
|MSMQ||Connects to a message queue.|
|MSOLAP100||Connects to an instance of SQL Server Analysis Services or an Analysis Services project.|
|MULTIFILE||Connects to multiple files and folders.|
|MULTIFLATFILE||Connects to multiple data files and folders.|
|OLEDB||Connects to a data source by using an OLE DB provider.|
|ODBC||Connects to a data source by using ODBC.|
|SMOServer||Connects to a SQL Server Management Objects (SMO) server.|
|SMTP||Connects to an SMTP mail server.|
|SQLMOBILE||Connects to a SQL Server Compact database.|
|WMI||Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.|
A connection manager can be created at the package level or the project level.
You can use connection managers created at the project level in place of data sources to share connections to sources.