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:
- Type name. Fully qualified, in the format Namespace.Class.
- Assembly name. Without the ".dll" file extension. (Building your UI as a separate assembly lets you deploy UI changes without redeploying the enumerator itself.)
- Version. And don't include any extra zeroes! If the file version is "220.127.116.11", then "1.00.00.00" won't work.
- Culture. Typically, "neutral".
- 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", _
UITypeName:="MyNamespace.Samples.MyCustomEnumeratorClass,MyCustomEnumeratorAssembly," & _
Public Class ForEachExcelTableEnumeratorVB
. . .
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 email@example.com. Thank you!