Multiple Data Flows

Question

Post reply

Multiple Data Flows

Colin Betteley

Hall of Fame

Points: 3095
More actions
June 1, 2010 at 6:58 am

#140529

Hi,
I am running an SSIS package to get the results of Back Up jobs from the msdb Database on multiple servers. Each query to a servers msdb is contained in its own data flow task. These tasks are not linked in any way.
I am finding that if one query fails, say a time out to a particular server, that ALL data flow tasks fail. As the tasks are not linked I would noot have expected this sort of failure. How can I ensure that those tasks without an issue always return data, which is inserted into a database for subsequent query and Email advice?
Thanks
Colin
June 1, 2010 at 8:12 am

This was removed by the editor as SPAM
June 1, 2010 at 11:09 pm

This was removed by the editor as SPAM
June 2, 2010 at 4:07 am

This was removed by the editor as SPAM
June 2, 2010 at 4:16 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 1

Colin Betteley

Hall of Fame

Points: 3095

June 1, 2010 at 8:19 am

#1174786

Thanks, however values are already set to false.

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 2

I believe that is what I have.

Each Task is speparate having its own SELECT clause and own connection manager to each server. The only connection between the tasks is that they are in the same SSIS package and insert into the same SQL table. They should run independently but some how seem to be connected. I may have to resort to creating a sinle SSIS for each server, something I did not want to do.

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

Colin Betteley (6/2/2010)
I believe that is what I have.
Each Task is speparate having its own SELECT clause and own connection manager to each server. The only connection between the tasks is that they are in the same SSIS package and insert into the same SQL table. They should run independently but some how seem to be connected. I may have to resort to creating a sinle SSIS for each server, something I did not want to do.

You say they should run independently, but you also say they write to the same table. Unless you deactivated all locking, they will interfere with each other as one data flow will place a lock on the table to write data. All the other data flows will have to wait then.

You could try to run them sequentially, connected with each other with the completion precedence constraint (blue arrow). Or, as stewartc suggested, create a single package that you configure differently for each server.

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

First, what are the errors return out of the other items when one of them fails?

Second, the method described where a package is executed repeatedly is very likely ultra inefficient, the reason is that there is overhead to start the package at each run, it is easy to do but generally inefficient. A better method would be to use a ForEach Loop that works through a list of servers.

CEWII