Notes from building a Custom ForEach Enumerator in SSIS

I recently posted about a quandary in which I found myself that led to me building my own extended ForEach File Enumerator in SSIS. All things considered, it was a reasonably straightforward experience, with most of my issues stemming from a relative unfamiliarity with Windows Forms development (I was always an ASP.NET man). The whole process can actually be split into four very simple steps to make things easier:

1. Create your Enumeration function.
2. Design your UI for SQL Server Data Tools (SSDT).
3. Validation and assignment of input from the UI.
4. Deploy your new component.

As long as your new custom component isn ’t too complicated, these steps can be completed very quickly, meaning you can be up and running in only a little longer than it would take to write everything in a Script Task, and think of the re-usability!

Create the enumeration function

This is the easy part. Begin by creating a new Class Library project in Visual Studio and give it a nice name. All this project needs to contain is a single .cs file. Add the following references to the project and import them at the top of your file.

using Microsoft.SqlServer.Dts.Runtime;  
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

At its most basic level, all you really need to do to create a custom enumerator, is override the existing GetEnumerator method, which you have exposed via the above references. Should you wish to pass further properties to your enumerator, you should also add these to your class now.

As noted by Dougbert in his great walkthrough, you need to make sure that the type returned by your method implements IEnumerator. NOT IEnumerable. For example, returning a List<T> will not work, but List<T>.GetEnumerator() will.

I ’ve not listed the code I used for my custom ForEach File Enumerator here, but you can see it on Github.

Design your UI

You know how I said the last part was the easy part? I lied. This part is the easy one, as all you need to do here is create another Class Library project in your solution and add a new Windows Forms User Control. This User Control will appear within the standard ForEach dialog box within SSDT.

You ’ll need to override at least three methods here, in order to properly hook up your UI: Initialize, Dispose and SaveSettings. Again, you want to add a reference to the relevant SSIS DLL to ensure that you can inherit correctly and not need to code from scratch. This time you want to add:

using Microsoft.SqlServer.Dts.Runtime;

You then need to derive your UI class from the ForEachEnumeratorUI class. You WILL get an error message, as Visual Studio will automatically create a partial class for your component that inherits from UserControl. Again, Dougbert explains how to resolve this error in his custom enumerator post.

An important thing to note here, is that in creating your own UI, you will lose some of the parts of the ForEach File Enumerator that you might take for granted, e.g. the Folder Path text box and Browse button, as well as the File mask box and Retrieve File Name option. If you want to continue to use these, you will need to add these components back onto your UI and reference them in your code-behind.

Validate and assign input

Once you ’ve designed your UI for the new component and it ’s looking mighty pretty (which will be seen at SSIS design-time via SSDT once we deploy), we can start thinking about how to get those values from the UI to the enumerator, so it can return the required collection. This is where I started having a bit of trouble with mine, mainly due to some issues passing property values from UI to enumerator, and trouble accessing locked variables from the hosting SSIS package.

It ’s extremely important that you ensure your input is validated and verified before passing to your enumerator, or you ’ll end up with lots of tasty errors when you try and validate your SSIS package. So make sure and validate all the input from the UI and make sure that all the properties you configured on your GetEnumerator method in step 1 are receiving the values that they need to operate. There ’s no point in having a Folder Path property that directs your enumerator to retrieve a list of files from said folder, then leaving that path blank. SSIS will not be nice about these errors, and you may find them somewhat obfuscated and hard to track down.

Luckily, those methods we overrode earlier provide us with a means to communicate with a handy host class for the ForEachEnumerator, which allows us to interact with properties, connections and variables that will be used by our actual enumerator class. We can assign this to a local variable within the UI code-behind for easy reference:

private ForEachEnumeratorHost _host;
private Variables _variables;
private Connections _connections;

public override void Initialize(ForEachEnumeratorHost FEEHost, IServiceProvider serviceProvider, Connections connections, Variables variables)
{

base.Initialize(FEEHost, serviceProvider, connections, variables);
_host = FEEHost;
_variables = variables;
_connections = connections;

}

Using the Initialize method, we can load any values which have already been saved to the component back into the UI for further editing. This prevents users from having to re-enter all their configuration data every time they open the UI to view/edit the component.

txtDirectory.Text = _host.Properties["SourceDirectory"].GetValue(_host).ToString();

The SaveSettings method allows us to pass content in the opposite direction, taking values from the UI components and assigning them to properties in the custom enumerator class.

_host.Properties["SourceDirectory"].SetValue(_host, txtDirectory.Text);

Be certain to validate properties and UI components before assignment, however. We don ’t want to be over-writing values with empty strings every time the UI is loaded, and so on.

Deploy the component

The final requirement prior to deploying the component is to actually hook up the UI to the new enumerator. Everything we did in the code-behind of the UI uses the base classes to allow interaction between the two objects, but to actually enable our new UI to show up in SSDT when we add our new enumerator from the toolbox, we have to join the dots. Go back to the enumerator class and add some attributes to describe its relationship to the UI.

To use my component as an example:

[DtsForEachEnumerator(
  DisplayName = "ForEachFileIntegerExpressionEnumerator",
  Description="Custom for each file enumerator that enumerates files as the result of an integer comparison against the filename. Only for files which have an integer as a filename.",
  ForEachEnumeratorContact="https://picnicerror.net",
  UITypeName = "PicnicerrorDotNet.SSIS.Enumerators.ForEachFileIntegerExpressionEnumeratorUI,PicnicerrorDotNet.SSIS.Enumerators.FileIntegerExpressionUI,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=7fef2b07d0ea7898")

To find out the PublicKeyToken of the UI component, first make sure you ’ve signed both projects (Project Properties > Signing), and then open a Visual Studio Command Prompt and run the command sn -T <assembly name>.

Then build your solution, and copy both DLLs into the "C:\Program Files (x86\)Microsoft SQL Server\110\DTS\ForEachEnumerators\" folder (This is the same for x86/x64).

Conclusion

My last post on the topic really only covered the setup of my problem and the different options I had available within SSIS, so I wanted to give a bit more information on exactly HOW I went about creating my own component, as there ’s very little information out there about this. I found Dougbert ’s post to be totally indispensible for this (hence all the links back to it), but I felt it was worth writing this follow up just to touch on the one or two areas that were specific to my experience that I didn ’t find in his post. Hopefully it ’ll help fill in one or two more blanks for the next person to come along.

I ’d be delighted to hear from anyone who ’s created their own SSIS components before and/or anyone thinking of attempting it for the first time. What were the major issues you encountered? Did this help at all? Is it worth exploring the subject in more detail?