Partager via


Add, Delete, or Share a Connection Manager in a Package

 

Applies To: SQL Server 2016

Integration Services includes a variety of connection managers for connecting to different data sources, such as relational databases, Analysis Services databases, and files in CSV and XML formats. 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, see Deployment of Projects and Packages. For more information about converting a project to the project deployment model, see Deploy Projects to Integration Services Server.

The following procedures apply to all types of connection managers and describe how to do the following tasks:

  • To add a connection manager when creating a package

  • To add a connection manager to an existing package

  • To add a connection manager at the project level

  • To create a parameter for a connection manager property

  • To delete a connection manager from a package

  • To delete a shared connection manager (project level connection manager)

To add a connection manager when creating 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.

To add a connection manager to an existing package

  1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.

  2. In Solution Explorer, double-click the package to open it

  3. In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.

  4. 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.

  5. Optionally, right-click the connection manager, click Rename, and then modify the default name of the connection manager.

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

To add a connection manager at the project level

  1. In SQL Server Data Tools (SSDT), open the Integration Services project.

  2. In Solution Explorer, right-click Connection Managers, and click New Connection Manager.

  3. 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.

  4. 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.

To create a parameter for a connection manager property

  1. In the Connection Managers area, right-click the connection manager that you want to create a parameter for and then click Parameterize.

  2. Configure the parameter settings in the Parameterize dialog box. For more information, see Parameterize Dialog Box.

To delete a connection manager from a package

  1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.

  2. In Solution Explorer, double-click the package to open it.

  3. In SSIS Designer, click the Control Flow tab, the Data Flow tab, or the Event Handler tab to make the Connection Managers area available.

  4. 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.

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

To delete a shared connection manager (project level connection manager)

  1. 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?

  2. 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.

See Also

Integration Services (SSIS) Connections
Set the Properties of a Connection Manager