Metadata Collection - SQL Server Integration Services Design Patterns - page 8

PRIMARY KEY CLUSTERED( SQLServerInstance )

);

Now you're ready to populate the table with a list of SQL Server instances to mon-
itor. The code in Listing 1-3 will walk you through how to do this, although you will
need to update the placeholders with SQL Server instances that exist in your environ-
ment.

Listing 1-3 . Example of T-SQL Code to Insert Data into the

dba_monitor_SQLServerInstances Table

INSERT INTO dbo.dba_monitor_SQLServerInstances
(

SQLServerInstance

)
SELECT @@SERVERNAME-- The name of the server that hosts
the central repository
UNION ALL
SELECT 'YourSQLServerInstanceHere'-- Example of a SQL
Server instance
UNION ALL
SELECT 'YourSQLServerInstance\Here';-- Example of
a server with multiple instances

You still need to create two tables to store the metadata you collect, but you will
create these as you get to the relevant section in this chapter. Next, you will create your
Integration Services package.

The Iterative Framework

In this section, you lay the foundation for your iterative framework. Specifically, you
will demonstrate a repeatable pattern for populating a variable with a list of SQL Serv-
er instances and then iterating through the list and performing an action on each server.
First, open Visual Studio. Create a new project by navigating to File New
Project. Expand the Business Intelligence section (under Installed Templates), and
then click Integration Services Project. Name the project MetadataCollection , as illus-
trated in Figure 1-3 .