March 29, 2016 at 2:11 am
Hi,
One of the servers I connect to is periodically taken down and when brought back up has a different name. The new name is only one of two names used, however my connection is no longer applicable and the job fails.
Is there any way to dynamically connect to a server based on which of the two servers is currently 'up'?
Thanks in advance,
Rich
March 29, 2016 at 12:13 pm
All I can say is "Wow!".
The only thing I can think of to do within SSIS is to do something like an Execute SQL Task as the first step that does something Like SELECT @@VERSION and if it fails have a Expression Task (2012+) or Script Task (pre-2012) that resets the variable that sets the connection string to the correct server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2016 at 3:42 am
Hi Jack - thanks for your response. Just after posting this I did a little further digging ("googling" to give the technical term) and found a property on the SSIS connection string called Failover. I merely had to enter the alternative server name in there. Tested it and it works a treat!
Cheers,
Rich
March 30, 2016 at 5:56 am
Interesting. I believe that is there for mirroring & availability groups but if it works for this situation even better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply