ForEachLoop task not behaving as expected

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 > script2 > script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Do you have the tasks hooked together by precedence constraints? (The green arrows?)|||

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

foreach loop?

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

You need to load the contents of the file into an ADO Recordset using a data-flow. You can then shred that recordset using the ForEach loop. This example demonstrates the same - the only differrence being that the ADO recordset is populated using an Execute SQL Task rather than a data-flow. The shredding is exactly the same though: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

|||I need a sql Query to loop through a column in one table reading the ID of tenants, the result being a list of the Tenants names from another table with the same tenantID's. The captured data needs to be filled into textboxes on a form.sql

foreach loop?

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

You need to load the contents of the file into an ADO Recordset using a data-flow. You can then shred that recordset using the ForEach loop. This example demonstrates the same - the only differrence being that the ADO recordset is populated using an Execute SQL Task rather than a data-flow. The shredding is exactly the same though: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

|||I need a sql Query to loop through a column in one table reading the ID of tenants, the result being a list of the Tenants names from another table with the same tenantID's. The captured data needs to be filled into textboxes on a form.

ForEach Loop Container and First Task

Hi,

My Foreach Loop container has 10 different task inside. I want to execute the first task only one time. I have a variable with increases for each repition. How can I put precedence contstraint on the first task so that it should execute only first time and other task has to execute all the time.

Thanks

You can add a property expression on the task that will set the Disable property to True/False accordingly

Thanks,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

You can add a property expression on the task that will set the Disable property to True/False accordingly

Thanks,
Ovidiu Burlacu

Far be it from me to disagree with Ovidiu (who knows far more about SSIS then I ever will) but I think a better approach would be to make all of the tasks execute after an empty sequence container. You can then put an expression on the precedence constraint that goes between the sequence container and the task you only want to execute on the first iteration.

-Jamie

ForEach Loop and Data Flow

In my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.

Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.

This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?

Thanks,
Gary

We need to get the column values for each record in the "ForEach ADO Enumerator" in some variable.
Then for the tasks inside the ForEach container - we can use those variables.

Similar example is @. http://www.codeproject.com/useritems/foreachadossis.asp

Thanks,
Loonysan

sql

ForEach from query

Hi All

I'm sure this is a simple thing to do, but I'm new to SSIS and trying to catch up fast.

I want to execute a query on the database which will give me a path and a filespec, say:

c:\apps\testapp1

and

fred*.csv

No problems here.

I then want to feed them into a ForEach loop and interate through all the files matching the filespec at that location. I can't figure this out at all.

Thanks for you help in advance.

FG

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Jamie's blog has good info on how to use the ForEach loop container with the ForEachFile enumerator. You can supply a filepath and extenstion with wildcard, fetch back filenames and update a connection strimg. Good stuff.

But I think what you were asking was how to dynamically update the enumerator with different paths and filenames? First it might depend on what your really trying to do.

If you just want to stream in the files then you can also not bother with the loop and instead update the connection string of a 'multiflatfile' connection manager with a property expression of what the file info should be. The key diffference between the multiflat file is that it will accept wildcards...so it can take c:\test\*.txt and the flat file source that uses that conection manager will just load all of the files. So, its functionally different than Jamies route...both have their uses. Looping over the files will load them one at a time, starting/stoping the dataflow each time BUT you can get file specific information such as useing rowcount transform. If you used a rowcount with the wildcard approach and multifileconnection mgr then you just get 1 rowcount result which would include all rows from all files. Again, each method has its place.

Now I think what you really are asking is how to tweak on the fly the folder (directory) and files (fielspec). Well, unfortunately you cannot use property expressions on those properties. Its a current limitation. They are not really properties of the ForEach Container but of the specific enumerator (ForEachFile) which you chose. However you can do it indirectly, using Configurations and having 2 packages, one calling the other, passing in the appropriate new values.

So the parent package uses and ExecuteSQL task to fetch the inforation from a table,returning "path" and "extension" to 2 varirables, all defined in the ExecuteSQL task.

You create a 2nd package with a For Each Loop.
Child: you create 'package Configurations' of the type "parent Package Variable'. one maps to the 'filespec' property and one to the 'directory' of the For each loop

Parent: Then from the parent you add an ExecutePackage task which calls the child package.

So flow is...

Parent ExecuteSQL to populate 2 vars
Parent Executes Child Package
Child Configurations are first thing to be 'pulled' from parent as Child package starts
Child ForEach Loop excutes and the appropriate properties are already update

I suggest reading aobut parent package configurations if you have not already. I think I also have a sample I could send you.

Hope that helps

|||

Very many thanks for both replies.

Craig is correct in his understanding of what I am trying to do. ie, get a path and a filespec from the DB and use these to control the ForEach loop. I'm pleased to hear that it can't be done directly at present, I hadn't missed something too obvious!

I will try your suggestion shortly Craig.

Thanks to Jamie for his input too.

FG

ForEach DataFlow Task

I want to be able to loop through a view and execute a dataflow task for each record. I would like to pass the value of a column to the dataflow task to be used as a parameter in a data reader.

How can I do this?

Danny Crowell wrote:

