SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Overview Part 1

By David Bird, (first published: 2008/01/07)

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.


Create the Database SQL_Overview

Create the database SQL_Overview on a SQL Server 2005 instance.

Create and populate the SSIS_ServerList table

USE SQL_Overview
CREATE TABLE [dbo].[SSIS_ServerList](
[Server] [varchar](128) NOT NULL,
[Usage] [char](10) NULL,
[Skip_SQL_Overview] [bit] NULL,
[Server] ASC

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.

Example: 'myserver.domain.com\InstanceName'.

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.
USE SQL_Overview
CREATE TABLE #Server ( [Server] [varchar](128) )
EXEC xp_cmdshell 'sqlcmd /Lc'
INSERT INTO SSIS_ServerList ([Server])
SELECT [Server] FROM #Server WHERE [Server] IS NOT NULL

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

USE [SQL_Overview]
CREATE TABLE [dbo].[Database_Status](
[Server] [nvarchar](128) NOT NULL,
[InstanceName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NOT NULL,
[DatabaseStatus] [nvarchar](128) NULL,
Recovery] [nvarchar](128) NULL,
[User_Access] [nvarchar](128) NULL,
[Updatability] [nvarchar](128) NULL

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
Visual Studio installed templates: Integration Services Projects
Name: SSIS_MultiServer
Location: Specify where you want to store the package
Create Directory for Solution: Check this

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.
Scope: SSIS_MultiServers_Package
Data Type: String
Value:(local) or server\instance
* The instance name where the SQL_Overview Database is located

Scope: SSIS_MultiServers_Package
Data Type: Object
Value: System.Object

Warning: Variable names are case sensitive.

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
Right Click in Connection Managers Panel
  • Select New OLE DB Connection
  • Click New
  • Enter (local) or Server name that has the SQL_Overview database
  • Select the SQL_Overview database
  • Click OK
  • Click OK
  • Right Click on the newly added connection
  • Select Rename
  • Enter QASRV.SQL_Overview

- 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.
Right Click in Connection Managers Panel
  • Select New OLE DB Connection
  • Click New
  • Enter (local) or Server name that has the SQL_Overview database
  • Click OK
  • Click OK
  • Right Click on the newly added connection
  • Select Rename
  • Enter MultiServer
Now we need to customize this connection
  • Right Click on MultiServer
  • Select Properties
  • Change the Following Properties
    • Expressions click ... box
      • Click Property
      • Click Drop Down Arrow
      • Select ServerName
      • In the expression box type @[User::SRV_Conn]
      • Click OK
    • Initial Catalog change to Master

Create Tasks

Truncate Tables

This task will truncate the tables before they are loaded with data from the instances.
  • 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

This task reads the SSIS_ServerList and loads the server names into the variable SRV_Conn.
  • 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
This container will loop through the server names passed in the SRV_Conn variable and execute two tasks. The first task will retrieve data from the remote instances. The second task will save the data in the Database_Status table.
  1. Add "Foreach Loop Container" below the "Populate ADO Variable" task
    1. Connect the "Populate ADO Variable" task to this item with the green line/arrow
    2. Settings
      1. General
        1. Name: Collect Database Status
      1. Collection
        1. Change Enumerator to Foreach ADO enumerator
        2. Select ADO object source variable User::SQL_RS
      2. Variable Mapping
        1. Add User::SRV_Conn
      3. Click OK
    3. Right Click on this Container
    4. Select Properties
    5. Set MaximumErrorCount to 999
  2. Add "Data Flow Task" to the "Foreach Loop Container"
    1. Rename to Load Database Status
    2. Right Click on this Task
    3. Select Properties
    4. Set MaximumErrorCount to 999
  3. 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.
  4. Select the Data Flow Tab or double click on Icon for "Data Flow task"
  5. Add "OLE DB Source" from toolbox
    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],
      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
  6. Add "OLE DB Destination" from toolbox
    • 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
This table will contain any errors encountered by the package.
USE [SQL_Overview]
CREATE TABLE [dbo].[SSIS_Errors](
[Server] [varchar](128) NOT NULL,
[TaskName] [varchar](128) NULL,
[ErrorCode] [int] NULL,
[ErrorDescription] [varchar](MAX) NULL

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
Right Click in Connection Managers Panel
  • Select New ADONET.Net Connection
  • Click New
  • Enter (local) or Server name that has the SQL_Overview database
  • Select the SQL_Overview database
  • Click OK
  • Click OK
  • Right Click on the newly added connection
  • Select Rename
  • Enter ADONET.SQL_Overview

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]
      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


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.




Total article views: 27067 | Views in the last 30 days: 51
Related Articles

Use DTS package to connect to SQL Server 2008 Database...

Connect to SQL Server 2008 Database...


connect Database server

connect Database server


Add Witness to Existing SQL Server Database Mirroring

1. After connecting to the principal server instance, Right-click the database & go to properties. S...


Remove Witness from SQL Server Database Mirroring

1) After connecting to the principal server instance, Right-click the database & go to properties. S...


How to create SSIS package in SQL Server Database System

How to create SSIS package in SQL Server Database System