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, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

ForEach Loop Editor

Help,

Trying to through a process together and using the "ForEach Loop" task. When configuring the task and using the ForEach Loop Editor I do not have the "ForEach File Enumerator" in the drop down. Anyone know whats going on or what I need to do to make this appear. I only see 5 selections: ADO, ADO.Net Schema Rowset, Variable, NodeList, and SMO Enumerators. I am also using the SQL 2005 eval with SP1.

Thanks,

John

This has been asked many times before:

https://forums.microsoft.com/MSDN/Search/Search.aspx?words=enumerator+missing&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

-Jamie

|||

Jamie,

Thanks for the info, but feel uncomfortable since I have never compiled C# and not sure if I can do this within BIDS. After reading the posts it appears that some of the registry settings do not have the correct permissions. Would you happen to know which reg settings I need change the permissions for and what permissions they need to have?

Thanks,

John

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 Container

I am using a SQL Task to load the data needed for the loop into a variable, then using a script task to perform calculations. Is it possible to update a table from the script task with the calculated values?.

Thanks
You can use System.Data and ADO.Net inside a script task. You could even use an ADO.Net connection manager. Just write some normal VB.Net code to do your data access.

I don't know what you are doing, but I would question this method. Why not do it all in a single SQL Task? Why use a SQL task to do the update? Why not do it all in a script task?|||I want to test the performance difference between a cusor in sql 2000 and the loop in sql2005.|||

I wouldn't recommend that as a use of SSIS... instead, consider revisiting what you do with the cursors to be done using the data flow task.

regards,
ash

|||Let me explain what I am trying to do. I have +/- 600 performance reports to calculate each with 40 odd items. If I'm on the wrong path please point me in the right direction. I would appreciate any help.|||

Do all performance reports have the same structure just different data? Are all the calculations identical as well?

I'm intruiged by the problem and can work with you on how to do this in most efficient manner, we can then let the group know what we found.

regards,
ash

|||

Yes the structure for all reports are the same, the calculations are dependant on the specific measure.

May I contact you offline and not involve the forum until a suggestion is ready

Foreach Loop - Value back to 0

Hi,

Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data.

Somewhere inside the Foreach Loop, you set an int variable MyInt to a value.

My question:

Is it possable when each iteration begins to set MyInt to 0?

Thanks in advance.

Is the foreach loop evaluating the same variable, or are you using the variable for something else?

Script tasks can assign values to variables.|||Can you set a value of a variable inside an expression?|||

MrHat wrote:

Can you set a value of a variable inside an expression?

No.|||But you could set it by running "SELECT ? = 0" inside an Execute SQL task at the start of your loop, and assigning that output parameter to your variable.

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

ForceExecutionResult is ignored by parent

1 Add a Sequence Container.
2 Add a Script Task to the contianer, and set the script to fail, Dts.TaskResult = Dts.Results.Failure
3 Set the ForceExecutionResult on the Script to Success.
4 Execute the package.

The container fails, so it detects the Dts.Results.Failure, and ignores ForceExecutionResult. The task itself is Green, so that indicates success. I think this is wrong, as ForceExecutionResult should be observed by both the task and parent container. Not that it should matter, I think, FailParentOnFailure is false. By design? (Sep CTP)
Ok, so there is a simpler issue here. The sequence container takes no notice of the ForceExecutionResult. Try a container with a simple task that always succeeds. Set ForceExecutionResult on the container to Failure, and the container still succeeds (goes green).|||

Containers don't fail because their children fail. Containers fail because one of their settings (MaxErrorCount, FailParentOnFailure, etc) mandates it to fail.

The task isn't raising an error. So MaxErrorCount doesn't take effect. A failed task doesn't fail the parent by default. Neither is FailParentOnFailure set to true.

Here, like the Disable/Enable, is a brute force method of making something happen. You've made the conceptual assumption that because tasks fail when they have errors, if the task is forced to look like it failed, the parent should behave the same as a task that failed because it had errors.

The feature does what it says and no more. It forces the return value to false. Nothing more. It effects only those features that key on the return value. Precedence constraints are effected by the execution result, failing the parent is not. Neither is there an error raised.

If you set fail parent or fail package on failure, it will fail the sequence.

If you return an error from the script task, it will fail the parent if MaxErrors is set to 1.

HTH,
K

force exit

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

"to force exit" is equivalent with "no execution", so

try to use "precedence constraints" (on that green arrow): if "a_condition" is true execute ftp task else is not run anythink;

