programming4us
programming4us
DATABASE

Using SQL Server 2005 Integration Services : Extensibility (part 4) - Custom Connection Managers

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/7/2012 6:21:22 PM

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.

Figure 6. Add SSIS Connection Manager

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.

Other  
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 3) - Reading and Writing a Data Set as XML
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 2) - Data Binding
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 1) - Creating and Accessing DataSet, DataTable, and DataView Objects
  •  .NET Compact Framework 3.5 : Examining ADO.NET
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 4) - Connecting the Source and Destination Adapters with a Path
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 3) - Setting Up Column Information
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 2)
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 1) - Creating Packages Programmatically - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 2) - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 1) - Control Flow
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us