For Each From Variable Enumerator

I had a scenario come up at work where I'll be downloading N files from our mainframe. I don't want to grab all the files as there are other processes that will need to use them. I'm loathe to hardcode a list as as soon as I do that, something will change. My first solution was to use the For Each Item Enumerator and it was great. I made a list with an element corresponding to each file I needed and all is well and good in the world. It enumerated through the list and I was able to download each file just fine.

Wait, something's missing. Oh yes, externally configurable. A quick question on the MSDN SSIS boards revealed that I was SOL using the For Each Item Enumerator and external configuration. There are other flavors of the enumerator and this For Each From Variable Enumerator sounded interesting.

Take a quick moment to read up on what it does. It won't take long, go on, click the link and this will wait.   http://wiki.sqlis.com/default.aspx/SQLISWiki/ForEachFromVariableEnumerator.html According to the above, the variable enumerator splits a string out by characters. That could actually work out well for my scenario: a finite list of elements, file names in this case, is defined in a variable which is externally configurable. I could use the FEFVE to iterate through the list and concatenate the values until the separator has been reached. At that point, I'd perform the logic in the special branch. In my scenario, I'd download the file.

Simple, not horribly inelegant and it answers the question from SQLIS of "where would you use it." And if merits a blog post, then obviously it wasn't that easy. I admit, the wiki page did say I needed to create 2 variables, one to provide the value and one to be the current element (Thanks Doug). What didn't click was that the second item needed to be of type Object. It's just a string so I set it as such, wired up all my logic and let it run. Dur bluh, can't cast to type string exception. I guess the wiki article wasn't just being overly cautious with the use of type Object. Dear reader, do you know what's a pain in the Integration Services? Objects. They cannot be used in Expressions, even if you cast them. My original control flow was elegant, I had a third variable that was simply the concatenation of itself plus the current character. There went my simple solution, dashed against the rocks like some Björk song.

The hour grows late and if I don't post this tonight, it'll be another few months before I get back to it so this is posted without a working solution. The challenge I have run into is that even though the local inspector window can gracefully turn that object into a character, heck it even shows it in the post from SQLIS, I'll be damned if I can get the VB script to spit it out.

A thousand words here will help show how I thought about making use out of FEFVE

From ProfessionalBlog

Package is on my skydrive

[edit 2009-02-17]I had an insight this morning that might be much better way to use a standard iterator and external configuration. In short, continue using the variable as the source but have a script step outside the loop perform a split on the separator and push that into an variable of type object. I think one of the enumerators will be able to pop iterate over a .NET arrary. Or I suppose I could dump it into a different data structure that implements IEnumerable if that's easier/better. I'll either follow up this post with the results or edit this one. Follow up post: Foreach Nodelist Enumerator[/edit]