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 ManagersIntegration 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:
Categories of Connection ManagersIn 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:
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. |