|
Data Warehouse |
|
|
[SSIS] |
|
|
Questions: |
Notes: |
|
What are Character Map Transformations? |
The Character Map transformation applies string functions, such as conversion from lowercase to upper case to character data. This transformation operates only on column data with a string data type.
The Character Map transformation can convert column data in place or add a column to the transformation output and put the converted data in the new column. |
|
What are Conditional Split Transformations? |
The Conditional Split transformation will split the data into two separate outputs depending on a specific condition. For example we could pass a table of Employees with first and last names. We could provide two output conditions, one for employees with last names A-M and the other with N-Z. This would give us two separate outputs with the respective result sets. |
|
What are Data Conversion Transformations? |
The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column. This can be especially useful when importing from a flat file and the data types will be all NVARCHAR’s. The data conversion can let us convert these types to the desired data type. |
|
What are Derived Column Transformations? |
The Derived Column transformation will add new columns to the input data set and the values in these columns will be based on the result of any function the user chooses. For example, we can create an Age derived column in an Employee table by applying a DATEDIFF function to DOB and GETDATE(). |
|
What are Lookup Transformation? |
The Lookup transformation can join data sets from different sources based on a common column. This is similar to a join in that it takes matching columns from both tables and performs matches based on them. We can only have one match per value though so this is not a valid option when trying to join multiple columns with the same value in the matching column. In SQL Server 2008 we can use lookup to give multiple outputs. We can output the matching and non-matching rows separately as well as a third option for errors. |
