SSIS Connection Managers

  • Article

 

In SSIS, almost all of the built-in components are configured by using Connection Managers. There are a plethora of connection managers built-in, and you can also write your own to plug in to the architecture. Only a subset of them applies to databases and traditional data access. The database-specific Connection Managers are:

  • ADO
  • ADO.NET
  • EXCEL
  • FILE, MULTIFILE, FLATFILE, MULTIFLATFILE
  • MSOLAP90 (Analysis Services)
  • ODBC
  • OLEDB
  • SQLMOBILE [1]

When you add a Connection Manager to as SSIS package, by choosing "New Connection" from the context menu you get a list of all of the Connection Manager types, as shown in figure 2. There are also shortcuts for specific Connection Managers, such as the OLE DB Connection Manager.

**Figure 2: SSIS Built-in Connection Managers **

Choosing a Connection Manager produces a list of data providers. The list of providers in the Provider drop-down list will reflect the type of Connection Manager you are using. ADO and OLE DB connection managers display a list of OLE DB provider installed on the local machine. OLE DB provider information lives in the Windows registry. The ADO.NET provider dialog displays a list of ADO.NET 2.0 data providers. You can also choose to use any of the OLE DB data providers through ADO.NET using the ADO.NET OleDb bridge provider, although there is usually no reason to do this. If you need an OLE DB provider, use an OLE DB connection manager. The ODBC connection manager uses the ADO.NET - Odbc bridge provider, so the Provider dropdown list simply contains the word "ODBC". A list of the available ODBC data sources is available as part of the dialog. Selecting "Use Connection String" and pressing the Build button invokes the ODBC control panel applet so you can build a custom ODBC data source.

To specify connection information, the main body of the dialog contains the most commonly used configuration parameters. There will be a custom dialog page body if one exists for the provider; an example of a provider with a custom dialog is the SQL Native Client OLE DB provider. Some providers may not have a dialog page with commonly used parameters at all. You can specify "advanced" connection properties or specify all of the connection properties for providers without a dialog page by clicking the "Advanced" tab of the main Connection Manager dialog. Providers are allowed to specify as many connection properties as are useful, each as a name/value pair. The standard connection properties vary by API. As an example, the property called "Data Source" in OLE DB is called "Server" in ADO.NET.

[1] The SSIS connection manager SQLMOBILE uses SQL Server Compact Edition as a data source; the original name for SQL Server Compact Edition was SQL Server Mobile Edition.

**Note: **This article has been ported over from http://ssis.wik.is/. 
Note: If you were the owner of above article, update it to the latest information or with other appropriate information.