THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

SSIS: ForEach Looping through a recordset

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
http://www.jumpstarttv.com/Media.aspx?vid=38

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
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

Another article that I used to get through this was this one:

How to configure a SQL Task that returns a ResultSet In SSIS
http://sqlug.be/blogs/drivenbysql/archive/2006/07/29/260.aspx

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

Published Friday, January 19, 2007 12:26 AM by drsql
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

J.L. said:

This is an unhelpful post. The article is a dead link, and the video requires registration and login.  a waste of a google hit.

February 3, 2010 12:59 PM
 

drsql said:

J.L. Can't say I disagree. This is a 3 year old post, so it is bound to be outdated. I don't see why having the video require registration is a bad thing. Everybody has to pay the rent somehow.

Good luck..

February 4, 2010 12:17 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement