• Article

Creating a Custom Foreach Enumerator

The steps involved in creating a custom foreach enumerator are similar to the steps for creating any other custom object for Integration Services:

  • Create a new class that inherits from the base class. For a foreach enumerator, the base class is ForEachEnumerator.

  • Apply the attribute that identifies the type of object to the class. For a foreach enumerator, the attribute is DtsForEachEnumeratorAttribute.

  • Override the implementation of the base class's methods and properties. For a foreach enumerator, the most important is the GetEnumerator method.

  • Optionally, develop a custom user interface. For a foreach enumerator, this requires a class that implements the IDTSForEachEnumeratorUI interface.

A custom enumerator is hosted by the ForEachLoop container. At run time, the ForEachLoop container calls the GetEnumerator method of the custom enumerator. The custom enumerator returns an object that implements the IEnumerable interface, such as an ArrayList. The ForEachLoop then iterates over each element in the collection, provides the value of the current element to the control flow through a user-defined variable, and executes the control flow in the container.

For a working sample of a custom enumerator, see the Integration Services samples on Codeplex.

Getting Started with a Custom ForEach Enumerator

Creating Projects and Classes

Because all managed foreach enumerators derive from the ForEachEnumerator base class, the first step when you create a custom foreach enumerator is to create a class library project in your preferred managed programming language and create a class that inherits from the base class. In this derived class you will override the methods and properties of the base class to implement your custom functionality.

In the same solution, create a second class library project for the custom user interface. A separate assembly for the user interface is recommended for ease of deployment because it allows you to update and redeploy the foreach enumerator or its user interface independently.

Configure both projects to sign the assemblies that will be generated at build time by using a strong name key file.

Applying the DtsForEachEnumerator Attribute

Apply the DtsForEachEnumeratorAttribute attribute to the class that you have created to identify it as a foreach enumerator. This attribute provides design-time information such as the name and description of the foreach enumerator. The Name property appears in the dropdown list of available enumerators on the Collection tab of the Foreach Loop Editor dialog box.

Use the UITypeName property to link the foreach enumerator to its custom user interface. To obtain the public key token that is required for this property, you an use sn.exe -t to display the public key token from the key pair (.snk) file that you intend to use to sign the user interface assembly.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Namespace Microsoft.Samples.SqlServer.Dts
    <DtsForEachEnumerator(DisplayName = "MyEnumerator", Description="A sample custom enumerator", UITypeName="FullyQualifiedTypeName,AssemblyName,Version=1.00.000.00,Culture=Neutral,PublicKeyToken=<publickeytoken>")> _ 
    Public Class MyEnumerator
     Inherits ForEachEnumerator
        'Insert code here.
    End Class
End Namespace

using System;
using Microsoft.SqlServer.Dts.Runtime;
namespace Microsoft.Samples.SqlServer.Dts
{
    [DtsForEachEnumerator( DisplayName = "MyEnumerator", Description="A sample custom enumerator", UITypeName="FullyQualifiedTypeName,AssemblyName,Version=1.00.000.00,Culture=Neutral,PublicKeyToken=<publickeytoken>")]
    public class MyEnumerator : ForEachEnumerator
    {
        //Insert code here.
    }
}

Building, Deploying, and Debugging a Custom Enumerator

The steps for building, deploying, and debugging a custom foreach enumerator in Integration Services are very similar to the steps required for other types of custom objects. For more information, see Building, Deploying, and Debugging Custom Objects.

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 or TechNet:

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