to get the information that the file exists i think it is a possibility using WMI in a ActiveX script task and testing a variable (to build "a_condition")- it is an ideea|||I have a success contraint to continue if it's there and a on failure constraint to quit, which it does but it keeps sending my on error email but there is no error? I even have the failpackageonfailure property set to False and it still keeps giving the error.|||

I got help by going to this blog, it worked perfect.

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

|||

Exactly what I thaught: the blogger build the condition and use the precedence constraints !

force exit

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

"to force exit" is equivalent with "no execution", so

try to use "precedence constraints" (on that green arrow): if "a_condition" is true execute ftp task else is not run anythink;

to get the information that the file exists i think it is a possibility using WMI in a ActiveX script task and testing a variable (to build "a_condition")- it is an ideea|||I have a success contraint to continue if it's there and a on failure constraint to quit, which it does but it keeps sending my on error email but there is no error? I even have the failpackageonfailure property set to False and it still keeps giving the error.|||

I got help by going to this blog, it worked perfect.

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

|||

Exactly what I thaught: the blogger build the condition and use the precedence constraints !

For/Foreach loop

Hi,

I'm looping through some query data and doing a Script Task check inside the loop on a datetime field.

If the datetime happens before 10 o'clock:

- Store the data row in Table1

If the datetime happens after 10 o'clock:

- Store the data row in Table2

How can I store the datarow inside the loop without inserting it into a database table?

I need to access the data in the next step after the loop. How can I do this?

Thank you very much!

Can you store the values in some variables?

|||

Thanks for the reply.

Yes, I could. But if I'm looping through 100 lines of query data, and I want to store 50 of them, wouldn′t it mean a whole lot of variables to store all the data?

Am I misunderstanding you perhaps?

|||If you have a single source connection for the data and you want to split the rows based on the value of a certain column, used a conditional split, as per previous post, and then write the rows to a raw file for later use.

Other option would be to generate the rows based on the conditions and then write them to variables, as per Jamie's option, and use them later.

Depending on the number of rows and different conditions you have, either option would work. Variable route may give performance / resource problems on large numbers though.|||

MrHat wrote:

Thanks for the reply.

Yes, I could. But if I'm looping through 100 lines of query data, and I want to store 50 of them, wouldn′t it mean a whole lot of variables to store all the data?

Yes it would. Is that a problem?

MrHat wrote:

Am I misunderstanding you perhaps?

I don't think so

-Jamie

For Travis Lowdermilk

I dont know if anyone mentioned this, but I would like to have the ability to disable objects in a Data Flow Task

Travis,

I've split this into a seperate thread because it isn't relevant to the previous thread.

What is the scenario for wanting to do this? I don't understand why this would be required or how it would even be possible.

e.g. If you have a data-flow with 3 components, components 2 & 3 would not be able to execute if component 1 were disabled. The unit of execution in SSIS is a task, not a component.

-Jamie

|||

Jamie -

I apologize for posting in the wrong topic.

The reason I think it would be cool to have the ability to disable a particular object in a Data Flow Task is:

Scenario:

Let say I have a Multicast that splits the data to into two different directions:

Direction 1: goes to a JOIN and then eventually to an OLE DB DELETE Transform

Direction 2: goes through a Lookup transform and then to an OLE DB UPDATE or INSERT.

I may want to test just Direction 2 exclusively (maybe I know Direction 1 isn't going to work yet <maybe there was a change on the db side that makes this path invalid now>)

It would be nice to disable the path on the Direction 1 side and just focus on Direction 2.

Or maybe I want to just see if the LOOKUP (Direction 1 path) is redirecting the correct number of rows but I don't want to execute the INSERT OLE DB at the end of the path.

I have already created an extensive OLE DB INSERT statement that I don't want to have to recreate. Disabling it would be nice.

I am no expert at this sort of stuff, so it is quite possible that my sense of design is flawed and therefor I have to rely on deleting/cut and pasting items in the Data Flow Task to have them be ignored during debugging.

Hope that makes sense :-)

|||

Yeah that does kinda make sense. I guess that rather than disabling a component it'd be more accurate to say "stop the flow at this point".

I think its a valid request. You should request it at the feedback center.

In the meantime you can achieve the same thing by putting a data viewer on the path that you're not interested in. That'll effectively halt the flow.

-Jamie

|||

Thats a great idea.

Thanks!

for loop expression

I'm trying to run a data flow task inside of a for loop container.

First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.

So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.

I've set the following:
AssignExpression : @.counter = @.counter + @.inc
EvalExpression : @.counter < @.max
InitExpression : @.counter = 1

To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.

What is the trick? Once I get this, then I can set these variables to other variables.

Thanks in advance.
-Lori

Are you sure @.inc is 2 in the scope of the for loop? Maybe check that it is not zero in the msg box.

Mark

|||I think I was doing something stupid like that when all the variables were ints.

Now I've moved to the next step, which is all the variables need to be strings. This is because both the @.max and the initial @.counter will be set based on values I pull from the database. When getting these values they have to come out as strings, otherwise the query fails.

@.max is going to be set to the max id of a table in mysql.
@.counter (initially) will be set to the max id of a table in sql server.
The goal is to pull all rows from the mysql table to the sql server table between @.counter and @.max. I'm doing this in a forloop so as to lessen the number of rows that the job tries to pull from mysql at any given time.

Once again, I've stepped back and am just trying to get the looping to work with strings.
Here are my expressions:
Currently the looping is not working:
Assign: @.counter =(DT_WSTR,50)((DT_U18)(@.counter) + (DT_U18)@.inc))
When running in debug mode, the counter is not incrementing. I've check that @.inc is not 0, by having it displayed to me in a msgbox.

Having to manipulate everything as strings to ints to strings again is extremely frustrating.

Thanks for help,
-Lori|||

I'm not sure I understand why the variables need to be strings in the for loop container. YOu get the values once, before the ForLoop begins, correct? Could you cast them at that time, or make integer copies for the ForLoop to simplify the expressions?

Thanks
Mark

|||Hmmm. That was fancy. Wrote this big long response on how to achieve this, and the forum says "can't post for unknown reason", and I lost the whole response. Let's try again.

I used to work with DTS. It was extremely straightforward to get max id from a table, set it to a parameter and then use that parameter in the pull command. SSIS is not so straightfoward. It took me about 3 full days to figure how to do what I just mentioned in SSIS. Then luckily I wrote a howto for myself and colleagues because I couldn't recall how to do it a month later. What I learned in those three days was that when getting the max id from a table, you have to convert it to a string because that's the only way to not have data loss when using max(id).

For others, here's the trick from mysql and sql server.
mysql (using ADO.net/odbc provider): select cast(max(id) as char) as max from <table>
sql server(using OLE DB provider): select convert(nvarchar(20), max(id)) as max from <table>
When setting the result set:
mysql: ResultSetName: 0, Variable name: <whatever>
sqlServer: ResultSetName: max, variable name: <whatever2>

Then to create the sql command, you had to click on DataFlow Task, Expressions, and set up an Expression for the sqlcommand. We had to do this, so that we could use the max string. If it's an integer, you don't have to do this.
"select * from <table> where id > " + @.[User::max]

So after finally figuring all that out, I now try to create a forloop. Since I kept fighting with integers vs. strings, I thought maybe it's just best to have every variable be a string. I don't think that this is that crazy of an assumption after all the **** I had to deal with.

But it turns out that setting @.counter as a string just doesn't work. Either the looping doesn't work or the loop won't end. Depends on lucky you get. I finally figured this out though.
Create your @.counter and @.inc variables with the Package as the scope and as Int64.

Then do the following for the forloop expressions:
InitExpression: @.counter = (DT_I8)@.mssql_max
EvalExpression: @.counter < (DT_I8) @.max
AssignExpression: @.counter = @.counter + @.inc

Then for the sqlcommand in the dataflow task, do the following:
"select * from <table> where id > " + (DT_WSTR, 50) @.counter + " and id <= " + (DT_WSTR, 50) ( @.counter+ @.inc).
Convert @.counter to a string, and (@.counter + @.inc) to a string. This is working very well.

I hope this helps someone else out as there didn't seem to be any examples of this out there.
Good luck to everyone in SSIS land.|||

After fighting the same problem for several hours I found this post that finally helped me on the right track...

However for if you like me use the mysql-odbc connector the index to the parameter seems to be starting on 1.

mysql: ResultSetName: 0, Variable name: <whatever>

Should then needs be changed to:

mysql: ResultSetName: 1, Variable name: <whatever>

/Albert

for loop expression

I'm trying to run a data flow task inside of a for loop container.

First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.

So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.

I've set the following:
AssignExpression : @.counter = @.counter + @.inc
EvalExpression : @.counter < @.max
InitExpression : @.counter = 1

To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.

What is the trick? Once I get this, then I can set these variables to other variables.

Thanks in advance.
-Lori

Are you sure @.inc is 2 in the scope of the for loop? Maybe check that it is not zero in the msg box.

Mark

|||I think I was doing something stupid like that when all the variables were ints.

Now I've moved to the next step, which is all the variables need to be strings. This is because both the @.max and the initial @.counter will be set based on values I pull from the database. When getting these values they have to come out as strings, otherwise the query fails.

@.max is going to be set to the max id of a table in mysql.
@.counter (initially) will be set to the max id of a table in sql server.
The goal is to pull all rows from the mysql table to the sql server table between @.counter and @.max. I'm doing this in a forloop so as to lessen the number of rows that the job tries to pull from mysql at any given time.

Once again, I've stepped back and am just trying to get the looping to work with strings.
Here are my expressions:
Currently the looping is not working:
Assign: @.counter =(DT_WSTR,50)((DT_U18)(@.counter) + (DT_U18)@.inc))
When running in debug mode, the counter is not incrementing. I've check that @.inc is not 0, by having it displayed to me in a msgbox.

Having to manipulate everything as strings to ints to strings again is extremely frustrating.

Thanks for help,
-Lori|||

I'm not sure I understand why the variables need to be strings in the for loop container. YOu get the values once, before the ForLoop begins, correct? Could you cast them at that time, or make integer copies for the ForLoop to simplify the expressions?

Thanks
Mark

|||Hmmm. That was fancy. Wrote this big long response on how to achieve this, and the forum says "can't post for unknown reason", and I lost the whole response. Let's try again.

I used to work with DTS. It was extremely straightforward to get max id from a table, set it to a parameter and then use that parameter in the pull command. SSIS is not so straightfoward. It took me about 3 full days to figure how to do what I just mentioned in SSIS. Then luckily I wrote a howto for myself and colleagues because I couldn't recall how to do it a month later. What I learned in those three days was that when getting the max id from a table, you have to convert it to a string because that's the only way to not have data loss when using max(id).

For others, here's the trick from mysql and sql server.
mysql (using ADO.net/odbc provider): select cast(max(id) as char) as max from <table>
sql server(using OLE DB provider): select convert(nvarchar(20), max(id)) as max from <table>
When setting the result set:
mysql: ResultSetName: 0, Variable name: <whatever>
sqlServer: ResultSetName: max, variable name: <whatever2>

Then to create the sql command, you had to click on DataFlow Task, Expressions, and set up an Expression for the sqlcommand. We had to do this, so that we could use the max string. If it's an integer, you don't have to do this.
"select * from <table> where id > " + @.[User::max]

So after finally figuring all that out, I now try to create a forloop. Since I kept fighting with integers vs. strings, I thought maybe it's just best to have every variable be a string. I don't think that this is that crazy of an assumption after all the **** I had to deal with.

But it turns out that setting @.counter as a string just doesn't work. Either the looping doesn't work or the loop won't end. Depends on lucky you get. I finally figured this out though.
Create your @.counter and @.inc variables with the Package as the scope and as Int64.

Then do the following for the forloop expressions:
InitExpression: @.counter = (DT_I8)@.mssql_max
EvalExpression: @.counter < (DT_I8) @.max
AssignExpression: @.counter = @.counter + @.inc

Then for the sqlcommand in the dataflow task, do the following:
"select * from <table> where id > " + (DT_WSTR, 50) @.counter + " and id <= " + (DT_WSTR, 50) ( @.counter+ @.inc).
Convert @.counter to a string, and (@.counter + @.inc) to a string. This is working very well.

I hope this helps someone else out as there didn't seem to be any examples of this out there.
Good luck to everyone in SSIS land.|||

After fighting the same problem for several hours I found this post that finally helped me on the right track...

However for if you like me use the mysql-odbc connector the index to the parameter seems to be starting on 1.

mysql: ResultSetName: 0, Variable name: <whatever>

Should then needs be changed to:

mysql: ResultSetName: 1, Variable name: <whatever>

/Albert

For each loop

Hi,

I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?

I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?

Please help me with the above problem as I am working on a demo.

Thank you!!

Here's how you do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx and there is a downloadable demo on there as well

The only difference is that in the example given here the recordset object variable is populated with an Execute SQL Task rather than the Recordset destination but thereafter the same applies.

You cannot use the DataReader destination by the way!

-Jamie