Get looped, with your own custom Foreach Enumerator for SSIS

The lack of control flow structures such as looping was one of the main shortcomings of  Data Transformation Services (DTS). If you're nostalgic, just look at the scripting hoops that you had to jump through to loop, by disabling and re-enabling steps in the DTS package.

Then Integration Services came along and blessed us with the Foreach Loop Container! Now your package can loop over most of the things you want to, just by selecting the right enumerator and setting properties in the dialog box. Right out of the box, you can loop over:

  • the rows in an ADO recordset
  • the items in any ADO.NET schema rowset (and this is probably the most under-appreciated enumerator! For example, you can use it out of the box to loop through all the worksheets in an Excel workbook. See How to: Loop through Excel Files and Tables by Using a Foreach Loop Container.).
  • the files in a folder or folder tree
  • the items in an array or other list stored in a package variable, typically populated by a script
  • the items in a list that you enter manually
  • the nodes in an XML document, or the nodes that match an XPath query
  • any list of items enumerated by SQL Management Objects (SMO)

Do you have some custom collection that you'd like to loop over in your package? If you can write C# or VB well enough to populate an array, then you can probably write your own custom Foreach Enumerator. BOL gets you started: Developing a Custom ForEach Enumerator. I'm surprised that we don't see more examples of these -- custom enumerators and connection managers and log providers are about as simple as custom tasks (log providers don't even need a UI!), and certainly simpler than custom data flow components. Each has only 1 main method to focus on: GetEnumerator(), AcquireConnection(), Log(), and Execute() respectively.

Anyway, since I've recently been working on writing a custom foreach enumerator, here are some observations.

Gotchas when writing a custom foreach enumerator for SSIS

Returning the right type from the GetEnumerator() method

The overridden GetEnumerator() method is the most important method in your custom enumerator. It returns the entire list that you've created in a single method call. The return type is Object, but don't think that means you have options! You must return a type that implements IEnumerator. IEnumerable doesn't work. So you can't just return an array. But if you're using an array, there's an extremely simple solution: Just return Array.GetEnumerator(). (Thanks for rescuing me when I panicked, Matt Masson!)

Fixing the error message about the base class in your UI class

The UI of your custom enumerator is hosted inside the Foreach Loop Editor dialog box. For your UI, you create a UserControl that inherits from ForEachEnumeratorUI. You immediately get a warning. This is because Visual Studio has created a Partial Class that's hidden by default that inherits from UserControl, and the 2 pieces of your UI class can't inherit from different base classes. The solution is to display the hidden file (by clicking Show All Files on the Solution Explorer toolbar), then change the Partial Class to inherit from ForEachEnumeratorUI also. Since ForEachEnumeratorUI already inherits from UserControl, this works just fine.

Fitting your UI into the available space

The space available for your custom UI appears to be limited to 450 pixels wide by 260 pixels high. Someone should really add these important details to BOL.

Getting your UI to show up by getting UITypeName perfect

Now we get to the biggest challenge of all - getting your custom UI to show up in the Foreach Loop Editor dialog box by getting absolutely correct the UITypeName property. This is the property in the DtsForeachEnumeratorAttribute applied to your main class that tells SSIS Designer where to find the code for your custom UI. Sounds simple? It's very picky, and I'm still trying to find and clean up all the mangled examples in BOL that make it harder.

The 5 elements that you have to include, comma-delimited, inside a single set of double quotes as the value of UITypeName are:

  1. Type name. Fully qualified, in the format Namespace.Class.
  2. Assembly name. Without the ".dll" file extension. (Building your UI as a separate assembly lets you deploy UI changes without redeploying the enumerator itself.)
  3. Version. And don't include any extra zeroes! If the file version is "", then "" won't work.
  4. Culture. Typically, "neutral".
  5. Public key token. After you sign and build your assembly, you can extract the public key token by opening a VS Command Prompt and typing sn -T <assembly>.

A Visual Basic .NET example of the complete DtsForeachEnumeratorAttribute, including UITypeName, would be:

<DtsForEachEnumerator(Description:="My custom foreach enumerator for looping over stuff.", _
  DisplayName:="My Custom ForEach Enumerator", _
  ForEachEnumeratorContact:="", _
  UITypeName:="MyNamespace.Samples.MyCustomEnumeratorClass,MyCustomEnumeratorAssembly," & _
  "Version=,Culture=Neutral,PublicKeyToken=zzzzzzzzzzzzzzzz")> _
Public Class ForEachExcelTableEnumeratorVB
  Inherits ForEachEnumerator
  Implements IDTSPersist
  . . .
End Class

The Foreach Loop Editor wants your custom enumerator under the 32-bit Program Files (x86) folder on a 64-bit computer

This makes sense, since BIDS is a 32-bit development environment. I assume (but have not yet confirmed) that at 64-bit run time, the 64-bit version is used from the 64-bit folder, if present.

Testing a custom foreach enumerator for SSIS

As you test (without debugging), you will typically have your enumerator code project open in one instance of Visual Studio, and your test package open in a second instance. Each time you rebuild and redeploy your enumerator assembly, you have to close and reopen the instance of VS that contains your test package.

Use post-build scripts in VS to make your life simpler. Here's what I use after each build to uninstall the previous version of the assembly from the global assembly cache (GAC), install the new version, and copy it to the deployment folder:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\x64\gacutil.exe" -u $(TargetName)
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\x64\gacutil.exe" -i $(TargetFileName)
copy $(TargetFileName) "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\ForEachEnumerators"

In spite of the stopping and restarting, it's handy to be able to step into both the run-time and the design-time methods of your custom enumerator. Follow the debugging steps in BOL: Building, Deploying, and Debugging Custom Objects.

I hope that these suggestions are helpful to the ambitious coders among you!

Please comment on my blog!

You don't have to sign up or sign in - anonymous comments are enabled. I "moderate" the comments to delete spam before you see it, but otherwise, I publish your comments immediately. You can also email me at Thank you!


Comments (4) -

SQL Lion

For more information on For each loop enumerators (Foreach File Enumerator using Excel files) and programming integration service (SSIS) using C# .Net and VB .Net, please visit the below link:">–-enumerating-excel-files/


Good topic!  I am facing a task right now to go through a directory and its subdirectories without knowing where the root directory starts.  First I thought that enumerating from variable is the way to go, but quickly realized that it's for something else.  Do I need to write a custom enumerator?  I would like to pass the starting directory as a parameter, so that the ForEach loop container will store this value in a variable.  Pointers?



You can pass variable values into many of the properties of the ForEach File Enumerator, by using an SSIS expression that merely passes the variable. However to do this you have to select "Expressions" on the Collection page of the ForEach Loop Editor - that is, on the same page where you chose and configured the ForEach File Enumerator. If you look at the available properties on the Expressions page of the editor, on the other hand, you'll only see the generic properties for the ForEach Loop container, not of the specific enumerator that you've chosen.



Great post Doug, and very helpful even 4 years on, given that there's still very little quality information available on the web regarding custom SSIS component authoring.  Helped me get started on my first custom ForEach Enumerator.


Pingbacks and trackbacks (2)+