Connection Managers

SQL Server Integration Services uses the Connection Manager as a logical representation of a connection. At design time, we can 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 we set at design time; at run time, a physical connection is created using the value in the connection string property.

---

The following lists the build-in connection managers:
1. ADO: Connects to ActiveX Data Objects (ADO) objects.
2. ADO.NET: Connects to a data source by using a .NET provider.
3. CACHE: Reads data from the data flow or from a cache file (.caw), and can save data to the cache file.
4. EXCEL: Connects to an Excel workbook file.
5. FILE: Connects to a file or a folder.
6. FLATFILE: Connect to data in a single flat file.
7. FTP: Connect to an FTP server.
8. HTTP: Connects to a Web server.
9. MSMQ: Connects to a message queue.
10. MSOLAP100: Connects to an instance of SQL Server Analysis Services or an Analysis Services project.
11. MULTIFILE: Connects to multiple files and folders.
12. MULTIFLATFILE: Connects to multiple data files and folders.
13. OLEDB: Connects to a data source by using an OLE DB provider.
14. ODBC: Connects to a data source by using ODBC.
15. SMOServer: Connects to a SQL Server Management Objects (SMO) server.
16. SMTP: Connects to an SMTP mail server.
17. SQLMOBILE: Connects to a SQL Server Compact database.
18. WMI: Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.

DQS: Connects to DQS Server. New addition in SSDT.

Open the SSISStudy / SSISStudy2012 project created earlier. Right click in the Connection manager area. It lists all the possible connection like OLE DB, ADO.NET, etc. Select New Connection.

Select the connection type in Add SSIS Connection Manager and select Add. This should open the connection manager Configure window. For example, select ADO and click Add.

Click on New.

Under Provider, select the Provider type. Click OK.

Under the Server name, select the Server instance. Specify the authentication type and select the database under Connect to database.

Click on All. This opens the Extended properties for the connection manager.

Click on Test Connection and then OK.

On similar lines, we can select and configure other connection managers.

Click for NEXT article in the sequence.

Please feel free to correct me by commenting your suggestions and feedback.