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

Tip Don't have SSDT-BI installed yet? SSDT-BI is available from Microsoft's
Download Center. Please note that SSDT-BI is not backward compatible, so make sure
you verify that the version you download is appropriate for your environment.
A Peek at the Final Product
Let's discuss the Integration Services package you will be creating in this chapter.
In SQL Server, you will do the following:
1. Create a database to act as your central repository for database monit-
oring.
2. Create a table to store a list of SQL Server instances that you wish to
monitor.
3. Create a table for each of the data elements you wish to monitor (un-
used indexes and database growth).
In Integration Services, you will do the following:
1. Create a new Integration Services package.
2. Retrieve a list of SQL Server instances and store the list in a variable.
3. Create an OLE DB connection with a dynamically populated server
name.
4. Iterate through each database and
a. Retrieve current database and log file sizes for historical monitor-
ing.
b. Retrieve a list of index candidates for potential redesign or drop-
ping.
c. Update the Last Monitored value for each SQL Server instance.
This is a very flexible model that you can easily expand to include many more
monitoring tasks. A screenshot of the completed package is displayed in Figure 1-1 .