SQL Overview SSIS Package I - Basic Package

Wouldn't it be nice to find out about all of your SQL Server instances

from a single instance? This is something I have been thinking about doing

for a few years. I could setup an instance with linked servers to all the

other instances. But this does not seem too secure to me plus linked

servers have been troublesome for me at times. I finally found what I was

looking in the May 2007 issue of SQL Server Magazine. It was the

article SQL Server Integration Services by Rodney Landrum. Using a

table loaded with server/instance names, the SSIS package would connect to

each instance, retrieve information from each of them, and then store it in

a database on the calling instance.

Using this package I can get an up-to-date overview of all the SQL Servers

Instances at once. To decipher all this collected information, I would

execute a series of reports that check for more than a dozen potential

problems. A report is e-mailed to me when a potential problem is detected.

How cool is that? Ok, I am getting a little DBA geeky now. This has been a

tremendous time saver and great at proactively preventing problems. Servers

that I barely paid attention to are being checked for problems on a daily

basis.

SQL Server Magazine provides a free download of the package from Rodney's article. You can, off

course, customize that package to your heart's content like I did. When I

needed a package to execute a single function, I decided to create my own

package instead of deleting a bunch of stuff and hoping it works. Besides I

really wanted to know how this package worked.

This article provides instructions for creating a simple SSIS package that

retrieves the database status from multiple servers. Knowing how this

package works will empower you to create your own versions. Some of these

instructions are very detailed and will bore those very familiar with

SSIS. I am sorry for that but I wanted a level of detail to allow

those still somewhat new to SSIS to be able to follow along. Entered or

selected values will be blue

except for the SQL which has been "prettified" using the Simple-talk

Prettifier website.

For building this package I used SQL Server Business Intelligence

Development Studio for SQL Server 2005 x64 SP2 with hot fix KB934459.

Setup

Create the Database SQL_Overview

Create and populate the SSIS_ServerList table

This table contains a list of instances that this package will access. If

the server has a named instance, append the instance name to the server

name using '\' to separate them.

The server name can be just the server name or the full domain name. Full

domain names are useful for speeding up DNS resolution for servers in other

domains. I use full domain names to help identify which data center the

server is in. Yes, this script works across data centers just as long as

the windows id running the script has the appropriate authority. The

"Usage" column is optional and can be used to distinguish between

production and development servers. The Skip_SQL_Overview column flags the

row to be skipped by the SSIS package.

To load the names of server\instances, this script can be used.

It requires XP_CMDSHELL to be enabled. As an alternative, use this script to create a list of servers, save it to a spread sheet,

and then import it into the table.

Create the Database_Status table

This table will contain the databases and their status for all the

instances in the SSIS_ServerList table.

Create the SSIS Package

Start SQL Server Business Intelligence Development Studio

Click File> New > Project

Use these settings

Rename Package.dtsx to SSIS_MultiServers_Package.dtsx. Answer yes

to "Do you want to rename the package object as well?"

Define Variables

Right click on the empty panel for the Control Flow Tab. Select

Variables. Then select the Add Variables icon and add the following two

variables.

Close the variable panel.

Create Connections

Connection Manager is used to establish connections for this package.

Here are the step by step instructions for creating these connections.

QASRV.SQL_Overview - points to the

database containing the SQL_Overview Database

MultiServer - this connection is used to connect to the different

instances. A variable is passed to it containing the server and instance

name from the SSIS_ServerList table created earlier.

Create Tasks

Truncate Tables

• Using the Toolbox add the "Sequence Container" object. Name

  it Truncate Tables. This object will contain all the truncate

  table tasks for the package.

• Add "Execute SQL Task" object to this container
• Settings - Double Click on Icon
• • Name: Truncate Database Status
  • Connection: to QASRV.SQL_Overview
  • SQL Statement: TRUNCATE Table

    Database_Status

  • BypassPrepare: False

Populate ADO Variable Task

• Add "Execute SQL Task" below the "Sequence Container" Truncate Table
• Connect the "Truncate Tables Container" to this object with the green

  line/arrow

• Settings - Double Click on Icon
• • General
  • • Name: Populate ADO Variable
    • ResultSet: Full result set
    • Connection: QASRV.SQL_Overview
    • SQL Statement:
    • • SELECT RTRIM(Server) AS servername
FROM SSIS_ServerList
WHERE (Skip_SQL_Overview is null or Skip_SQL_Overview = 'FALSE')
ORDER BY 1
        
    • BypassPrepare: False
  • Result Set - its on the left side of the panel
  • • Click Add
    • Change Result Name to 0
    • Click OK

Collect Database Status

Container

1. Add "Foreach Loop Container" below the "Populate ADO Variable" task
2. 1. Connect the "Populate ADO Variable" task to this item with the

      green line/arrow

   2. Settings
   3. 1. General
      1. 1. Name: Collect Database

            Status

      2. Collection
      3. 1. Change Enumerator to Foreach

            ADO enumerator

         2. Select ADO object source variable User::SQL_RS
      4. Variable Mapping
      5. 1. Add User::SRV_Conn
      6. Click OK
   4. Right Click on this Container
   5. Select Properties
   6. Set MaximumErrorCount to 999
3. Add "Data Flow Task" to the "Foreach Loop Container"
4. 1. Rename to Load Database Status
   2. Right Click on this Task
   3. Select Properties
   4. Set MaximumErrorCount to 999
5. Next, 2 data flow elements will be added to the "Data Flow Task".

   One will read tables on the remote instance and the other will save the

   results in the local database.

6. Select the Data Flow Tab or double click on Icon for "Data Flow task"
7. Add "OLE DB Source" from toolbox
8. 1. Double Click Icon
   1. OLE DB connection manager:MultiServer
   2. Change Data access mode to SQL Command
   3. SQL Command Text:

      SELECT CONVERT(NVARCHAR(128), SERVERPROPERTY('Servername')) AS

      [Server],

      ISNULL(RTRIM(CONVERT(NVARCHAR(128),

      SERVERPROPERTY('InstanceName'))), 'Default') AS InstanceName,

      master..sysdatabases.Name AS DatabaseName ,

      CONVERT(sysname,DATABASEPROPERTYEX(Name,'Status')) AS DatabaseStatus,

      CONVERT(sysname,DATABASEPROPERTYEX(Name,'Recovery')) AS

      [Recovery],

      CONVERT(sysname,DATABASEPROPERTYEX(Name,'UserAccess')) AS User_Access,

      CONVERT(sysname,DATABASEPROPERTYEX(Name,'Updatability')) AS Updatability

      FROM master..sysdatabases

   4. Click Preview to verify the SQL and then click Close when done
   5. Click OK
9. Add "OLE DB Destination" from toolbox
10. • Connect the "OLE DB Source" to this element with the green

      line/arrow

    • Double click on the Icon for OLE DB Destination and make the

      following changes

    • 1. OLE connection manager: QASRV.SQL_Overview
      2. Name of the table or the view: [dbo].[Database_Status]
      3. Click Mappings and confirm the columns mapping are correct
      4. Click OK

Ready to be Tested

• Click Control Flow tab
• Save all by pressing Ctrl+Shift +S
• Confirm all the servers added to the table SSIS_ServerList are valid.
• Press F5 to run
• To review any errors check progress tab.
• When done, click the blue line to get back to edit mode

Now you have the database status for all instances from a single

server.

Setting up Error Handling

The SSIS package from SQL Server magazine is missing error capturing

capabilities and would stop running tasks when an error was encountered. If

the package has trouble accessing an instance, I want it to continue

running after capturing the error. To allow this to happen a property needs

to be set first.

The MaximumErrorCount property is assign to each package, container, and

task. A container/task stops executing whenever this value is reached and

the package will not go to the next successfully dependent task. To keep

things running, I set the MaximumErrorCount property to 999 on most

containers and tasks. I left the "Populate the ADO Variable" task set to 1

because if it doesn't work, the remaining tasks have nothing to do.

Now to capture the error messages.

Create table SSIS_Errors

Modify the SSIS Package

Set MaximumErrorCount for the Package

The package has a MaximumErrorCount property that defaults to 1. If

any errors occur while running the package through a SQL Agent job, the job

will stop and be reported as failed. I set MaximumErrorCount property

failure to 999 because I want it to continue with the other steps in the

job.

Set the package property

1. Right Click on a blank spot on the package
2. Select Properties
3. Set MaximumErrorCount to 999

Create Error Log Connection

Use the Connection Manager to create an ADO.NET connection for the

SSIS_Errors table. An ADO.NET connection is used in place of the existing

OLE connection because OLE SQL can't access passed variables.

ADONET.SQL_Overview - point to the

database containing the SQL_Overview Database

Create Error Log Tasks

Empty the SSIS Errors Table

• Add "Execute SQL Task" object to the Truncate Tables container
• It does not need to be connected to any other tasks in this container
• Settings - Double Click on Icon
• • Name: Truncate SSIS_Errors
  • Connection: QASRV.SQL_Overview
  • SQL Statement: TRUNCATE Table

    SSIS_Errors

  • BypassPrepare: False
  • Click OK

Create OnError Event Handler

The Event Handlers tab can be used to capture errors encountered in a

package. A package can have a separate event handler for each container and

task. Only one is needed for capturing errors for this package.

Create the Event Handler

• Click Event Handlers tab
• Change Executable drop down to SSIS_MultiServers_Package
• Change Event Handler: drop down to OnError
• Click the Panel if there is a blue line.

• From the Toolbox add "Execute SQL Task" to the panel
• Settings - Double Click on Icon
• • General
  • • Name: Capture Errors Task
  • ConnectionType: ADO.NET
  • Connection: ADO.SQL_Overview
  • SQLStatement

    INSERT INTO [SQL_Overview].[dbo].[SSIS_Errors]

    ([Server]

    ,[TaskName]

    ,[ErrorCode]

    ,[ErrorDescription])
SELECT @Server,@TaskName,@ErrorCode,@ErrorDescription

  • Parameter Mappings - This is used to map variables that can be

    used by the ADO SQL

  • • Make the panel wider
    • Make Variable Name and Parameter Name columns wider
    • Click Add
    • • Variable Name> User::Srv_Conn
      • Data Type > String
      • Parameter > @Server
    • Click Add
    • • Variable Name> System::SourceName
      • Data Type > String
    • • Parameter > @TaskName
    • Click Add
    • • Variable Name> System::ErrorCode
      • Parameter > @ErrorCode
    • Click Add
    • • Variable Name> System::ErrorDescription
      • Data Type > String
      • Parameter > @ErrorDescription
    • Click OK
      

Test Package

• Put some nonexistent server names into the table SSIS
• Click Control Flow Tab
• Press F5
• Check the contents of the table SSIS_Errors
  

Conclusion

This package is just the tip of what you can collect from instances

using this package. In Part II, I will be providing instructions on

capturing output from stored procedures executed on remote instances. Part

III will contain an overview of the full package along with sample

reports.

References

• SQL Server Integration Services by Rodney Landrum