Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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.

Note Note

Most of the tasks, sources, and destinations that have been built into Integration Services work only with specific types of built-in connection managers. Therefore, these samples cannot be tested with the built-in tasks and components.

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

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 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 override void ReleaseConnection(object connection)
    {
      SqlConnection sqlConnection;
      sqlConnection = (SqlConnection)connection;
      if (sqlConnection.State != ConnectionState.Closed)
        sqlConnection.Close();
    }
Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.