Informatica Scenario split source records and loading into 3 targets (based on some logic)

Posted: October 15, 2014 in Informatica

Informatica Scenario:-

Let’s say I have 200 record in source table and I have 3 Target table A,B,C. I have to insert first 1 to 10 records in ‘A’ , then 11 to 20 in ‘B’ and 21 to 30 in C. Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C. So on up to last record.

Solution:-  

In the above requirement we need to Informatica  and load it into 3 targets (each group with 10 Records).

The below is the mapping. Let’s see in steps.

STEP1:- First let’s see how to create sequence and reset it, after it reaches to 30 count (because of the above Requirement 10 records as a set for 3 targets).

As you can see this can be done in one Variable port. Let’s see how this works

IIF(v_SEQ=30

(this means the variable port reaches to 30),then set the variable port back to 1, else v_SEQ+1  increment the variable).

Lets parameter the value ‘30’ to $$SPLT_LIMT. And go to Mappings –> then Parameter and variable click it and declare the variable/parameter there.


Why we have to use Mapping Parameters:-
what if tomorrow requirement changes to (load 1st records to target A, Second records to target B and finally 3 records to target C). In this case if we don’t use mapping parameters we have to modify mapping.

In this case Just change in Param File:- Assign 3 to the variable $$SPLT_LIMT in the parameter file, no need to change anything in the mapping.

Step3:-
Create 3 groups and divide the records as per the requirement in RTR (Router
Transformation).


As we did for Expression transformation we can parameterize the router Group conditions as follows.
o_SEQ >0 and o_SEQ <11 (change the hard coded values 0 and 11)
 TO  
o_SEQ> $$GRP1_LOW_LMIT AND  o_SEQ <$$GRP1_UP_LMIT (declare this in Mapping parameter and variables and add it to your parameter file). Similarly do it for rest of the groups as well.

So let’s run our mapping and see the output.

Total SRC records 201 divided equally (10 records per set).

The Parameter file Looks something like this 

SAMP_MAPP.pram
[GLOBAL]

$$GRP1_LOW_LMIT=0
$$GRP1_UP_LMIT=11
$$GRP1_LOW_LMIT=10
$$GRP1_UP_LMIT=21
$$GRP1_LOW_LMIT=20
$$GRP1_UP_LMIT=31

$$SPLT_LIMT=30
Based on your requirement change the values in the parameter file.

This way we created a generic mapping. (the mapping needs to change only in case if you want to add one more target).

Working With JAVA Transformation:-
http://netezzamigration.blogspot.com/2014/10/implementing-java-transformation-with.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s