LOOKUP TRANSFORMATION

Introduction:

            Using lookup transformation we can find the matched and unmatched records.

Sample package:

Requirement:-

Source:-

            I have a source file that contains employee data (ID, Name, Sal, and Dept_Num). The source file is having few invalid records with wrong department.

Lookup table:-

            Department table is having valid department information.

Target table:-

            I need to load the valid data into target table, with the help of source file and lookup table.

Package creation:

• From control flow items, drag and drop “Data flow task” into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• From data flow items, drag and drop “Flat file source” into designer surface. Create the source file mappings.

• After source mappings, drag and drop the “Lookup” transformation into designer surface. Connect the source green arrow to the transformation shown below screen shot.

Note: I am going to find the valid records from source, by checking the department value in department table because department table is having valid departments information. So we need to join the two data sets (Source and lookup) based on Dept_Num column. Make sure while the time of join Dept_num column data type is same from source and lookup datasets.

• Dept_num data type from source file (varchar(50)) is not matching with lookup table (INT), so we need to do data conversion.
• Instead of “Data conversion” transformation we can use “Advanced editor”. Follow below steps.

1.      Right click on “Flat file source” and select “Show advanced editor” option.

2.      “Advanced editor for flat file source“window will open.

3.      Click on “Input and output properties” tab.

4.      Expand the “Flat file source output” highlighted in the above screen shot. In the “Flat file source output”, expand the “Output columns”.

5.      In the “Output columns” select “Dept_Num” column. Then right side of the editor window we can find the properties of the column.

6.      In the “Data Type Properties”, enable the drop down list of the “Data type” and select data type “four-byte signed integer [DT_I4]”. Because Dept_Num column data type in the lookup table is "INT".

7.      Click “OK” then editor window will close. Data conversion of the “Dept_Num” column is finished.

• Edit the lookup transformation.
• Set the “Redirect row to no matched output” to handle the rows with no matching entries.

• After that select “Connection” option (highlighted in the above screen shot) and create connection manager and select the lookup table.

• Click on “Columns” option (highlighted in the above screen shot).

• Right click on source or lookup columns and select “Edit Mappings” option.

• “Create relationships” window will open. Select “Input column” and “Lookup column” as "Dept_Num".

• Click “OK” to finish relationship.

• Click “OK” button in the “Lookup transformation editor” window, then window will disappear.
• Lookup transformation mappings are finished.
• Drag and drop the “OLEDB destination” into designer surface.
• Connect the “Lookup transformation green arrow to the “OLEDB destination”, then “Input output selection” window will open. In that select “Output” as “Lookup Matched Output”.

• Click “OK”
• Edit the “OLEDB destination” and give the target table mappings.
• If unmatched records are required then drag and drop the one more destination from tool box. Connect the "Lookup" transformation green arrow to the new target. Edit the new target and give the mappings.
• After mappings, execute the package.

Target table output:

Department 40 is invalid department, so the dept 40 related records are not loaded into target table.

 

DERIVED COLUMN TRANSFORMATION

Introduction:

            The derived column transformation creates new columns by applying expressions (expression can contain any combination of variables, functions, operations, and columns from the transformation input) to the source columns.

The result can be added as a new column or inserted into an existing column as a replacement value.

Sample package:

Requirement:-

            I have source table with three columns (ID, First_Name, and Last_Name) shown below screen shot.

I need to load source data into target table with two additional columns.

Full_Name: Full name is a combination of first name and last name.

Load_Time: Load time is current package execution time stamp.

Package creation:-

• From control flow items, drag and drop “Data flow task” into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• From data flow items, drag and drop “OLEDB source” into designer surface. Create the source table mappings.

• After source mappings, drag and drop the “Derived column” transformation into designer surface. Connect the source green arrow to the transformation shown below screen shot.

• Double click on “Derived column” transformation, then “Derived column transformation editor” window will open, shown below.

• In the expression box we need to write required expression.
• Full_Name: expand the columns shown below screen shot.

• Drag and drop the “First_Name” column into the “Expression” box after that “Last_Name” into the same box (After the column “First_Name”). Use concatenation (+) between the two columns ([First_Name]+ [Last_Name]). See the below screen shot.

• Update the “Derived Column Name” box with appropriate name (Full_Name).

• “Full_Name” column creation is finished. We need to create “Load_Time” column, for that expand the “Date/Time Functions”. Drag and drop the GETDATE() function into the expression box and give the appropriate name (Load_Time) to that column.

• Click “OK”.
• Drag and drop the “OLEDB destination” into designer surface, and connect the “Derived column” transformation green arrow to the “Destination”.
• Edit the “OLEDB destination” and give the mappings.

• After target mappings, execute the package.

Target table output:

Note:

If you want space between the “first name and last name” then use below expression.

Expression: [First_Name]+” “+[Last_Name]

Output:

CONDITIONAL SPLIT TRANSFORMATION

Introduction:

            Conditional split transformation can split the data rows to different outputs based on given condition. If a row is not satisfied the given condition, then that row directed to the default output.

Requirement:

            Source table (Sample_tbl) is having birds and animals information. With the help of source data I need to create “Bird” and “Animal” files (Bird file should have bird’s information and animal file should have animal’s information).

Source table data:

Package creation:

• From control flow items, drag and drop “Data flow task” into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• From data flow items, drag and drop “OLEDB source” into designer surface. Create the source table mappings.

• After source mappings, drag and drop “Conditional split” into designer surface.
• Connect the source green arrow to the “Conditional split” transformation.
• Edit the “Conditional split” then “Conditional split transformation editor” window will open.

• We need to write required conditions in the “Conditional split transformation editor” window to split the data.
• Case1: Type==”Bird”
• Case2: Type==”Animal”

• In the “output Name" section, rename the cases with appropriate names. Shown below screen shot.

• Click “OK” button.
• From tool box drag and drop two “Flat file target” (one is for “Birds” and other one is for “Animals”)
• Select “Conditional split” green arrow and connect to one of the “Flat file target” then “Input output selection” window will open, shown below.

• In the “Input and output selection” window select the output “Birds” by using “Output:” drop down list. Shown below screen shot.

• Click “OK”.
• Select the "Conditional split" green arrow and connect to the second “Flat file destination”. Input output selection window will open. In the selection window “Output:” drop down list select “Animals”.

• Click “OK” to close “Input output selection” Window.

• Edit the “Birds” and “Animals” Flat file destinations one after other one and give the mappings (target file path and name etc). Once mappings get finished then  execute the package.

Target files output:

Birds:

Animals: