Add Connections Programmatically

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

The ConnectionManager class represents physical connections to external data sources. The ConnectionManager class isolates the implementation details of the connection from the runtime. This enables the runtime to interact with each connection manager consistently and predictably. Connection managers contain stock properties that all connections have in common, such as the Name, ID, Description, and ConnectionString. However, the ConnectionString and Name properties are ordinarily the only properties required to configure a connection manager. Unlike other programming paradigms, where connection classes expose methods such as Open or Connect to establish a connection to the data source physically, the run-time engine manages all the connections for the package while it runs.

The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time. You can add more connection managers to the collection using the Add method of the collection, and supplying a string that indicates the connection manager type. The Add method returns the ConnectionManager instance that was added to the package.

Intrinsic Properties

The ConnectionManager class exposes a set of properties common to all connections. However, sometimes you need access to properties unique to the specific connection type. The Properties collection of the ConnectionManager class provides access to these properties. The properties can be retrieved from the collection using the indexer or the property name and the GetValue method, and the values are set using the SetValue method. The properties of the underlying connection object properties can also be set by acquiring an actual instance of the object and setting its properties directly. To get the underlying connection, use the InnerObject property of the connection manager. The following line of code shows a C# line that creates an ADO.NET connection manager with the underlying class, ConnectionManagerAdoNetClass.

ConnectionManagerAdoNetClass cmado = cm.InnerObject as ConnectionManagerAdoNet;

This casts the managed connection manager object to its underlying connection object. If you use C++, the QueryInterface method of the ConnectionManager object is called and the interface of the underlying connection object is requested.

The following table lists the connection managers included with Integration Services, and the string that is used in the package.Connections.Add("xxx") statement. For a list of all connection managers, see Integration Services (SSIS) Connections.

The following code example demonstrates adding an OLE DB and FILE connection to the Connections collection of a Package. The example then sets the ConnectionString, Name, and Description properties.

using System;
using Microsoft.SqlServer.Dts.Runtime;
  
namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      // Create a package, and retrieve its connections.
      Package pkg = new Package();
      Connections pkgConns = pkg.Connections;
  
      // Add an OLE DB connection to the package, using the
      // method defined in the AddConnection class.
      CreateConnection myOLEDBConn = new CreateConnection();
      myOLEDBConn.CreateOLEDBConnection(pkg);
  
      // View the new connection in the package.
      Console.WriteLine("Connection description: {0}",
         pkg.Connections["SSIS Connection Manager for OLE DB"].Description);
  
      // Add a second connection to the package.
      CreateConnection myFileConn = new CreateConnection();
      myFileConn.CreateFileConnection(pkg);
  
      // View the second connection in the package.
      Console.WriteLine("Connection description: {0}",
        pkg.Connections["SSIS Connection Manager for Files"].Description);
  
      Console.WriteLine();
      Console.WriteLine("Number of connections in package: {0}", pkg.Connections.Count);
  
      Console.Read();
    }
  }
  // <summary>
  // This class contains the definitions for multiple
  // connection managers.
  // </summary>
  public class CreateConnection
  {
    // Private data.
    private ConnectionManager ConMgr;
  
    // Class definition for OLE DB Provider.
    public void CreateOLEDBConnection(Package p)
    {
      ConMgr = p.Connections.Add("OLEDB");
      ConMgr.ConnectionString = "Provider=SQLOLEDB.1;" +
        "Integrated Security=SSPI;Initial Catalog=AdventureWorks;" +
        "Data Source=(local);";
      ConMgr.Name = "SSIS Connection Manager for OLE DB";
      ConMgr.Description = "OLE DB connection to the AdventureWorks database.";
    }
    public void CreateFileConnection(Package p)
    {
      ConMgr = p.Connections.Add("File");
      ConMgr.ConnectionString = @"\\<yourserver>\<yourfolder>\books.xml";
      ConMgr.Name = "SSIS Connection Manager for Files";
      ConMgr.Description = "Flat File connection";
    }
  }
  
}

Imports Microsoft.SqlServer.Dts.Runtime
  
Module Module1
  
  Sub Main()
  
    ' Create a package, and retrieve its connections.
    Dim pkg As New Package()
    Dim pkgConns As Connections = pkg.Connections
  
    ' Add an OLE DB connection to the package, using the
    ' method defined in the AddConnection class.
    Dim myOLEDBConn As New CreateConnection()
    myOLEDBConn.CreateOLEDBConnection(pkg)
  
    ' View the new connection in the package.
    Console.WriteLine("Connection description: {0}", _
      pkg.Connections("SSIS Connection Manager for OLE DB").Description)
  
    ' Add a second connection to the package.
    Dim myFileConn As New CreateConnection()
    myFileConn.CreateFileConnection(pkg)
  
    ' View the second connection in the package.
    Console.WriteLine("Connection description: {0}", _
      pkg.Connections("SSIS Connection Manager for Files").Description)
  
    Console.WriteLine()
    Console.WriteLine("Number of connections in package: {0}", pkg.Connections.Count)
  
    Console.Read()
  
  End Sub
  
End Module
  
' This class contains the definitions for multiple
' connection managers.
  
Public Class CreateConnection
  ' Private data.
  Private ConMgr As ConnectionManager
  
  ' Class definition for OLE DB provider.
  Public Sub CreateOLEDBConnection(ByVal p As Package)
    ConMgr = p.Connections.Add("OLEDB")
    ConMgr.ConnectionString = "Provider=SQLOLEDB.1;" & _
      "Integrated Security=SSPI;Initial Catalog=AdventureWorks;" & _
      "Data Source=(local);"
    ConMgr.Name = "SSIS Connection Manager for OLE DB"
    ConMgr.Description = "OLE DB connection to the AdventureWorks database."
  End Sub
  
  Public Sub CreateFileConnection(ByVal p As Package)
    ConMgr = p.Connections.Add("File")
    ConMgr.ConnectionString = "\\<yourserver>\<yourfolder>\books.xml"
    ConMgr.Name = "SSIS Connection Manager for Files"
    ConMgr.Description = "Flat File connection"
  End Sub
  
End Class

Sample Output:

Connection description: OLE DB connection to the AdventureWorks database.

Connection description: Flat File connection.

Number of connections in package: 2

See also

• Integration Services (SSIS) Connections
• Create Connection Managers