Re: Iterative loop through SQL expression

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:

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?




.

Relevant Pages

  • Re: Problem with SQL Task in MS SQL Server DTS package
    ... I'm not an SQL expert but it looks to me like I'm using the ... How could I word my query without the aliases? ... "Allan Mitchell" wrote: ... DTS is unable to offer you ...
    (microsoft.public.sqlserver.dts)
  • Re: Dynamic Query Order in DTS
    ... are you using the Dynamic Properties task for something else because you do ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> I support PASS - the definitive, global community ...
    (microsoft.public.sqlserver.dts)
  • Re: INSERT Problem
    ... You were right Allan. ... SQL told me that it was not a valid ... >is invalid syntax in SQL Server. ... >> in your query? ...
    (microsoft.public.sqlserver.dts)
  • Re: batch update
    ... my $sql = 'UPDATE IGNORE ordes SET activation_date=?, ... from thr local DB I do a select query and while the select query run ... foreach row I do a UPDATE query: ... my $sql = <<EOT; ...
    (perl.dbi.users)
  • Re: DTS with SQL Express 2005
    ... Thanks Allan ... I suspect that the Import/Export utility may have been removed from the ... release version of SQL Express ...
    (microsoft.public.sqlserver.dts)