Creating a Custom Foreach Enumerator

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

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.

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.

See Also

Coding a Custom Foreach Enumerator
Developing a User Interface for a Custom ForEach Enumerator