You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading
into a dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was
last processed.
Which data flow components should you use to identify modified data? (Each correct answer
presents a complete solution. Choose all that apply.)
A.
Multicast
B.
Data Conversion
C.
Lookup
D.
Slowly Changing Dimension
E.
Aggregate
Explanation:
A: The transformation that distributes data sets to multiple outputs.
The transformation that distributes data sets to multiple outputs.
C: Lookup Transformation
The transformation that looks up values in a reference table using an exact match.
Note:* SQL Server Integration Services provides three different types of data flow components:
sources, transformations, and destinations. Sources extract data from data stores such as
tables and views in relational databases, files, and Analysis Services databases.
Transformations modify, summarize, and clean data. Destinations load data into data stores
or create in-memory datasets.
Incorrect:
Not B: Data Conversion Transformation
The transformation that converts the data type of a column to a different data type.
Not D: Slowly Changing Dimension Transformation
The transformation that configures the updating of a slowly changing dimension.
Not E: The Aggregate transformation applies aggregate functions, such as Average, to
column values and copies the results to the transformation output. Besides aggregate
functions, the transformation provides the GROUP BY clause, which you can use to specify
groups to aggregate across.
C.
Lookup
D.
Slowly Changing Dimension
0
0
C lookup
A would be wrong because multicast doesn’t use a condition to split the data
0
0
C, D
0
0
C and D
https://msdn.microsoft.com/en-us/library/ms141715(v=sql.110).aspx
At run time, the Slowly Changing Dimension transformation first tries to match the incoming row to a record in the lookup table. If no match is found, the incoming row is a new record; therefore, the Slowly Changing Dimension transformation performs no additional work, and directs the row to New Output.
If a match is found, the Slowly Changing Dimension transformation detects whether the row contains changes. If the row contains changes, the Slowly Changing Dimension transformation identifies the update type for each column and directs the row to the Changing Attributes Updates Output, Fixed Attribute Output, Historical Attributes Inserts Output, or Inferred Member Updates Output. If the row is unchanged, the Slowly Changing Dimension transformation directs the row to the Unchanged Output.
0
0