Iterative loop through SQL expression

#1

Very long story short, I have a group of steps in an SSIS package that I
would like to loop through based on the values returned from an SQL SELECT
statement. I've tried to set up the ForEach Loop container to process the
statement, but have been unsuccessful. How do I tell the container to
iteratively process the key values returned by the SELECT?

#2

Hello Mike,

So you have a SELECT statement that returns a rowset. You want to loop over
the rowset and execute n Tasks?

Read the SELECT statement into a Rowset variable (object)

Now in the ForEach loop use the ForEach ADO Enumerator type and add your
variable when prompted.

Is this what you want to do ?

Allan

Quote:

Very long story short, I have a group of steps in an SSIS package that
I would like to loop through based on the values returned from an SQL
SELECT statement. I've tried to set up the ForEach Loop container to
process the statement, but have been unsuccessful. How do I tell the
container to iteratively process the key values returned by the
SELECT?

#3

Allan:

That should do it. Thanks for the assist.

mc

"Allan Mitchell" wrote:

Quote:

Hello Mike,

So you have a SELECT statement that returns a rowset. You want to loop over
the rowset and execute n Tasks?

Read the SELECT statement into a Rowset variable (object)

Now in the ForEach loop use the ForEach ADO Enumerator type and add your
variable when prompted.

Is this what you want to do ?

Allan

Very long story short, I have a group of steps in an SSIS package that
I would like to loop through based on the values returned from an SQL
SELECT statement. I've tried to set up the ForEach Loop container to
process the statement, but have been unsuccessful. How do I tell the
container to iteratively process the key values returned by the
SELECT?

#4

Mike: Can you tell me EXACTLY how you accomplished this scenario? I
understand Allan's GENERAL instructions, but I can't seem to put it into
practice.

For example, Allan says,

Quote:

Read the SELECT statement into a Rowset variable (object)
GREAT! How do I do that? Do I need a separate Data Flow to make that happen?

Once I do that, how do I retrieve the values from each row?

Thanks in advance.

Todd C

"Mike Coffey" wrote:

Quote:

Allan:

That should do it. Thanks for the assist.

mc

"Allan Mitchell" wrote:

Hello Mike,

So you have a SELECT statement that returns a rowset. You want to loop over
the rowset and execute n Tasks?

Read the SELECT statement into a Rowset variable (object)

Now in the ForEach loop use the ForEach ADO Enumerator type and add your
variable when prompted.

Is this what you want to do ?

Allan

Very long story short, I have a group of steps in an SSIS package that
I would like to loop through based on the values returned from an SQL
SELECT statement. I've tried to set up the ForEach Loop container to
process the statement, but have been unsuccessful. How do I tell the
container to iteratively process the key values returned by the
SELECT?

#5

Hello Todd,

For reading into a Rowset variable have a look here

http://www.sqlis.com/default.aspx?59

You could also do the assignment through an ExecuteSQL task

http://www.sqlis.com/default.aspx?58

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Quote:

Mike: Can you tell me EXACTLY how you accomplished this scenario? I
understand Allan's GENERAL instructions, but I can't seem to put it
into practice.

For example, Allan says,

Read the SELECT statement into a Rowset variable (object)

GREAT! How do I do that? Do I need a separate Data Flow to make that
happen?

Once I do that, how do I retrieve the values from each row?

Thanks in advance.

Todd C

"Mike Coffey" wrote:

Allan:

That should do it. Thanks for the assist.

mc

"Allan Mitchell" wrote:

Hello Mike,

So you have a SELECT statement that returns a rowset. You want to
loop over the rowset and execute n Tasks?

Read the SELECT statement into a Rowset variable (object)

Now in the ForEach loop use the ForEach ADO Enumerator type and add
your variable when prompted.

Is this what you want to do ?

Allan

Very long story short, I have a group of steps in an SSIS package
that I would like to loop through based on the values returned from
an SQL SELECT statement. I've tried to set up the ForEach Loop
container to process the statement, but have been unsuccessful.
How do I tell the container to iteratively process the key values
returned by the SELECT?

#6

Allan: Thank you for the links. Got those pages bookmarked for future issues!

I was able to accomplish this with help from your book, "Pro SQL 2005 IS",
with an Execute SQL Task (outside), a Script Task (inside), and the ForEach
container. The SQL Task reads the recordset into an object variable, the
ForEach shreds it to the assigned variables for each respective column, and
the Script builds dynamic SQL and assignes it to another string variable for
use in the Data Source.

The ultimate goal was to iterate through a bunch of tables that had
identical schema design and send them all to a Fact table in the warehouse.

Yes, a simple UNION ALL would work, but I wanted a way to dynamicly choose
which tables to pull from based on entries in a meta table.

Thanks for your suggestions.

Keep them coming!

Todd C

"Allan Mitchell" wrote:

Quote:

Hello Todd,

For reading into a Rowset variable have a look here

http://www.sqlis.com/default.aspx?59

You could also do the assignment through an ExecuteSQL task

http://www.sqlis.com/default.aspx?58

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Mike: Can you tell me EXACTLY how you accomplished this scenario? I
understand Allan's GENERAL instructions, but I can't seem to put it
into practice.

For example, Allan says,

Read the SELECT statement into a Rowset variable (object)

GREAT! How do I do that? Do I need a separate Data Flow to make that
happen?

Once I do that, how do I retrieve the values from each row?

Thanks in advance.

Todd C

"Mike Coffey" wrote:

Allan:

That should do it. Thanks for the assist.

mc

"Allan Mitchell" wrote:

Hello Mike,

So you have a SELECT statement that returns a rowset. You want to
loop over the rowset and execute n Tasks?

Read the SELECT statement into a Rowset variable (object)

Now in the ForEach loop use the ForEach ADO Enumerator type and add
your variable when prompted.

Is this what you want to do ?

Allan

Very long story short, I have a group of steps in an SSIS package
that I would like to loop through based on the values returned from
an SQL SELECT statement. I've tried to set up the ForEach Loop
container to process the statement, but have been unsuccessful.
How do I tell the container to iteratively process the key values
returned by the SELECT?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Iterative loop through SQL expression

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Iterative loop through SQL expression - 05-15-2006 , 01:17 PM

Re: Iterative loop through SQL expression - 05-15-2006 , 01:28 PM

Re: Iterative loop through SQL expression - 05-15-2006 , 01:50 PM

Re: Iterative loop through SQL expression - 11-20-2006 , 12:04 PM

Re: Iterative loop through SQL expression - 11-20-2006 , 12:59 PM

Re: Iterative loop through SQL expression - 11-22-2006 , 07:14 AM