Creating 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 Connections.
To automatically create and configure connection managers when you create a new package, you can use either the SQL Server Import and Export Wizard or Integration Services Connections Project Wizard. These wizards also help you create and configure the sources and destinations that use the connection managers. For more information, see Creating a Package by Running a Wizard.
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 Elements.
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.
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 Business Intelligence Development Studio.
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 64-bit Considerations for Integration Services and Integration Services in Business Intelligence Development Studio.