DRAG DROP
You are creating a SQL Server Integration Services (SSIS) package to populate a fact table
from a source table. The fact table and source table are located in a SQL Azure database.

The source table has a price field and a tax field. The OLE DB source uses the data access
mode of Table.
You have the following requirements:
The fact table must populate a column named TotalCost that computes the sum of the price
and tax columns.
Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To
answer, move the appropriate components from the list of components to the answer area
and arrange them in the correct order.)

Answer: See the explanation

Explanation:
Box 1: Ole DB Source
Box 2: Conditional Split
Disregard lines with a 0 sum.
Box 3: Derived column
Box 4: Ole DB Destination

Note:
* You configure a Data Flow task by adding components to the Data Flow tab. SSIS
supports three types of data flow components:
Sources: Where the data comes from
Transformations: How you can modify the data
Destinations: Where you want to put the data
* Creating a data flow includes the following steps:
/ Adding one or more sources to extract data from files and databases, and add connection
managers to connect to the sources.
/ Adding the transformations that meet the business requirements of the package. A data
flow is not required to include transformations.
Some transformations require a connection manager. For example, the Lookup
transformation uses a connection manager to connect to the database that contains the
lookup data.
/ Connecting data flow components by connecting the output of sources and transformations
to the input of transformations and destinations.
/ Adding one or more destinations to load data into data stores such as files and databases,
and adding connection managers to connect to the data sources.
/ Configuring error outputs on components to handle problems.

At run time, row-level errors may occur when data flow components convert data, perform a
lookup, or evaluate expressions. For example, a data column with a string value cannot be
converted to an integer, or an expression tries to divide by zero. Both operations cause
errors, and the rows that contain the errors can be processed separately using an error flow.
/ Include annotations to make the data flow self-documenting.
* The capabilities of transformations vary broadly. Transformations can perform tasks such
as updating, summarizing, cleaning, merging, and distributing data. You can modify values
in columns, look up values in tables, clean data, and aggregate column values.
* The Data Flow task encapsulates the data flow engine that moves data between sources
and destinations, and lets the user transform, clean, and modify data as it is moved. Addition
of a Data Flow task to a package control flow makes it possible for the package to extract,
transform, and load data.
A data flow consists of at least one data flow component, but it is typically a set of connected
data flow components: sources that extract data; transformations that modify, route, or
summarize data; and destinations that load data.