I want to be able to loop through a view and execute a dataflow task for each record. I would like to pass the value of a column to the dataflow task to be used as a parameter in a data reader.

How can I do this?

I don't understand...could you clarify it?

What do you mean with 'loop through a view'? or you mean the rows in a view?...Then how is that you want t pass a column as parameter?

|||

Danny Crowell wrote:

I want to be able to loop through a view and execute a dataflow task for each record. I would like to pass the value of a column to the dataflow task to be used as a parameter in a data reader.

How can I do this?

Load an Execute SQL task with the SQL for the view into an OBJECT-typed variable. Then, using a foreach loop against that ADO recordset, you can grab a column and stick its contents into another variable that you can use in the foreach loop-contained data flow.|||Here is an artilce from Brian Knight that helped me with this.
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

For Loop Container does not check condition first time

Hi,
It seems like For Loop Container works like do-while loop in C++. I have set its EvalExpresstion to @.[User::SyncStats]==0 , I have an Execute SQL Task before this container that sets the variable to 1 but Container still executes atleast one time.

Anybody else been through this ?Curious, what happens when you set the For Loop's DelayValidation = True?|||

Phil Brammer wrote:

Curious, what happens when you set the For Loop's DelayValidation = True?

Same behavior|||Do you have anything in InitExpression?

What happens if you set the User::SyncStatus variable to 0 in the variables window and then try to run it?|||Well, There is an Execute SQL Task before the container which is responsible for initializing value on the basis of the return value from Stored Procedure.

For just the curiosity, Yes it works if I set the value in InitExpression. But I want it to use the value set by prior task.|||Well,
Silly me,

There were actually 2 variables of same name but one with Loop Container scope one with global scope.

And I wanted Loop Container to check the Global one. I changed the namespace and it works

Thanks anyways.

For Each in SQL?

Hi. using SQL Server 2000 here.

What I want to do is pretty much execute a stored procedure, giving it the value of a field.

The thing is, since I am still learning SQL, I am new to this and have no idea how it should be done....

The way this has to be is, execute a query (SELECT someField FROM SomeTable)

then, foreach record for this someField column, I want to be able to get the someField value, and give it to a stored proc as a parameter (EXEC sp_whatever @.p1)

how would I achieve this? is this possible?

First off, this is generally a bad idea if you can avoid it. Most of the time if you can rewrite it to where it acts in a single statement, that is the best way.

However, if you are stuck with the stored procedure:

create table test
(
testId int primary key
)
go
insert into test
select 1
union all
select 2
union all
select 3
go

declare @.cursor cursor, @.testId int
set @.cursor = cursor for select testId from test
open @.cursor

while 1=1
begin
fetch from @.cursor into @.testId
if @.@.fetch_status <> 0
break
exec sp_whatever @.testId --also, you should shy away from using sp_ as a
--procedure name as this is the standard for
--system objects

end

|||

ah ok, if you say its generally the bad way, which is what I assumed, please tell me a better way of doing this.

The current situation is.... I will need to send out emails to a list of email addresses from my ASP.NET site.

Since I do not want the user to have to wait impatiently, as there could be several hundred email addresses, I want SQL Server to take care of it and thought perhaps this would be the best way - create a job and run it, and this job will send out the emails using a stored proc.

What do you think? What is the best practice? I always want to use best practice. should such a thing be done in SQL?

|||

Hi...

Thats a valid use for cursors... But i try to avoid them like there is no tomorrow.

Anyway... Since sending an email is a "slow" process you should consider decoupling it from your procedure that gets called when a "user" does anything. If he is requesting 1000 email to be send, and your sql server will stop responding for that time, then the user might think that your website is broken...

This can be done in 2 ways... One in SQL 2005 would be the service broker (Check BOL), and a solution in SQL 2000 would be that you create an email table, populate it (without a cursor) when the user wants to send his email, and then process it asyncronically (spelling?) in a job thats executing regulary (You can use a cursor in this job...

But make sure you only use the type of cursor you realy need).

Another solution to avoid cursors would be that you can do a select top 1 in a while loot and evaluating rowcount...

|||Many thanks, wow soo many things to consider. *overload*|||

Hatzi74 wrote:

This can be done in 2 ways... One in SQL 2005 would be the service broker (Check BOL), and a solution in SQL 2000 would be that you create an email table, populate it (without a cursor) when the user wants to send his email, and then process it asyncronically (spelling?) in a job thats executing regulary (You can use a cursor in this job...

I would suggest the email queue table for either version. It is just simpler to work with in your other code, since you can do a simple insert (and more than one row at a time!)

Also, in 2005 the email isn't sent immediately, so it is a pretty fast call to the db_send_mail procedure. (and it can be rolled back if you are in a transaction: http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!913.entry)

Hatzi74 wrote:

But make sure you only use the type of cursor you realy need).

Agreed.

Hatzi74 wrote:

Another solution to avoid cursors would be that you can do a select top 1 in a while loot and evaluating rowcount.

While this can be a bit faster in some situations, it is best to just avoid looping altogether if possible :)

|||

Many thanks

fortunatly I found another way of doing this, after following and using the example, without having to use SQL *pheeww* so yes, great replies but was bad practice.

thread should really be removed to prevent people using bad practice lol