Sunday, June 12, 2011

Incremental Reading - Only one row at one session run..

How to load first record of first time run, Second record on 2nd time run... 3rd record on 3rd time run, to target table?

I understood your requirement as
When you load the session for the first time it has to load only 1st record. On the second run of the session it has to load 2nd record only, and so on... rit? If this is your requirement.. you can achieve this using Mapping variable concept.... Assuming  EMP schema as my source..

> Desing mapping as below..
incremental_load.jpg
> Crete and connect nexval port to Exp-Tr and Set Seq_Gen properties as: Check on Cycle, (Be clear with properties)
> Create Mapping Variable $$Var.
> In the Exp tr Create variable port and expression as   --  ' var_assign = $$Var + 1
> In the Filter Transformation write condition as   Condition :   seq_no = $$Var
> Connect to Target..
  


   For the first time run $$Var will hold intial value as 1 and first record will be loaded, and $$Var will be incremented by 1 in the Exp transformation . On the second run $$Var value will become 2 and only 2nd record will be loaded into the target.. and so on..


-- Ram

3 comments:

  1. thanks a lot..What should be the datatype for the variable port.??The error is "Operand cannot be converted as a number"..But doesnt accept any of the data types?Pls help..

    ReplyDelete
  2. this is wrong!!!!
    suppose we have 3 records in the source, and for the first record var=1 and seq no=1; for the second record var=2, seq no=2, and for the 3rd record var=3 and seq no=3; how do u stop loading 2nd and 3 rd records during the first run?????

    ReplyDelete