Database Reference
In-Depth Information
CHAPTER 1
Metadata Collection
The first Integration Services design pattern we will cover is metadata collection. What
do we mean by
metadata collection
? Good question. This chapter could also be called
“Using SSIS to Save Time and Become an Awesome DBA.” Many DBAs spend a large
portion of time monitoring activities such as verifying backups, alerting on scheduled
job failures, creating schema snapshots (“just in case”), examining space utilization, and
logging database growth over time, to name just a very few. Most Relational Database
Management Systems (RDBMS's) provide metadata to help DBAs monitor their sys-
tems. If you've been a DBA for a few years, you may even have a “tool bag” of scripts
that you use to interrogate metadata. Running these scripts manually is easy when you
have just one or two servers; however, this can quickly become unwieldy and consume a
large portion of your time as your enterprise grows and as the number of database serv-
ers increases.
This chapter examines how to use Integration Services and the metadata that exists
within SQL Server to automate some of these routine tasks.
About SQL Server Data Tools
SQL Server Data Tools - Business Intelligence (SSDT-BI) is Microsoft's IDE for devel-
oping Integration Services packages. It leverages the maturity and familiarity of Visual
Studio to provide a unified development platform for SQL Server Business Intelligence
projects, including Integration Services, Analysis Services, and Reporting Services pro-
jects. This topic is written using SSDT-BI for Visual Studio 2013 and SSIS 2014.
