Foreachloop continue inspite of server connectivity issues in SSIS package.

Question

Foreachloop continue inspite of server connectivity issues in SSIS package.

mtz676

SSCrazy Eights

Points: 9212
More actions
March 15, 2016 at 4:06 am

#399863

How do I build an SSIS package which will have a dataflow task within a foreachloop container.
The dataflow task has source and destination ole db's.
At the beginning outside of the foreaachloop container will be a execute sql task which will list the servernames.
If my server is not online or cannot connect for any reason my foreachloop shouls ignore it ans should go to the next available server.
How do I make this happen.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

sqlnewbie17 (3/15/2016)
How do I build an SSIS package which will have a dataflow task within a foreachloop container.
The dataflow task has source and destination ole db's.
At the beginning outside of the foreaachloop container will be a execute sql task which will list the servernames.
If my server is not online or cannot connect for any reason my foreachloop shouls ignore it ans should go to the next available server.
How do I make this happen.
Thanks

You want to know the details of how to build the entire package?

Or just how to configure a foreach loop container?

Or how to test a connection?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

I have already built the package.It works fine as long as the servers exists.

Lets say a server is not reachable for any reason.My foreachloop should skip that server and go to the next server.

And also I should maintain the list of servers not reachable in a table. So each time a server is not reacheable it should be stored in a SQL table and then the foreach loop continues to the next server.

This is my requirement.

(If it be done I would also like to know why the server was not reacheable in an additional column-Servernotreachable,reasonnotreacheable(2 col's in the table))

Thanks

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

sqlnewbie17 (3/15/2016)
I have already built the package.It works fine as long as the servers exists.
Lets say a server is not reachable for any reason.My foreachloop should skip that server and go to the next server.
And also I should maintain the list of servers not reachable in a table. So each time a server is not reacheable it should be stored in a SQL table and then the foreach loop continues to the next server.
This is my requirement.
(If it be done I would also like to know why the server was not reacheable in an additional column-Servernotreachable,reasonnotreacheable(2 col's in the table))
Thanks

Trying to find out why a server is unreachable is somewhat similar to determining why the taxi you are waiting for is late: unless you can somehow contact the taxi to ask why, you'll never know.

So, in your FEL, create a script task as the first item.

Set a package-scoped variable in the script task to yes/no, depending on whether the server can be contacted.

Add precedence constraints after the script task which depend on the value of the variable you just set:

If the contact was successful, process as usual

If the contact failed, run an ExecuteSQL task to update your ServerStatus table

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin