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

Figure 1-14 . Dynamic connection properties
At this point, you now have a reusable framework for iterating through a list of
SQL Server instances and doing something on each server. This in and of itself is a
very valuable design pattern. However, because this is a chapter on metadata collec-
tion, we would be remiss if we did not actually demonstrate collecting and storing
metadata. The next section will walk you through setting up two useful metadata ex-
tracts.
Metadata Collection
You're now ready to retrieve metadata from your list of servers. But what should you
collect? An incredible wealth of information is available for retrieval, including secur-
ity information, usage data, table schema snapshots, failed job details, fragmentation
levels, and performance counters, to name just a few. For this first example, let's keep
it simple and retrieve current database and log file size. This information is useful for
historical database growth and capacity planning.