For loop for I reachable sql servers

Question

For loop for I reachable sql servers

mtz676

SSCrazy Eights

Points: 9261
More actions
October 8, 2018 at 4:48 am

#415790

I am able to loop through a list of sql servers and capture information but how do I tackle a sql server when it is not reachable.
My scenario : sql table with an initial list of sql servers ; My for loop goes through the sql table and dumps servers which are reachable in successservers table and the servers which are not reachable in a failureservers table. How do I do this in SSIS.
Thanks

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

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

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

mtz676 - Monday, October 8, 2018 4:48 AM
I am able to loop through a list of sql servers and capture information but how do I tackle a sql server when it is not reachable.My scenario : sql table with an initial list of sql servers ; My for loop goes through the sql table and dumps servers which are reachable in successservers table and the servers which are not reachable in a failureservers table. How do I do this in SSIS.Thanks

Are you asking about the how to do the entire process, or how to check whether a server is 'reachable'?

mtz676 SSCrazy Eights Points: 9261 More actions · Answer 2

As I said if the servers are reachable then my ssis package loops through the sql table captures relevant data. but once a server is not reachable then my ssis package fails . So in such a case I would like to save the unreachable servers in a failureservers table and reachable servers in a successservers table. I wouldn’t mind knowing the entire process.

Thanks

mtz676 SSCrazy Eights Points: 9261 More actions · Answer 3

As I said if the servers are reachable then my ssis package loops through the sql table captures relevant data. but once a server is not reachable then my ssis package fails . So in such a case I would like to save the unreachable servers in a failureservers table and reachable servers in a successservers table. Then my ssis package should loop through the successservers sql table and capture relevant information.I wouldn’t mind knowing the entire process.

mtz676 SSCrazy Eights Points: 9261 More actions · Answer 4

As I said if the servers are reachable then my ssis package loops through the sql table captures relevant data. but once a server is not reachable then my ssis package fails . So in such a case I would like to save the unreachable servers in a failureservers table and reachable servers in a successservers table. Then my ssis package should loop through the successservers sql table and capture relevant information.I wouldn’t mind knowing the entire process.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

If I quote from your original post:

My for loop goes through the sql table and dumps servers which are reachable in successservers table

This implies that you already have a for loop that does the job, hence my question.

One solution is to add a script task within your loop which pings the server and sets a package-scoped variable depending on the result of the ping.
\Subsequent processing would follow two different paths, depending on the value of this variable, though the use of appropriate 'Expression and Constraint' precedence constraints.

mtz676 SSCrazy Eights Points: 9261 More actions · Answer 6

Can this be achieved without using script task (without coding) Please advise

mtz676 SSCrazy Eights Points: 9261 More actions · Answer 7

My Initial table list of servers

My reachable list of servers in successservers table

My not reachable list of servers in failureservers table

Then I use my select against successservers table and collect relevant data by looping through the table

Is this possible to dump the servers list into 2 separate tables as above without using the script task ?

frederico_fonseca SSCoach Points: 16251 More actions · Answer 8

without some type of coding it will be complex and cumbersome - and code is easy, straightforward and plenty of samples on the net. One such example https://stackoverflow.com/questions/6589136/how-to-ping-from-within-an-ssis-package - you would need to change the task results to populate a variable instead.

but if you don't wish to do code you can do
-- command line task - issue a ping to the server redirecting output to a file A
-- create dataflow
     -- read file A
   - do a conditional split
   -- if record matches "request timeout" redirect to fail
   -- if record matches "reply from ... ttl" redirect to success
   -- other redirect to rowcount

other ways of doing it but all require multiple steps - for example loading the ping output to a sql server table and then use t-sql to process its output.
Not something a professional would do just to avoid some tiny coding in a script task.