Data Flow task within For Each Loop not executing

All:

I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

desibull wrote:

All:

I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

If the dataflow uses variables that are passed to it via the ForEach loop, how does the dataflow work when it is moved out of the ForEach loop? Do the variables have package scope?

Regards

|||

Yes, the variables have package scope.

It is really odd. The control does not seem to be flowing to the data flow. It jsut sits there doing nothing while the loop finishes executing.

|||

desibull wrote:

Yes, the variables have package scope.

It is really odd. The control does not seem to be flowing to the data flow. It jsut sits there doing nothing while the loop finishes executing.

Strange. Try dragging another executable (I suggest an empty sequence container) into the ForEach loop and see if it executes.

-Jamie

|||

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

|||

desibull wrote:

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

Strange. They should still change colour. Can I suggest you raise this at connect.microsoft.com with a repro?

-Jamie

|||Still being a newbie to this forum could you explain what repro means and what is expected to be posted to connect? Is it a visual display of the flow?|||

desibull wrote:

Still being a newbie to this forum could you explain what repro means and what is expected to be posted to connect? Is it a visual display of the flow?

Sorry. "repro" means reproduction. i.e. Something that demonstrates teh problem and can be executed by Microsoft.

Connect is a place for posting what you think is a bug. You should post anything that helps explain the problem. Words, pictures, repro, whatever.

-Jamie

|||

Jamie Thomson wrote:

desibull wrote:

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

Strange. They should still change colour. Can I suggest you raise this at connect.microsoft.com with a repro?

-Jamie

I have a different viewpoint on this. Since the data flow task is never executing (there are no records, so the For Each loop isn't execute anything inside it), it shouldn't show green (or yellow, or red).

|||

jwelch wrote:

I have a different viewpoint on this. Since the data flow task is never executing (there are no records, so the For Each loop isn't execute anything inside it), it shouldn't show green (or yellow, or red).

John,

The dataflow would still have to execute in order for it to KNOW that there were no records. The same is true of the source adapters. Hence, if a source adapter is executing then it must create at least one buffer - even if the buffer is empty. If a buffer is created then I would expect the other components to process that buffer.

That's how I figure it in my head anyway.

-Jamie

|||

I agree, if the data flow is actually being run. If I am interpreting the OP's comments correctly, though, the Data Flow isn't executing because there were no rows retrieved in the ADO resultset that is driving the For Each loop, . If there are rows in the resultset that is used by the For Each, then the data flow should be executing and it should show green. If, however, there are no rows in the For Each resultset, it shouldn't be executing the data flow at all, so I would expect it to remain white.

|||

jwelch wrote:

I agree, if the data flow is actually being run. If I am interpreting the OP's comments correctly, though, the Data Flow isn't executing because there were no rows retrieved in the ADO resultset that is driving the For Each loop, . If there are rows in the resultset that is used by the For Each, then the data flow should be executing and it should show green. If, however, there are no rows in the For Each resultset, it shouldn't be executing the data flow at all, so I would expect it to remain white.

Ah OK. I'll guess we'll have to see if he replies to clarify

|||

John is right on the money. I observed that the data flow within the ForEach loop does not execute when the ADO Recordset driving the loop is empty.

But I am still unable to understand why the data flow will not execute at least once because the loop executes once using the default values of the variables. Now, using the default values the data flow does not pull any records but it should at least execute, right?

Anyways, my observation confirms John's viewpoint.

Thank you!

|||

There is no concept of default values. Yes, there are values stored there at design-time but they won't get used if you using another method to set them at execution-time.

-Jamie