I was trying to find a way to repeatedly run the same MDX CREATE GLOBAL CUBE statement 70 times, building 70 different offline cubes for sales rep type folks to carry around for a client, and I just couldn't get it. I have some experience with SSIS, mostly from writing a tutorial on how to do RSS feeds with SSIS earlier that I am not sure anyone read :), but not with the ForEach container. I had spent the past few days attempting to get the cube building thing actually working, struggling mostly with the whole 64 bit issue. I was staring at having 70 ExecuteSQL tasks, each with very similar text, and that was not cool at all.
So, looking for ways to get this done, I finally decided that I would make the SSIS package work. So I started googling (though I might have used live search, but to say I started living is never going to catch on. Someday Microsoft will learn) around and found this nice little 10 minute video from Brian Knight on Jumpstart TV:
Using the For Each ADO Enumerator
I watched this video and followed along doing the stuff he said step by step. Worked like a charm. He also has an article about the same thing that covers things pretty well:
Looping through a ADO Resultset in SSIS
Another article that I used to get through this was this one:
How to configure a SQL Task that returns a ResultSet In SSIS
Okay I admit it. I worked ahead while Brian talked...some (thank goodness for rewind.) And I did one dumb thing, and the following error was returned:
"result binding name must be set to zero for full result set and XML results"
Well, okay...I remembered something about that. So I found the answer on Geoffrey Samper's DrivenBySQL blog (it hasn't been updated in a while, but the stuff there looks okay. I will say this, the referenced entry was spot on in fixing my issue.
So finally my solution was to create a SQL statement that returned a set of MDX statements, and run it in an ExecuteSQL task. Then use a ForEach container to loop through the set of statements and run them in another ExecuteSQL task.
My next task is to figure out how to do the parameter substitution in the SSIS task instead of falling back on my old habits of using SQL to solve every problem. As always, if you want information don't hesitate to ask. And likewise, if you know a better way, then by all means show me up. I don't mind at all!
Crossposted to: drsql.spaces.live.com