A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, February 18, 2009

Foreach Nodelist Enumerator

As a followup to my fumblings with Foreach From Variable Enumerator, in theory I was right but I misremembered my available enumerators.

What I am attempting to do is keep an externally configured list that is consumed by an enumerator. That isn't a database table. I haven't given up on finding a useful case for the FEFVE, but as I have slightly more than a month before my project is due, I need to take a working path and run with it.

The solution I'm going to go with is fairly simple. I have a script task that will take my input string and transform it into a simple XML blob. That blob will be shredded by an Foreach Nodelist Enumerator and voila it's done. And really, I spent more time trying to build my XML using the real libraries instead of cobbling together strings than it took to wire the enumerator up.

From ProfessionalBlog


Given a starting value of FileList:Foo.txt;Bar.txt;Blee.txt

The Script task will generate output of



Foo.txt
Bar.txt
Blee.txt



An interesting thing to note, I am using a semi-colon as my delimiter. Assuming I tried to set the value at runtime via dtexec, the not-entirely-intuitive manner of escaping the semicolon is to put it all in quotes. And then escape the quotes.

C:\>dtexec /File XMLEnumerator.dtsx /Set \Package.Variables[User::FileList].Properties[Value];\"A.txt;b.txt;c.csv;d.dat\"


The package has been uploaded to my skydrive

References:
In memory XML Build a formatted XML document in memory by using an XmlTextWriter in VB .NET

No comments: