Shared Connection

The database connection you define in a dataflow object can be made visible, or ‘shared’ to other objects on the dataflow. This allows you to use the shared connection in any number of objects within the same dataflow as long as the object provides the capability to use a shared connection. Examples of such objects are destination objects, SQL Query Source object, SQL Statement Destination object, SQL Statement Map etc.

Usage

Use Shared Connection object if you are updating a table from multiple destination objects or if you are updating multiple tables and you want the entire dataflow to run in a single database transaction.

Sample

Steps

The name of a shared connection is the same as the name of the Shared Connection object, for example SharedConnection1; as a result there can only be one shared connection per each shared connection object.

To share a connection, add a Shared Connection object to the dataflow. To that end, open the Flow toolbox, expand the Resources group, and drag-and-drop the Shared Connection object on the dataflow.

An example of what a Shared Connection object might look like is shown below.

As with any object on the dataflow, double click your Shared Connection object to open its properties.

Using the Database Connection screen, enter the connection details, such as server name, credentials, database name etc.

You can also specify shared connection options for transaction management. These options are available in the Shared Connection Options screen.

To use your shared connection, open the properties of an object whose connection you wish to populate with your shared connection. Simply select the Use Shared Connection option on the Database Connection screen, and select a shared connection from the dropdown.