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

Figure 1-20 . The Map Items by Matching Names option in the Mappings page
Once more, the log_id column will not map to anything because it is an identity
column. Click OK to return to the Data Flow designer, and then click the Control Flow
tab.
See how quickly that second data flow went? You can continue to easily add more
metadata collection tasks using this method. All that you have left to do is to update
your LastMonitored column in the dba_monitor_SQLServerInstances
table.
Tip It may be tempting to create a one-size-fits-all package. However, it is generally
a better idea to separate metadata collections into separate packages organized by fre-
quency requirements. For example, the metadata you have collected in this chapter only
requires periodic samples, such as daily or weekly collection. Metadata that requires
more frequent collection, such as an hourly check for failed SQL Server Agent jobs,
should be stored in a separate package.
Add an Execute SQL task to your Foreach Loop container and rename it Update
LastMonitored . Connect the Unused Indexes data flow to the Update LastMonitored
Execute SQL task. Double-click the Execute SQL task to edit its properties. Select the
dbaCentralLogging connection in the Connection drop-down, and then enter the code
from Listing 1-9 in the SQLStatement field.