How To Create Connection Manager In Ssis

Integration Services (SSIS) Connections

06/24/2021
17 minutes to read

Applies to: SQL Server (all supported versions) SSIS Integration Runtime in Azure Data Factory

Microsoft SQL Server Integration Services packages use connections to perform different tasks and to implement Integration Services features:

Connecting to source and destination data stores such as text, XML, Excel workbooks, and relational databases to extract and load data.
Connecting to relational databases that contain reference data to perform exact or fuzzy lookups.
Connecting to relational databases to run SQL statements such as SELECT, DELETE, and INSERT commands and also stored procedures.
Connecting to SQL Server to perform maintenance and transfer tasks such as backing up databases and transferring logins.
Writing log entries in text and XML files and SQL Server tables and package configurations to SQL Server tables.
Connecting to SQL Server to create temporary work tables that some transformations require to do their work.
Connecting to Analysis Services projects and databases to access data mining models, process cubes and dimensions, and run DDL code.
Specifying existing or creating new files and folders to use with Foreach Loop enumerators and tasks.
Connecting to message queues and to Windows Management Instrumentation (WMI), SQL Server Management Objects (SMO), Web, and mail servers.

To make these connections, Integration Services uses connection managers, as described in the next section.

Connection Managers

Integration Services uses the connection manager as a logical representation of a connection. At design time, you set the properties of a connection manager to describe the physical connection that Integration Services creates when the package runs. For example, a connection manager includes the ConnectionString property that you set at design time; at run time, a physical connection is created using the value in the connection string property.

A package can use multiple instances of a connection manager type, and you can set the properties on each instance. At run time, each instance of a connection manager type creates a connection that has different attributes.

SQL Server Integration Services provides different types of connection managers that enable packages to connect to a variety of data sources and servers:

There are built-in connection managers that Setup installs when you install Integration Services.
There are connection managers that are available for download from the Microsoft website.
You can create your own custom connection manager if the existing connection managers do not meet your needs.

Package level and project level connection managers

A connection manager can be created at the package level or at the project level. The connection manager created at the project level is available all the packages in the project. Whereas, connection manager created at the package level is available to that specific package.

You use connection managers that are created at the project level in place of data sources, to share connections to sources. To add a connection manager at the project level, the Integration Services project must use the project deployment model. When a project is configured to use this model, the Connection Managers folder appears in Solution Explorer, and the Data Sources folder is removed from Solution Explorer.

Note

If you want to use data sources in your package, you need to convert the project to the package deployment model.

For more information about the two models, and about converting a project to the project deployment model, see Deploy Integration Services (SSIS) Projects and Packages.

Built-in Connection Managers

The following table lists the connection manager types that SQL Server Integration Services provides.

Type	Description	Topic
ADO	Connects to ActiveX Data Objects (ADO) objects.	ADO Connection Manager
ADO.NET	Connects to a data source by using a .NET provider.	ADO.NET Connection Manager
CACHE	Reads data from the data flow or from a cache file (.caw), and can save data to the cache file.	Cache Connection Manager
DQS	Connects to a Data Quality Services server and a Data Quality Services database on the server.	DQS Cleansing Connection Manager
EXCEL	Connects to an Excel workbook file.	Excel Connection Manager
FILE	Connects to a file or a folder.	File Connection Manager
FLATFILE	Connect to data in a single flat file.	Flat File Connection Manager
FTP	Connect to an FTP server.	FTP Connection Manager
HTTP	Connects to a webserver.	HTTP Connection Manager
MSMQ	Connects to a message queue.	MSMQ Connection Manager
MSOLAP100	Connects to an instance of SQL Server Analysis Services or an Analysis Services project.	Analysis Services Connection Manager
MULTIFILE	Connects to multiple files and folders.	Multiple Files Connection Manager
MULTIFLATFILE	Connects to multiple data files and folders.	Multiple Flat Files Connection Manager
OLEDB	Connects to a data source by using an OLE DB provider.	OLE DB Connection Manager
ODBC	Connects to a data source by using ODBC.	ODBC Connection Manager
SMOServer	Connects to a SQL Server Management Objects (SMO) server.	SMO Connection Manager
SMTP	Connects to an SMTP mail server.	SMTP Connection Manager
SQLMOBILE	Connects to a SQL Server Compact database.	SQL Server Compact Edition Connection Manager
WMI	Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.	WMI Connection Manager

