Load from text file, Usage of query transform, Table Comparison ,Map operation and Key Generation in a single Job

[former_member206072]

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.

1. 1) In the row-by-row mode, the Table Comparison Transform executes a select statement for every single input row to lookup the value in the compare table.
2. 2) In sorted input mode, we guarantee that the data is coming sorted by the columns listed as primary key columns ascending. Then DI will execute one select statement for the entire comparison table with an order by on the columns of the input primary key list ascending plus the generated key column descending if specified. The advantage is, just one SQL statement executed and no memory required in the engine.
3. 3) In cached mode, this transform is just collecting the input data and indexing it in memory to later lookup the rows inside the cache.

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.