April 1, 2016 at 12:26 am
I have a list of 150 sqlservers.I need to build an SSIS package which will test connectivity
to the servers and if the connection is good the servername is inserted into a sql table and
if the connection fails then the servername is inserted into another sql table and eventually
I use the good conn table as part of foreach loop.
How do I build such a package.
Can this be done without a Script task.
As of now my package keeps failing due to connectivity issues.
If I build something like I mentioned I can use the good server connection table as source to my foreach loop
container and the package would run good.
Thanks
April 1, 2016 at 8:54 pm
sqlnewbie17 (4/1/2016)
I have a list of 150 sqlservers.I need to build an SSIS package which will test connectivityto the servers and if the connection is good the servername is inserted into a sql table and
if the connection fails then the servername is inserted into another sql table and eventually
I use the good conn table as part of foreach loop.
How do I build such a package.
Can this be done without a Script task.
As of now my package keeps failing due to connectivity issues.
If I build something like I mentioned I can use the good server connection table as source to my foreach loop
container and the package would run good.
Thanks
I'm thinking that SSIS is the wrong tool for this. Since you have a list of servers, I'm thinking that a WMIC batch file maybe even PowerShell would be the way to do this. If you're clever, you might even be able to call it directly from SQL Server and have the results dump directly to a table.
That, notwithstanding, this reply should suffice as a "bump" for your post because I, too, am interested in seeing how an SSIS Ninja might tackle this task.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2016 at 9:51 am
As Jeff said, SSIS might not be the best tool for this but you can achieve this using SSIS and without a script task.
1. Create a new SSIS Package
2. Create a connection manager for the SQL db where you will record the results
3. Create a package variable e.g. varServerName.
3. Create a For Each Loop container. Use the list of 150 server as the enumerator (depending on where you have this it might be an object variable or similar). Set the varServerName variable to map to the column containing the server name
4. Create a Target db connection and set the ServerName as an Expression to be varServerName - this will change the DB connection for each iteration of the loop
5. Within the For Each Loop container create a simple Execute SQL Task using the Target server db connection e.g. SELECT 1. This will test whether the server name is valid.
6. Within the For Each Loop container, create two Execute SQL tasks - one which will log success, one which will log failure.
7. Connect the first Exec SQL to the Log Success SQL task with a Success constraint
8. Connect the first Exec SQL to the Log Fail SQL task with Failure constraint
9. Change the MaximumErrorCount in the For Each Loop and in the first Exec SQL task to a large number (at least as big as the number of servers). This will allow the package to continue even if it cannot connect to a server.
The downside to this is that it can take some time for an invalid connection to timeout so if you have a lot of invalid server names then this wouldn't be a quick package to run.
Jez
April 4, 2016 at 4:24 pm
I'm not an SSIS user but I think even I could make my way through those good instructions. Thanks for taking the time, Jez.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2016 at 5:19 pm
Jez-448386 (4/4/2016)
As Jeff said, SSIS might not be the best tool for this but you can achieve this using SSIS and without a script task.1. Create a new SSIS Package
2. Create a connection manager for the SQL db where you will record the results
3. Create a package variable e.g. varServerName.
3. Create a For Each Loop container. Use the list of 150 server as the enumerator (depending on where you have this it might be an object variable or similar). Set the varServerName variable to map to the column containing the server name
4. Create a Target db connection and set the ServerName as an Expression to be varServerName - this will change the DB connection for each iteration of the loop
5. Within the For Each Loop container create a simple Execute SQL Task using the Target server db connection e.g. SELECT 1. This will test whether the server name is valid.
6. Within the For Each Loop container, create two Execute SQL tasks - one which will log success, one which will log failure.
7. Connect the first Exec SQL to the Log Success SQL task with a Success constraint
8. Connect the first Exec SQL to the Log Fail SQL task with Failure constraint
9. Change the MaximumErrorCount in the For Each Loop and in the first Exec SQL task to a large number (at least as big as the number of servers). This will allow the package to continue even if it cannot connect to a server.
The downside to this is that it can take some time for an invalid connection to timeout so if you have a lot of invalid server names then this wouldn't be a quick package to run.
Jez
That's a great start Jez, but there are a couple of things that I'd change/add/do different.
>> Instead of populating a list in SSIS with the 150 servers, I'd create a table in the database. I'd have the table include a column for the SQL Instance name and an IsActive flag (so you can turn them on/off for the package). Those are a minimum. I'd also consider adding some tracking columns here instead of logging the success/failures in different tables. I know this came from the OP, but the idea of putting the connection statuses into two different tables, then looping through the success table is redundant. The package is already looping through each server, why not do the work all in the same for each container? If you can connect, continue on with the work. If you can't connect, consider setting the IsActive flag to false. Or, reserve IsActive for manually setting the status for the package to pick up, but include some other connection columns into the table. Examples could be ConnectionStatus, LastConnectAttemptDate, etc.
You're package would then need an Execute SQL Task to get the server names and load them into an object variable before the For Each container. The For Each container would be changed to enumerate the object variable instead of the list. Inside the loop, you can still try the SELECT 1 method. Failure goes one direction to your failure action and success goes to whatever else you'd do on each server.
>>Next, I would not recommend handling the connection errors by changing the MaximumErrorCount count. I would instead leave this at it's default. I would create an On-Error event handler for the test connect step. You don't need any components added to this event handler (unless you want to put your failure tasks here instead of in the control flow). You need to set the Propagate variable here to False. This prevents the connection error from bubbling up to the for each loop and causing it to fail. It will instead execute anything you have in the handler (or control flow on failure) and then move on with the next loop iteration. Pretty slick way to handle errors in a For Each container.
>> Lastly, the connection manager for the server has a timeout setting. If you open the connection manager and go to the 'All' tab, you'll see the Connect Timeout setting under the Initialization section. Here, you can change the connection timeout to something small so that your package does not take forever if there are a high percentage of connection failures. Set it to 2 seconds or whatever you think is appropriate for your network. 1 or 2 would be my suggestion, but I could see setting this higher if you encounter network latency from time to time. You could always parameterize this for ease of changing it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply