My solution was to query each database
to return database-specific information in a
table that I created in the TempDB database.
The table I created wasn't a true temporary
table with a # or ## prefix. Although the
table resides in the TempDB database, its
size and location have minimal effect on
the source server. Web Listing 3 contains
the code to create and populate this table,
called HoldForEachDB. Notice the syntax
of the sp_MSForEachDB stored procedure,
which substitutes a question mark for the
database name. This command is fairly
useful, without requiring you to wrap logic
into cursors to provide similar functionality.
(Note: When you save the task, you might
receive a message such as "The DROP table
construct statement is not supported." However, the query will still delete the table and
repopulate it with crucial database information such as status, recovery model, size, and
updateability.)
Troubleshooting
The package runs on both SQL Server 2005
and 2000 servers. If no errors generate, the
package will run in less than 2 seconds for
2 servers and in less than 2 minutes for 30
servers with varying amounts of data. If the
package runs successfully and each package object turns green, you're ready to query
the repository with custom queries and
reports. Even if the package objects don't
all turn green, don't worry—each task has
a maximum error value that you can set to
let the package continue running even if
it can't connect to an individual server, for
example.
Servers fail to connect for various reasons—for example, they might not be
online, or the user running the package
doesn't have access. You can use event handling to send an email message or write to a
log to report server failures. Event handling,
logging, and scheduling the SSIS package for
automatic runs are beyond the scope of this
article. For more information about these
tasks, see SQL Server Books Online (BOL).
After you load the database, you can run
a simple query such as the following:
SELECT [Server]
,\[ProductVersion]
,\[ProductLevel]
,\[IsIntegratedSecurityOnly]
,\[EngineEdition]
,\[Collation]
,\[IsClustered]
,\[IsFullTextInstalled]
,\[SqlCharSet]
FROM [DBA_Rep].[dbo].[SQL_
Servers]
This query provides the data that Table 1 shows, which includes useful information
about the two servers I used to test the
package.
You've got the Power
The package I created is just one example
of what you can do with SSIS. Combining
the variable and expression values gives you
unprecedented control and efficiency in
designing packages. For my package, I used
variables and expressions to programmatically control a connection string to query
multiple servers without needing to
manually define each connection.