Connection Managers available for download

The following table lists additional types of connection manager that you can download from the Microsoft website.

Important

The connection managers listed in the following table work only with SQL Server Enterprise and SQL Server Developer edition.

Type	Description	Topic
ORACLE	Connects to an Oracle <version info> server.	The Oracle connection manager is the connection manager component of the Microsoft Connector for Oracle by Attunity. The Microsoft Connector for Oracle by Attunity also includes a source and a destination. For more information, see the download page, Microsoft Connectors for Oracle and Teradata by Attunity.
SAPBI	Connects to an SAP NetWeaver BI version 7 system.	The SAP BI connection manager is the connection manager component of the Microsoft Connector for SAP BI. The Microsoft Connector for SAP BI also includes a source and a destination. For more information, see the download page, Microsoft SQL Server 2008 Feature Pack.
TERADATA	Connects to a Teradata <version info> server.	The Teradata connection manager is the connection manager component of the Microsoft Connector for Teradata by Attunity. The Microsoft Connector for Teradata by Attunity also includes a source and a destination. For more information, see the download page, Microsoft Connectors for Oracle and Teradata by Attunity.

Custom Connection Managers

You can also write custom connection managers. For more information, see Developing a Custom Connection Manager.

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.

Using the Connection Managers Area

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

32-Bit and 64-Bit Providers for Connection Managers

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.

Add a connection manager

Add a connection manager when you create a package

Use the SQL Server Import and Export Wizard

In addition to creating and configuring a connection manager, the wizard also helps you create and configure the sources and destinations that use the connection manager. For more information, see Create Packages in SQL Server Data Tools.

Add a connection manager to an existing package

In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it
In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.

Right-click anywhere in the Connection Managers area, and then do one of the following:

Click the connection manager type to add to the package.

-or-
If the type that you want to add is not listed, click New Connection to open the Add SSIS Connection Manager dialog box, select a connection manager type, and then click OK.

The custom dialog box for the selected connection manager type opens. For more information about connection manager types and the options that are available, see the following options table.

Connection manager	Options
ADO Connection Manager	Configure OLE DB Connection Manager
ADO.NET Connection Manager	Configure ADO.NET Connection Manager
Analysis Services Connection Manager	Add Analysis Services Connection Manager Dialog Box UI Reference
Excel Connection Manager	Excel Connection Manager Editor
File Connection Manager	File Connection Manager Editor
Multiple Files Connection Manager	Add File Connection Manager Dialog Box UI Reference
Flat File Connection Manager	Flat File Connection Manager Editor (General Page) Flat File Connection Manager Editor (Columns Page) Flat File Connection Manager Editor (Advanced Page) Flat File Connection Manager Editor (Preview Page)
Multiple Flat Files Connection Manager	Multiple Flat Files Connection Manager Editor (General Page) Multiple Flat Files Connection Manager Editor (Columns Page) Multiple Flat Files Connection Manager Editor (Advanced Page) Multiple Flat Files Connection Manager Editor (Preview Page)
FTP Connection Manager	FTP Connection Manager Editor
HTTP Connection Manager	HTTP Connection Manager Editor (Server Page) HTTP Connection Manager Editor (Proxy Page)
MSMQ Connection Manager	MSMQ Connection Manager Editor
ODBC Connection Manager	ODBC Connection Manager UI Reference
OLE DB Connection Manager	Configure OLE DB Connection Manager
SMO Connection Manager	SMO Connection Manager Editor
SMTP Connection Manager	SMTP Connection Manager Editor
SQL Server Compact Edition Connection Manager	SQL Server Compact Edition Connection Manager Editor (Connection Page) SQL Server Compact Edition Connection Manager Editor (All Page)
WMI Connection Manager	WMI Connection Manager Editor

