Adding Connections Programmatically

The ConnectionManager class represents physical connections to external data sources. The ConnectionManager class isolates the implementation details of the connection from the runtime. This enables the runtime to interact with each connection manager in a consistent and predictable manner. Connection managers contain a set of stock properties that all connections have in common, such as the Name, ID, Description, and ConnectionString. However, the ConnectionString and Name properties are ordinarily the only properties required to configure a connection manager. Unlike other programming paradigms, where connection classes expose methods such as Open or Connect to physically establish a connection to the data source, the run-time engine manages all the connections for the package while it runs.

The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time. You can add more connection managers to the collection by using the Add method of the collection, and supplying a string that indicates the connection manager type. The Add method returns the ConnectionManager instance that was added to the package.

The ConnectionManager class exposes a set of properties that are common to all connections. However, sometimes you need access to properties that are unique to the specific connection type. The Properties collection of the ConnectionManager class provides access to these properties. The properties can be retrieved from the collection using the indexer or the property name and the GetValue method, and the values are set using the SetValue method. The properties of the underlying connection object properties can also be set by acquiring an actual instance of the object and setting its properties directly. To get the underlying connection, use the InnerObject property of the connection manager. The following line of code shows a C# line that creates an ADO.NET connection manager that has the underlying class, ConnectionManagerAdoNetClass.

This casts the managed connection manager object to its underlying connection object. If you are using C++, the QueryInterface method of the ConnectionManager object is called and the interface of the underlying connection object is requested.

The following table lists the connection managers included with Integration Services. and the string that is used in the package.Connections.Add("xxx") statement. For a list of all connection managers, see Integration Services Connections.

String

Connection manager

"OLEDB"

Connection manager for OLE DB connections.

"ODBC"

Connection manager for ODBC connections.

"ADO"

Connection manager for ADO connections.

"ADO.NET:SQL"

Connection manager for ADO.NET (SQL data provider) connections.

"ADO.NET:OLEDB"

Connection manager for ADO.NET (OLE DB data provider) connections.

"FLATFILE"

Connection manager for flat file connections.

"FILE"

Connection manager for file connections.

"MULTIFLATFILE"

Connection manager for multiple flat file connections.

"MULTIFILE"

Connection manager for multiple file connections.

"SQLMOBILE"

Connection manager for SQL Server Compact connections.

"MSOLAP100"

Connection manager for Analysis Services connections.

"FTP"

Connection manager for FTP connections.

"HTTP"

Connection manager for HTTP connections.

"MSMQ"

Connection manager for Message Queuing (also known as MSMQ) connections.