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 the Integration Services samples on Codeplex. The code examples shown in this topic are drawn from the SQL Server Custom Connection Manager sample.
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.
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 = "AdventureWorks2008R2"
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 = "AdventureWorks2008R2";
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();
}
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services