Custom Connection Managers
Integration Services
supports the notion of pluggable connection manager components. If you
build and register a connection manager component, it will appear in the
list of available connection types when you right-click in the
Connection Managers tray and choose New Connection.
To build a custom connection manager, create a Class Library project in Visual Studio. Add a reference to the Microsoft.SqlServer.ManagedDTS
assembly. In the project properties, go to the Signing tab and select
the Sign The Assembly check box. Use a key file you already have, or
create a new key file by clicking New.
Next, create a class derived from ConnectionManagerBase and add the DtsConnection
attribute to the class. Build the project, and add the compiled
assembly to the GAC. Also copy the assembly to %ProgramFiles%\Microsoft
SQL Server\90\DTS\Connections. Restart SQL Server Business Intelligence
Development Studio, and you should see your custom connection manager in
the list of available connection managers, as shown in Figure 6.
Listing 5 shows sample code for a custom connection manager class.
Listing 5. An Example of a Custom Connection Manager Class
using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace CustomConnectionManager
{
public class MyConnection
{
// TODO: Implement your connection class
}
[DtsConnection(
ConnectionType = "MYCONN",
DisplayName = "My Custom Connection",
Description = "Connection manager for my custom type of connections")]
public class CustomConnectionManager : ConnectionManagerBase
{
public override object AcquireConnection(object txn)
{
MyConnection myConnection = new MyConnection();
// TODO: Add code to open your type of connection
return (object) myConnection;
}
public override void ReleaseConnection(object connection)
{
MyConnection myConnection = (MyConnection)connection;
// TODO: Add code to close your type of connection
}
}
}
|
Log Providers
Custom log providers
are useful when you want to integrate Integration Services logging with a
system of logging you already have in place. The custom log provider
sees the stream of log entries just as the built-in log providers do.
What you do with these log entries is up to you.
To create a custom Log
Provider component, you follow the steps given earlier for creating a
custom connection manager except that your class should be derived from LogProviderBase and the attribute on that class should be DtsLogProvider. Copy the compiled assembly to %ProgramFiles%\Microsoft SQL Server\90\DTS\LogProviders.
Foreach Enumerator
You might want to create a
custom Foreach Enumerator component to extend the Foreach Loop
container so it can iterate over collections of items that the product
doesn’t know about. For example, if you read data using some kind of
proprietary method, you can use a custom Foreach Enumerator component to
execute some control flow in a loop over each data element.
To create a custom
Foreach Enumerator component, you follow the steps given earlier for
creating a custom connection manager except your class should be derived
from ForEachEnumeratorBase and the attribute on that class should be DtsForEachEnumerator. Copy the compiled assembly to %ProgramFiles%\Microsoft SQL Server\90\DTS\ForEachEnumerators.Summary.
