Adding Connections Programmatically

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 in a consistent and predictable manner. Connection managers contain a set of 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 physically establish a connection to the data source, 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 by 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 that are common to all connections. However, sometimes you need access to properties that are 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 that has the underlying class, ConnectionManagerAdoNetClass.

ConnectionManagerAdoNetClass cmado = cm.InnerObject as ConnectionManagerAdoNet;

This casts the managed connection manager object to its underlying connection object. If you are using 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 Connection Managers.

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: OLE DB connection to the AdventureWorks database.

Number of connections in package: 2

Change History

See Also

Other Resources

Integration Services Connections
Creating Connection Managers

Help and Information

Getting SQL Server 2005 Assistance