The Connection Managers area lists the added connection manager.

Optionally, right-click the connection manager, click Rename, and then modify the default name of the connection manager.
To save the updated package, click Save Selected Item on the File menu.

Add a connection manager at the project level

In SQL Server Data Tools (SSDT), open the Integration Services project.
In Solution Explorer, right-click Connection Managers, and click New Connection Manager.

In the Add SSIS Connection Manager dialog box, select the type of connection manager, and then click Add.

The custom dialog box for the selected connection manager type opens. For more information about connection manager types and the options that are available, see the following options table.

Connection manager	Options
ADO Connection Manager	Configure OLE DB Connection Manager
ADO.NET Connection Manager	Configure ADO.NET Connection Manager
Analysis Services Connection Manager	Add Analysis Services Connection Manager Dialog Box UI Reference
Excel Connection Manager	Excel Connection Manager Editor
File Connection Manager	File Connection Manager Editor
Multiple Files Connection Manager	Add File Connection Manager Dialog Box UI Reference
Flat File Connection Manager	Flat File Connection Manager Editor (General Page) Flat File Connection Manager Editor (Columns Page) Flat File Connection Manager Editor (Advanced Page) Flat File Connection Manager Editor (Preview Page)
Multiple Flat Files Connection Manager	Multiple Flat Files Connection Manager Editor (General Page) Multiple Flat Files Connection Manager Editor (Columns Page) Multiple Flat Files Connection Manager Editor (Advanced Page) Multiple Flat Files Connection Manager Editor (Preview Page)
FTP Connection Manager	FTP Connection Manager Editor
HTTP Connection Manager	HTTP Connection Manager Editor (Server Page) HTTP Connection Manager Editor (Proxy Page)
MSMQ Connection Manager	MSMQ Connection Manager Editor
ODBC Connection Manager	ODBC Connection Manager UI Reference
OLE DB Connection Manager	Configure OLE DB Connection Manager
SMO Connection Manager	SMO Connection Manager Editor
SMTP Connection Manager	SMTP Connection Manager Editor
SQL Server Compact Edition Connection Manager	SQL Server Compact Edition Connection Manager Editor (Connection Page) SQL Server Compact Edition Connection Manager Editor (All Page)
WMI Connection Manager	WMI Connection Manager Editor

The connection manager you added will show up under the Connections Managers node in the Solution Explorer. It will also appear in the Connection Managers tab in the SSIS Designer window for all the packages in the project. The name of the connection manager in this tab will have a (project) prefix in order to differentiate this project level connection manager from the package level connection managers.

Optionally, right-click the connection manager in the Solution Explorer window under Connection Managers node (or) in the Connection Managers tab of the SSIS Designer window, click Rename, and then modify the default name of the connection manager.

Note

In the Connection Managers tab of the SSIS Designer window, you won't be able to overwrite the (project) prefix from the connection manager name. This is by design.

Add SSIS Connection Manager dialog box

Use the Add SSIS Connection Manager dialog box to select the type of connection to add to a package.

To learn more about connection managers, see Integration Services (SSIS) Connections.

Options

Connection manager type
Select a connection type and then click Add, or double-click a connection type, to specify connection properties using the editor for each type of connection.

Add
Specify connection properties using the editor for each type of connection.

Create a parameter for a connection manager property

In the Connection Managers area, right-click the connection manager that you want to create a parameter for and then click Parameterize.
Configure the parameter settings in the Parameterize dialog box. For more information, see Parameterize Dialog Box.

Note

Property ConnectionString is not sensitive and designed not to contain sensitive password information. it is recommended to use property Password to parameterize sensitive password.

Delete a connection manager

