+++&gt; 3.Map_operation

Map_Operation

Allows conversions between data manipulation operations. The Map_Operation transform allows you to change

operation codes on data sets to produce the desired output. For example, if a row in the input data set has been

updated in some previous operation in the data flow, you can use this transform to map the UPDATE operation to

an INSERT. The result could be to convert UPDATE rows to INSERT rows to preserve the existing row in the target.

Data Services can push Map_Operation transforms to the source database.

Note on Map_Operation: It allows you to change the opcodes on your data. Before discussing this
we should know about opcodes precisely

In DI we have 5 opcodes, Normal, Insert, Update, Delete and Discard(you’ll see this option in Map_Operaion only)

Normal: Indeed, it creates a new row in the target. The data which is coming from the source, is usually flagged as
normal opcode

Insert: It does the same thing, it creates a new row in the target and the rows will be flagged as ‘I’ – Insert

Update: If the rows are flagged as ‘U’ , it overwrites an existing row in the target.

Delete: If the rows are flagged as ‘D’, those rows will be deleted from the target

Discard: If you select this option, those rows will not be loaded into the target.

Understanding Opcodes: Here is an example, in the below figure I’m using (normal to normal, normal to insert,
normal to update, normal to delete) opcodes. Here i have taken normal opcode mainly because,query transform
always takes normal rows as input and produces normal rows as output.

In the first flow, i.e., MO_Normal-> i have selected Normal as Normal and discarded rest all opcodes.

This flow inserts all records in to the target which are coming from the source

In the second flow, MO_Insert-> i have selected normal as insert and discarded rest all opcodes.

It does the same thing, inserts all records in to target.

Have a glance on both the data sets before loading in to the target. You will see no opcode for Normal as
Normal rows(1st flow), but you can see Insert opcode indicated as ‘I’ for Normal as Insert (2nd flow).

In the third flow, i want to update few records in the target .

Let’s say i want to update all the records whose deptno = 20.

Now, I have selected normal as update in the map_operation and discarded rest all.

Check the data, you can see the updated rows flagged as ‘U’

In the fourth flow, I want to delete some records from the target.

Let’s say i want to delete rows whose deptno = 30, in the map_operation transform i have selected

normal as delete and discarded rest all .

In the query transform i have filtered out few records, where i want to delete those from the target.

and you can see the data after the map_operation dataset along with delete opcode ‘D’.

In the target data set, the above records will be deleted. Check the target data

Now here in the sub-flow, i have inserted these deleted records in to another table.
For this i have added one more Map_Operation and selected row type delete as insert.