Coding a Custom Connection Manager

After you have created a class that inherits from the ConnectionManagerBase base class, and applied the DtsConnectionAttribute attribute to the class, you must override the implementation of the properties and methods of the base class to provide your custom functionality.

For samples of custom connection managers, see Developing a User Interface for a Custom Connection Manager. The code examples shown in this topic are drawn from the SQL Server Custom Connection Manager sample.

Configuring the Connection Manager

Setting the ConnectionString Property

The ConnectionString property is an important property and the only property unique to a custom connection manager. The connection manager uses the value of this property to connect to the external data source. If you are combining several other properties, such as server name and database name, to create the connection string, you can use a helper function to assemble the string by replacing certain values in a connection string template with the new value supplied by the user. The following code example shows an implementation of the ConnectionString property that relies on a helper function to assemble the string.

  ' Default values.
  Private _serverName As String = "(local)"
  Private _databaseName As String = "AdventureWorks"
  Private _connectionString As String = String.Empty

  Private Const CONNECTIONSTRING_TEMPLATE As String = _
    "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=SSPI"

  Public Property ServerName() As String
    Get
      Return _serverName
    End Get
    Set(ByVal value As String)
      _serverName = value
    End Set
  End Property

  Public Property DatabaseName() As String
    Get
      Return _databaseName
    End Get
    Set(ByVal value As String)
      _databaseName = value
    End Set
  End Property

  Public Overrides Property ConnectionString() As String
    Get
      UpdateConnectionString()
      Return _connectionString
    End Get
    Set(ByVal value As String)
      _connectionString = value
    End Set
  End Property

  Private Sub UpdateConnectionString()

    Dim temporaryString As String = CONNECTIONSTRING_TEMPLATE

    If Not String.IsNullOrEmpty(_serverName) Then
      temporaryString = temporaryString.Replace("<servername>", _serverName)
    End If
    If Not String.IsNullOrEmpty(_databaseName) Then
      temporaryString = temporaryString.Replace("<databasename>", _databaseName)
    End If

    _connectionString = temporaryString

  End Sub

    // Default values.
    private string _serverName = "(local)";
    private string _databaseName = "AdventureWorks";
    private string _connectionString = String.Empty;

    private const string CONNECTIONSTRING_TEMPLATE = "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=SSPI";

    public string ServerName
    {
      get
      {
        return _serverName;
      }
      set
      {
        _serverName = value;
      }
    }

    public string DatabaseName
    {
      get
      {
        return _databaseName;
      }
      set
      {
        _databaseName = value;
      }
    }

    public override string ConnectionString
    {
      get
      {
        UpdateConnectionString();
        return _connectionString;
      }
      set
      {
        _connectionString = value;
      }
    }

    private void UpdateConnectionString()
    {

      string temporaryString = CONNECTIONSTRING_TEMPLATE;

      if (!String.IsNullOrEmpty(_serverName))
      {
        temporaryString = temporaryString.Replace("<servername>", _serverName);
      }

      if (!String.IsNullOrEmpty(_databaseName))
      {
        temporaryString = temporaryString.Replace("<databasename>", _databaseName);
      }

      _connectionString = temporaryString;

    }

Validating the Connection Manager

You override the Validate method to make sure that the connection manager has been configured correctly. At a minimum, you should validate the format of the connection string and make sure that values have been provided for all arguments. Execution cannot continue until the connection manager returns Success from the Validate method.

The following code example shows an implementation of Validate that makes sure that the user has specified a server name for the connection.

  Public Overrides Function Validate(ByVal infoEvents As Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents) As Microsoft.SqlServer.Dts.Runtime.DTSExecResult

    If String.IsNullOrEmpty(_serverName) Then
      infoEvents.FireError(0, "SqlConnectionManager", "No server name specified", String.Empty, 0)
      Return DTSExecResult.Failure
    Else
      Return DTSExecResult.Success
    End If

  End Function

    public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
    {

      if (String.IsNullOrEmpty(_serverName))
      {
        infoEvents.FireError(0, "SqlConnectionManager", "No server name specified", String.Empty, 0);
        return DTSExecResult.Failure;
      }
      else
      {
        return DTSExecResult.Success;
      }

    }

Persisting the Connection Manager

Usually, you do not have to implement custom persistence for a connection manager. Custom persistence is required only when the properties of an object use complex data types. For more information, see Developing Custom Objects for Integration Services.

Working with the External Data Source

The methods that support connecting to an external data source are the most important methods of a custom connection manager. The AcquireConnection and ReleaseConnection methods are called at various times during both design time and run time.

Acquiring the Connection

You need to decide what type of object it is appropriate for the AcquireConnection method to return from your custom connection manager. For example, a File connection manager returns only a string that contains a path and filename, whereas an ADO.NET connection manager returns a managed connection object that is already open. An OLE DB connection manager returns a native OLE DB connection object that cannot be used from managed code. The custom SQL Server connection manager, from which the code snippets in this topic are taken, returns an open SqlConnection object.

Users of your connection manager need to know in advance what type of object to expect, so that they can cast the returned object to the appropriate type and access its methods and properties.

  Public Overrides Function AcquireConnection(ByVal txn As Object) As Object

    Dim sqlConnection As New SqlConnection

    UpdateConnectionString()

    With sqlConnection
      .ConnectionString = _connectionString
      .Open()
    End With

    Return sqlConnection

  End Function

    public override object AcquireConnection(object txn)
    {

      SqlConnection sqlConnection = new SqlConnection();

      UpdateConnectionString();

      {
        sqlConnection.ConnectionString = _connectionString;
        sqlConnection.Open();
      }

      return sqlConnection;

    }

Releasing the Connection

The action that you take in the ReleaseConnection method depends on the type of object that you returned from the AcquireConnection method. If there is an open connection object, you should close it and to release any resources that it is using. If AcquireConnection returned only a string value, no action needs to be taken.

  Public Overrides Sub ReleaseConnection(ByVal connection As Object)

    Dim sqlConnection As SqlConnection

    sqlConnection = DirectCast(connection, SqlConnection)

    If sqlConnection.State <> ConnectionState.Closed Then
      sqlConnection.Close()
    End If

  End Sub

    public override void ReleaseConnection(object connection)
    {
      SqlConnection sqlConnection;
      sqlConnection = (SqlConnection)connection;
      if (sqlConnection.State != ConnectionState.Closed)
        sqlConnection.Close();
    }

See Also

Tasks

Creating a Custom Connection Manager

Developing a User Interface for a Custom Connection Manager