Execution Patterns - SQL Server Integration Services Design Patterns - page 81

Most data warehouses require an extraction step that stages all data—from dimen-
sion and fact sources— to a staging database. Next, dimension data are loaded into the
data warehouse. Finally, fact data are loaded from the staging database into the data
warehouse.
The precedence of operations is as follows: extract fact and extract dimensions can
run concurrently (in parallel). You can design one package for each dimension and fact
source table extract operation, add them to the extract SSIS application, and execute
that SSIS application as Step 1 of your DW ETL job. Once that completes, Step 2 can
load the dimension data from the stage database to the data warehouse. Once that com-
pletes, Step 3 can load the fact data from the staging database to the data warehouse.
So while relatively simple and somewhat limited, our custom execution framework can
facilitate configurable parallel and serial ETL operations.
Execute Package Task
The Execute Package task is best understood in action. To demonstrate, create a new
SSIS package and rename it Parent2.dtsx . Add an Execute Package task to the
control flow. Open the editor and observe the selections for the ReferenceType prop-
erty as shown in Figure 2-7 .
Figure 2-7 . The Execute Package Task Reference Property
If the ReferenceType package is set to Project Reference, the Execute Package task
can be used to start packages in the SSIS project, supporting the project deployment
model. Setting this property to External Reference allows executing SSIS packages to
be stored in either the MSDB database or file system, supporting the package deploy-