|
Data Warehouse |
|
|
[SSIS] |
|
|
Questions: |
Notes: |
|
What are Merge Join Transformations? |
The Merge Join transformation will give an output by taking two inputs and performing an INNER, LEFT, or FULL OUTER JOIN on the two tables. The input data must be sorted according to the matching columns for this to work correctly. We can then derive a result set by selecting the output columns from the left and right tables. |
|
What are Multicast Transformations? |
Multi cast will provide multiple copies of the same input. This lets us give inputs to multiple different sources from the one data set. |
|
What are Row Sampling Transformations? |
Row Sampling transformations will give a randomly generated sample of the data contained in the input. This can be specified by selecting a seed value to perform an exact random number of outputs or else use the Percentage Sampler to generate a % sample of records to the output. |
|
What are Sort Transformations? |
The Sort Transformation will sort the input data in ascending or descending order. We can perform multiple sorts on a single input in both ascending or descending order. This is much like the order by clause in T-SQL. Sort is a performance heavy transformation and will slow performance when operating on large amounts of data. This is because it must take all the data from the source, perform the sort and then output it rather than doing the calculations on a row by row basis. |
|
What are Union All Transformations? |
Union All is used to combine multiple inputs into one output. It can be used when used with merge join as it can handle more than 1 input and be used to give a single output. We can also specify what columns to output so we can have a single table from multiple sources with only the columns we are interested in. |
|
What are Destination Components? |
A destination is a Data Flow Component that writes the data from a data flow to a specific data store, or creates an in-memory dataset.
An Integration Service destination must have at least one input. The input contains input columns, which come from another data flow component. The input columns are mapped to columns in the destination. |
