SSIS Package Pings Servers

In many environments, DBAs and SQL Server administrators need to keep a close eye on server connectivity. That's the case where I work. We try to minimize LAN downtime as much as possible so that our extraction, transformation, and loading (ETL) processes run undisturbed.

Related: Troubleshooting Common SQL Server Performance Problems

Although regularly monitoring server connectivity is important, it can be time-consuming. For this reason, I developed a flexible and easy-to-implement SQL Server Integration Services (SSIS) package that uses the Ping command to check the connectivity of each host in my company's IP network. The SSIS package not only finds servers that are unresponsive but also flags servers that aren't responding to pings as often as we'd like, which can help identify potential problems.

The SSIS package executes three stored procedures to ping each host and retrieve the output. I'll show you how to create this package. But first you need to create the stored procedures and the database and tables they use.

Creating the Database, Tables, and Stored Procedures

The first step is to create the database, two tables, and three stored procedures. The database, which is named PingNotification, will hold two tables: ServerList and PingOutput.

The ServerList table will contain a list of the hosts that the SSIS package needs to ping during each run. It has the following columns:

The PingOutput table will store the output from each ping. It has the following columns:

I wrote the script PingNotification_CREATE_DATABASE_PROCEDURE.sql to demonstrate how to create the database and tables. This script also populates the tables with sample data in case you want to follow along with the example I'm presenting.

Besides creating the database and tables, the script creates three T-SQL stored procedures:

Note that PingListOfServers uses the xp_cmdshell system stored procedure to execute the Ping commands. So, prior to executing PingListOfServers, xp_cmdshell must be enabled on local host. You can enable it by running the command

EXEC sp_configure 'xp_cmdshell', 1;GORECONFIGURE;GO 

When xp_cmdshell is enabled, the PingListOfServers stored procedure uses the Ping command to send echo requests to the servers, as the code in Listing 1 shows.

SET @sql = 'EXECUTE XP_CMDSHELL ''ping '  + LTRIM(RTRIM(@ServerName)) + ' -n '  + CAST(@NofPings AS VARCHAR(10))+ ''''CREATE TABLE #pingtemp  (  ID INT IDENTITY(1,1)  ,line VARCHAR(2000)  )INSERT INTO #pingtemp(line)EXECUTE SP_EXECUTESQL @sql

PingListOfServers stores the results in a temporary table, which it creates with an identity column for easier tracking of rows. Figure 1 shows sample Ping command output with the row (i.e., line) numbers added.

Figure 1: Sample Ping Command Output with Line Numbers Added 

In the output of each Ping command, the needed result always starts with line 4. When line 4 starts with Reply from, the echo request is successful. When line 4 starts with Request timed out, the echo request failed.

Because the output is set up this way, PingListOfServers uses the code in Listing 2 to capture the information it needs to determine the @ActualAvailability value. This value is important because it's used to determine the values for the PingAvailability and PingStatus columns in the PingOutput table.

@ActualAvailability = SUM(x.Val_)/@NofPings*100  FROM  (    SELECT    id    ,line    ,CASE WHEN line LIKE 'Reply%' THEN 1 ELSE 0 END AS Val_    FROM #pingTemp    WHERE    -- BEGIN CALLOUT A  id > 3  AND id < @NofPings+3    -- END CALLOUT A    -- BEGIN CALLOUT B  AND EXISTS (SELECT * FROM #pingtemp    WHERE id = 1 AND (line IS NULL OR LEN(LINE) = 0))    -- END CALLOUT B  ) AS X

The @ActualAvailability value is a percentage indicating how many pings received replies compared with how many were sent out. The number of pings sent (@NofPings) is already known. It's the value in the NumberOfPings column in the ServerList table. So, you just need to know how many replies were received. You can find out by searching the output in the temporary table for the text Reply. If a line (i.e., row) has the word Reply, it's counted as 1. Otherwise, it's 0. However, before you do this, you need to filter the output.

The code at callout A selects only those lines that need to be checked to see whether they contain the word Reply. The statement id > 3 is used to ignore the first three lines because they have nothing to do with the replies. The statement AND id < @NofPings+3 is used to ignore the lines after the replies. In this statement, you need to add 3 to the number in @NofPings because the first three lines are ignored. So, for example, if the number of pings requested is 3, the code at callout A will select lines 4 through 6.

The code at callout B filters out any output indicating that a host didn't exist. When a host doesn't exist, the Ping command returns the message Ping request could not find host in the first line. When a hosts exists, the first line can be null or an empty string, so the code

WHERE id = 1 AND (line IS NULL OR LEN(LINE) = 0)) 

is used.

If you'd like to see how the rest of PingListOfServers (or the other two stored procedures) works, check out the comments in PingNotification_CREATE_DATABASE_PROCEDURE.sql. You can download this script (and the SSIS package) by clicking the Download button. I've tested the script on several different Windows environments, including Windows Server 2008 R2, Windows Server 2008, Windows Server 2003 R2, Windows 7, Windows XP, and Windows 2000. For this article, I used Windows 7 and SQL Server 2008 R2. Both are set to English as the native language. If your systems are set to a different language, slight changes need to be made to the PingListOfServers stored procedure. For more information, see the ChangingLanguageSettings.txt file that accompanies the script.

Creating the SSIS Package

As Figure 2 shows, the SSIS package contains four tasks:

 
Figure 2: SSIS packages Four Tasks

Run Stored Procedure PingListOfServers. This Execute SQL task executes PingListOfServers, which populates the PingOutput table with ping results for each host.

Check for Not Pinging Servers. This Execute SQL task executes the ResultOfPingFailures stored procedure, which determines how many servers didn't exist and how many servers had a lower number of successful echoes than requested. When creating this task, you need to create a variable named User::Failed_Yes and initialize it to zero, as shown in Figure 3. The stored procedure's result is passed to this variable, which is checked by the precedence constraint that's connecting second and third tasks. This constraint contains the simple expression @Failed_Yes >= 1. When the User::Failed_Yes variable equals 0, the SSIS package will stop executing (indicating success). When the variable is greater than 0, the flow continues to the next task.

 
Figure 3: Creation of the User::Failed_Yes Variable

Run Stored Procedure ResultOfPingForEmail. This Execute SQL task executes the ResultOfPingForEmail stored procedure, which constructs the email message about the ping results. When creating this task, you need to create a variable named User::Failed_Message, as shown in Figure 4. This variable will contain the constructed message.

Figure 4: Creation of the User::Failed_Message Variable 

Send Mail on PingFailures. This Send Mail task sends the email message. As Figure 5 shows, you need to specify Variable for the MessageSourceType property and User::Failed_Message for the MessageSource property. You also need to specify the SMTP connection, sender, recipients, and Subject line.

Figure 5: Configuration of the Send Mail Task 

Using the SSIS Package

As you've seen, you can use the Ping command in a standalone SSIS package to notify you when there's a problem with connections between computers. For example, I've scheduled a job to run this package every minute to check the LAN connection between my local host and a remote server. If I get a notification email, I can act immediately. In case it happens during non-working hours, we have an SMS notification system that sends an SMS notification to our DBA for immediate action. This simple SSIS package can also be used to check connections in failover scenarios, backup processes, ETL processes, and more.

Learn more: Using an SSIS Script Component to Import Data to a Wide Table