Connection Manager Concepts

Connection managers are a new object type in Integration Services. They are fundamentally different from Data Transformation Services Connection objects, yet very similar in function. There are also a lot more connection managers because of a fundamental shift in the way connections are used in Integration Services. This chapter explains the concepts behind connection managers and then describes each of the stock connection managers.

Connection Managers

Integration Services has an object called a connection manager. The name should give you a hint about what it does. It manages connections, or, more accurately, it creates connections to resources based on its property settings and delivers the connection to the object that asks for it by calling the AcquireConnection method. So, connection managers are essentially connection factories. OLEDB connection managers create OLEDB connections, WMI connection managers create WMI connections, and so forth. To generate or "build" a new connection, components call AcquireConnection on the connection manager.

Note

DTS users will note that this is a departure from DTS connection design. DTS connections were not connection factories. They were more like connection wrappers and were only capable of generating and holding one physical connection.

In Integration Services, if you want to access resources, you create a connection manager. The reasons for this design are not always apparent at first, and some benefits won't be fully realized until later versions of Integration Services take full advantage of the design, but the following are some of the immediate benefits:

• It's much easier to identify what resources the package references if they are all centralized in the Connections window at the bottom of the Control Flow Designer than if the connection strings and filenames are spread throughout the package as values for component properties.

• It's easier to manage connection managers than hard-coded strings.

• It is easier to configure connection managers than properties on tasks and data flow components.

• Connection managers can be copied and pasted.

• Connection managers can be extended with custom types.

• Connection managers can be used by more than one component.

• Connection managers make it possible to do impact analysis.

• Connection managers relieve tasks from the burden of validating connection strings.

Microsoft Practicing What It Preaches?

Astute readers and users of Integration Services will note that there are some cases in which stock components don't adhere to the standard of using connection managers for accessing resources. For example, the raw source adapter only supports directly typing in the source filename or retrieving it from a variable. The reasons for this are varied and have to do with trying to pull together an overall philosophy for a product while building the product simultaneously.

These exceptions should be corrected in future releases.

Categories of Connection Managers

In the client/server, two-tier world, people typically think of a connection as a link or contract between the client and the server over a network that can be used to access resources on the server. Integration Services supports this type of connection but extends the idea to include access to resources other than servers.

Depending on the resources to which the connection manager provides access, the type of object or value returned from the AcquireConnection call is different. For example, the OLEDB Connection Manager returns a pointer to an instance of an OLEDB Session object, the File Connection Manager returns a simple string, and the WMI Connection Manager returns an instance of a .NET object called System.Management.ManagementScope. The current collection of stock connection managers can be classified into three categories, as follows:

• Database connections that use the various data access layers and often support distributed transactions, such as ODBC, OLEDB, MSMQ, and ADO.NET

• Network connections to resources found on the Internet, such as FTP, HTTP, and web services

• File-system resources, such as flat files, text files, and folders

Note

If you're familiar with Data Transformation Services, you'll recognize this is a fundamental shift in philosophy. DTS packages had only the first type of connection.

Database Connection Managers (ADO.NET, OLEDB, ODBC, and Others)

At the low level, database connections are the starting point for accessing resources. Typically, pointers to objects or opaque numbers function as a handle, and the client code uses the pointer or handle to access even more information and perform more functions. For this class of connection managers, every call to AcquireConnection creates a new physical connection. This makes it possible for multiple tasks or other clients to use one connection manager and still have different connections to a database.

Tip

This category of connection managers has a property in common called RetainSameConnection. RetainSameConnection tells the connection manager to keep the connection it creates the first time a client calls its AcquireConnection method until the package completes. It gives out that same physical connection every time a client component calls AcquireConnection.

This is useful if you need to maintain a SQL transaction across multiple clients, you want to simulate DTS behavior, or you want to retain a temporary table for use by multiple clients.

Network Connection Managers (FTP, HTTP, WMI, and Others)

This category of connection managers each returns something different. Most of these return an object that the calling task or component must know how to use. For example, the FTP Connection Manager returns a special FTP object with properties such as ServerName, ServerPassword, Retries, PassiveMode, and other related FTP settings.

File System Connection Managers (File, MultiFlatFile, and Others)

File system connection managers are a bit odd. They don't return a pointer or handle. They return a string that points to a given file on the file system. The MultiFile and MultiFlatFile Connection Managers support wildcards and lists of files so that more than one filename can be returned by calling AcquireConnection.