You design a Business Intelligence (BI) solution by using SQL Server 2008. You are building an extract, transform, and load (ETL) process by using SQL Server 2008 Integration Services (SSIS).
You stage data in a SQL Server 2008 database. You load a data warehouse that has 15 dimension tables and 3 fact tables.
You need to design a control flow that meets the following requirements:
-Each table must be loaded with its own SSIS package. All packages must be controlled from a master package.
-Dimension tables must have no interdependencies.
-Dimension tables must load without error before the Fact tables are loaded.
-Master package parallelism must be maximized.
What should you do?
A.
Place the dimension packages in a sequence container and connect them by using a precedence constraint set to Success.
Use a precedence constraint set to Success to connect another sequence container holding the Fact packages.
B.
Place the dimension packages in a sequence container.
Use a precedence constraint set to Success to connect another sequence container holding the Fact packages.
Set the FailParentOnFailure property to True for each dimension package.
C.
Connect the dimension packages by using a precedence constraint set to Success.
Connect the Fact packages to the end of the dimension packages by using a precedence constraint set to Success.
Set the FailPackageOnFailure property to True for each dimension package.
D.
Place an Execute Package task inside a Foreach Loop container and change the connection string for the Execute Package task on each iteration.
Connect the Fact packages to the end of the Foreach Loop container by using a precedence constraint set to Success.
Set the FailPackageOnFailure property to True for the Foreach Loop container.
Explanation:
Tip: "package parallelism" = "sequence container … precedence constrain … FailParentOnFailure"http://msdn.microsoft.com/en-us/library/ms139855.aspx
Sequence Container
The Sequence container defines a control flow that is a subset of the package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.
There are many benefits of using a Sequence container:
– Disabling groups of tasks to focus package debugging on one subset of the package control flow.
– Managing properties on multiple tasks in one location by setting properties on a Sequence container instead of on the individual tasks.
– Providing scope for variables that a group of related tasks and containers use.
You can set a transaction attribute on the Sequence container to define a transaction for a subset of the package control flow. In this way, you can manage transactions at a more granular level. For example, if a Sequence container includes two related tasks, one task that deletes data in a table and another task that inserts data into a table, you can configure a transaction to ensure that the delete action is rolled back if the insert action fails.
FailParentOnFailure – A Boolean value that specifies whether the parent container fails if an error occurs in the container. The default value for this property is False.
Precedence Constraints – Precedence constraints link containers and tasks within the same parent container into an ordered control flow.
http://msdn.microsoft.com/en-us/library/ms141261.aspx
Precedence constraints link executables, containers, and tasks in packages into a control flow, and specify conditions that determine whether executables run. An executable can be a For Loop, Foreach Loop, or Sequence container; a task; or an event handler. Event handlers also use precedence constraints to link their executables into a control flow.
A precedence constraint links two executables: the precedence executable and the constrained executable. The precedence executable runs before the constrained executable, and the execution result of the precedence executable may determine whether the constrained executable runs. The following diagram shows two executables linked by a precedence constraint.