The comparison table (that are not of the long or blob data type or the Generated key column) are used as compare columns. You do not need to add primary key columns to the compare column list. They are always compared before the compare columns apply. The compare columns. Apr 23, 2019 This video explains about GENERATED KEY COLUMN feature of table comparison. Visit the below tutorials before viewing this: Basics of Table Comparison: https. Jul 13, 2017 Generated key column value has EMPID column, which has duplicates, selected in table comparison: – Initial source records before job is executed: – Now after execution, When the job is executed table comparison brings the records in it’s before image.
Here I am going to explain Load from text file, Usage of query transform, Table Comparison ,Map operation and Key Generation in a single Job.
We have the below employee details in a text file. The information that we have is Employee ID, Employee Name, Department, Salary and Age.
We need to design a job to load these information into a table named Employee_learning with the structure shown below. Once this data is being loaded in to the table, every update on Name or Department or Salary or Age of the employee should be treated as a new insert to the table. The way by which we can accomplish this scenario is explained further.
Target Table structure.
*) Create a new job with a workflow and data flow in it.
*) Put the source file in a location.
*) Right click on the Flat files and click on New. The below window will pop up.
*) At the Data files section of the new window, Select the location as ‘job Server’ from the dropdown, specify the root directory in the job server where the text file is placed.
Once the source file is specified, BODS automatically populates the data in the flat file window with the information from the source file. Remember to set the column delimiter as per the data. Here I have chosen it as Tab. You can edit the field name, data type, length, etc.. as you desire according to the data available. Click on Save and Close.
*) Drag the newly created flat file to your data flow and make it as source. Drag a Query transform and map the columns from the source files.
*) Drag a table comparison transform and connect the output of the query transform to the Table Comparison transform.
Select the target table name, mention the input primary column and the list of compare columns as shown below.
Note: Differences between the difference comparison methods.
Here I have selected ‘Cached Comparison Table’.
Map operation transform allows conversion between data manipulation operations. Map the Table comparison to a ‘Map_Operation’ Transform and change update as insert as shown below.
Map the output of the map operation to a key generation transform and select the target table, generated key column and increment value as shown below.
Now our job will look like this.
Now let’s execute the job and see the data loaded into the target table.
Let’s see what happens to the data by adding a new row in the source file as well updating an existing record in it.
Here I have added a new row and also updated the salary of row number 3.
Let’s see what happens to the data in the table by executing the job once again.
We will have the updated row and newly inserted rows added in the table as shown below.