Delete a connection manager from a package

In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.
Right-click the connection manager that you want to delete, and then click Delete.

If you delete a connection manager that a package element, such as an Execute SQL task or an OLE DB source, uses, you will experience the following results:
- An error icon appears on the package element that used the deleted connection manager.
- The package fails to validate.
- The package cannot be run.
To save the updated package, click Save Selected Items on the File menu.

Delete a shared connection manager (project level connection manager)

To delete a project-level connection manager, right-click the connection manager under Connection Managers node in the Solution Explorer window, and then click Delete. SQL Server Data Tools displays the following warning message:

Warning

When you delete a project connection manager, packages that use the connection manager might not run. You cannot undo this action. Do you want to delete the connection manager?
Click OK to delete the connection manager or Cancel to keep it.

Note

You can also delete a project level connection manager from the Connection Manager tab of the SSIS Designer window opened for any package in the project. You do so by right-clicking the connection manager in the tab and then by clicking Delete.

Set the Properties of a Connection Manager

All connection managers can be configured using the Properties window.

Integration Services also provides custom dialog boxes for modifying the different types of connection managers in Integration Services. The dialog box has a different set of options depending on the connection manager type.

Modify a connection manager using the Properties window

In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.
Right-click the connection manager and click Properties.
In the Properties window, edit the property values. The Properties window provides access to some properties that are not configurable in the standard editor for a connection manager.
Click OK.
To save the updated package, click Save Selected Items on the File menu.

Modify a connection manager using a connection manager dialog box

In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.

In the Connection Managers area, double-click the connection manager to open the Connection Manager dialog box. For information about specific connection manager types, and the options available for each type, see the following table.

Connection manager	Options
ADO Connection Manager	Configure OLE DB Connection Manager
ADO.NET Connection Manager	Configure ADO.NET Connection Manager
Analysis Services Connection Manager	Add Analysis Services Connection Manager Dialog Box UI Reference
Excel Connection Manager	Excel Connection Manager Editor
File Connection Manager	File Connection Manager Editor
Multiple Files Connection Manager	Add File Connection Manager Dialog Box UI Reference
Flat File Connection Manager	Flat File Connection Manager Editor (General Page) Flat File Connection Manager Editor (Columns Page) Flat File Connection Manager Editor (Advanced Page) Flat File Connection Manager Editor (Preview Page)
Multiple Flat Files Connection Manager	Multiple Flat Files Connection Manager Editor (General Page) Multiple Flat Files Connection Manager Editor (Columns Page) Multiple Flat Files Connection Manager Editor (Advanced Page) Multiple Flat Files Connection Manager Editor (Preview Page)
FTP Connection Manager	FTP Connection Manager Editor
HTTP Connection Manager	HTTP Connection Manager Editor (Server Page) HTTP Connection Manager Editor (Proxy Page)
MSMQ Connection Manager	MSMQ Connection Manager Editor
ODBC Connection Manager	ODBC Connection Manager UI Reference
OLE DB Connection Manager	Configure OLE DB Connection Manager
SMO Connection Manager	SMO Connection Manager Editor
SMTP Connection Manager	SMTP Connection Manager Editor
SQL Server Compact Edition Connection Manager	SQL Server Compact Edition Connection Manager Editor (Connection Page) SQL Server Compact Edition Connection Manager Editor (All Page)
WMI Connection Manager	WMI Connection Manager Editor

To save the updated package, click Save Selected Items on the File menu.

Video, Leverage Microsoft Attunity Connector for Oracle to enhance Package Performance, on technet.microsoft.com
Wiki articles, SSIS Connectivity, on social.technet.microsoft.com
Blog entry, Connecting to MySQL from SSIS, on blogs.msdn.com.
Technical article, Extracting and Loading SharePoint Data in SQL Server Integration Services, on msdn.microsoft.com.
Technical article, You get "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER" error message when using Oracle connection manager in SSIS, on support.microsoft.com.