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

Figure 1-12 . Variable mappings
Click the OK button to close the Foreach Loop Editor.
Let's review what you've done so far. You now have a variable, SQLServer-
List , which contains a list of all the SQL Server instances you inserted into the
dba_monitor_SQLServerInstances table. The Foreach Loop container then
shreds this variable, walking through each value—each SQL Server instance, in this
case—one at a time. At each pass, it pushes the value of one of those SQL Server in-
stances into another variable, SQLServerInstance .
Now it's time to set up the dynamic connection that will be used to connect to each
of the SQL Server instances you're monitoring. To do this, you need to create a dummy
connection and configure it to use the server name stored in SQLServerInstance .
Right-click in the Connection Managers window and select New OLE DB Connec-
tion. Create a new connection using the same server and security credentials you used
previously ( Figure 1-7 ), but select Master as the database this time. To be clear, you're
using the same server purely for convenience. In reality, the server specified in the
dummy connection is irrelevant as long as you have sufficient permissions to log onto
it, because whatever value you enter will be overwritten by the SQLServerIn-
stance variable at runtime. The database value does matter, however, because the
database you select must exist on every server. Since Master is a system database, it is
a natural choice.
Click OK to close the Connection Manager Properties window. But you're not done
with this connection just yet. Right-click the newly created connection and select Prop-
erties. Change the Name property to DynamicSQLServerInstance , then click the [ … ]
icon in the Expressions field. This will bring up the Property Expressions Editor. Select