Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

Create Connection Managers

Integration Services includes a variety of connection managers to suit the needs of tasks that connect to different types of servers and data sources. Connection managers are used by the data flow components that extract and load data in different types of data stores, and by the log providers that write logs to a server, SQL Server table, or file. For example, a package with a Send Mail task uses an SMTP connection manager type to connect to a Simple Mail Transfer Protocol (SMTP) server. A package with an Execute SQL task can use an OLE DB connection manager to connect to a SQL Server database. For more information, see Integration Services (SSIS) Connections.

To automatically create and configure connection managers when you create a new package, you can use the SQL Server Import and Export Wizard. The wizard also helps you create and configure the sources and destinations that use the connection managers. For more information, see Create Packages in SQL Server Data Tools.

To manually create a new connection manager and add it to an existing package, you use the Connection Managers area that appears on the Control Flow, Data Flow, and Event Handlers tabs of SSIS Designer. From the Connection Manager area, you choose the type of connection manager to create, and then set the properties of the connection manager by using a dialog box that SSIS Designer provides. For more information, see the section, "Using the Connection Managers Area," later in this topic.

After the connection manager is added to a package, you can use it in tasks, Foreach Loop containers, sources, transformations, and destinations. For more information, see Integration Services Tasks, Foreach Loop Container, and Data Flow.

You can create connection managers while the Control Flow, Data Flow, or Event Handlers tab of SSIS Designer is active.

The following diagram shows the Connection Managers area on the Control Flow tab of SSIS Designer.

Screenshot of control flow designer with package

To add, configure, or delete a connection manager in SSIS Designer

Many of the providers that connection managers use are available in 32-bit and 64-bit versions. The Integration Services design environment is a 32-bit environment and you see only 32-bit providers while you are designing a package. Therefore, you can only configure a connection manager to use a specific 64-bit provider if the 32-bit version of the same provider is also installed.

At run time, the correct version is used, and it does not matter that you specified the 32-bit version of the provider at design time. The 64-bit version of the provider can be run even if the package is run in SQL Server Data Tools (SSDT).

Both versions of the provider have the same ID. To specify whether the Integration Services runtime uses an available 64-bit version of the provider, you set the Run64BitRuntime property of the Integration Services project. If the Run64BitRuntime property is set to true, the runtime finds and uses the 64-bit provider; if Run64BitRuntime is false, the runtime finds and uses the 32-bit provider. For more information about properties you can set on Integration Services projects, see Integration Services (SSIS) and Studio Environments.

Integration Services icon (small)  Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.