SSIS For Each Container Failure, With no Error on Progress Tab

Question

SSIS For Each Container Failure, With no Error on Progress Tab

zlthomps

Default port

Points: 1480
More actions
July 28, 2011 at 8:11 am

#255007

I have an SSIS package where an execute SQL task is populating an object variable (ADO Enum) with a result set variable. Then I am looping through those values. When I run one data flow task at a time everything works, but when I try to run more than 1 data flow task concurrently the for each loop container is failing, but there is no error listed on the progress tab. Below are screen shots of the setup, any ideas?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Nice job posting your question 🙂

The only thing I can come up with for this, and it is a guess, is that there is only one copy of the ADO Recordset with n-FEL containers trying to access it at the same time from different threads...and that's going to be a problem.

To test the theory connect two enabled FEL containers and have them run sequentially to see if they complete.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

zlthomps Default port Points: 1480 More actions · Answer 2

opc.three (7/28/2011)
Nice job posting your question 🙂
The only thing I can come up with for this, and it is a guess, is that there is only one copy of the ADO Recordset with n-FEL containers trying to access it at the same time from different threads...and that's going to be a problem.
To test the theory connect two enabled FEL containers and have them run sequentially to see if they complete.

I tried and still no good 🙁 It's really weird that the container itself would turn red, but the DFT inside the n-FEL container would turn green and show success. I wonder what would happen if I created another Execute SQL Task, populating the exact same record set object variable as the one I already have (just named differently), and see if it works then.

zlthomps Default port Points: 1480 More actions · Answer 3

zlthomps (7/28/2011)
opc.three (7/28/2011)
Nice job posting your question 🙂
The only thing I can come up with for this, and it is a guess, is that there is only one copy of the ADO Recordset with n-FEL containers trying to access it at the same time from different threads...and that's going to be a problem.
To test the theory connect two enabled FEL containers and have them run sequentially to see if they complete.
I tried and still no good 🙁 It's really weird that the container itself would turn red, but the DFT inside the n-FEL container would turn green and show success. I wonder what would happen if I created another Execute SQL Task, populating the exact same record set object variable as the one I already have (just named differently), and see if it works then.

A picture is worth 1000 words right? I guess I just wrote a 6,000 word essay question 😛

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

zlthomps (7/28/2011)
opc.three (7/28/2011)
Nice job posting your question 🙂
The only thing I can come up with for this, and it is a guess, is that there is only one copy of the ADO Recordset with n-FEL containers trying to access it at the same time from different threads...and that's going to be a problem.
To test the theory connect two enabled FEL containers and have them run sequentially to see if they complete.
I tried and still no good 🙁 It's really weird that the container itself would turn red, but the DFT inside the n-FEL container would turn green and show success.

That sounds like a problem with your FEL config but from your pics it looks fine :ermm:

I wonder what would happen if I created another Execute SQL Task, populating the exact same record set object variable as the one I already have (just named differently), and see if it works then.

It's worth a try, if for nothing else to test the theory of separating that out as a possible failure point. You could do that with a single Execute SQL Task leveraging multiple-result sets. That said, beyond debugging, you really should not need to do that. I would save that as a last resort in terms of using it as a way forward.

What else is showing in the output window?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

zlthomps Default port Points: 1480 More actions · Answer 5

Here is a shot of the failure, again without an error on the progress tab...

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

What if you connect the first FELC with the second one using a precedence constraint? Does it work then?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

zlthomps Default port Points: 1480 More actions · Answer 7

Koen Verbeeck (7/29/2011)
What if you connect the first FELC with the second one using a precedence constraint? Does it work then?

Yes, it does - But I would ideally be able to to run the tasks in parallel. As far as I can tell, nothing is the second DFT that depends on the first.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

zlthomps (7/29/2011)
Koen Verbeeck (7/29/2011)
What if you connect the first FELC with the second one using a precedence constraint? Does it work then?
Yes, it does - But I would ideally be able to to run the tasks in parallel. As far as I can tell, nothing is the second DFT that depends on the first.

This behavior supports my earlier theory (not saying that I nailed it, but it might be the reason).

You might try going down the road of making more than one Recordset object available as previously mentioned and have each FEL use its own.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

zlthomps Default port Points: 1480 More actions · Answer 9

opc.three (7/29/2011)
zlthomps (7/29/2011)
Koen Verbeeck (7/29/2011)
What if you connect the first FELC with the second one using a precedence constraint? Does it work then?
Yes, it does - But I would ideally be able to to run the tasks in parallel. As far as I can tell, nothing is the second DFT that depends on the first.
This behavior supports my earlier theory (not saying that I nailed it, but it might be the reason).
You might try going down the road of making more than one Recordset object available as previously mentioned and have each FEL use its own.

Trying to do what your suggestion is with one Ex SQL Task by creating a new object var. On the Result Set tab of the Ex SQL Task Im using a result name of 1, and am getting the following error...

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

Any ideas? I found a few posts where users have gotten this error and ended up mapping to an ADO object. I wonder if that would be the solution. Seems like its populating the first variable with the result set, but it doesnt know what to do with the second.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Sorry if I missed it, but are we on SSIS 2005 or 2008?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

My fault on the Execute SQL Task handling multiple resultsets. I thought that I had done that before, but I ended up using UNION ALL. You'll need separate Execute SQL Tasks each mapping into a different variable, or you'll have to use a Script Task.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato