Using Database Sequence Instead of Sequence Generator in Informatica

We generate Surrogate Key in DW environment to maintain history and we need a key other than Primary Key to uniquely identify the record. Example: If EMPNO is the key, we can keep only one record in target for the same Employee number and can’t maintain history, so we use Surrogate key as Primary key and not EMPNO.
Informatica Sequence Generator:

• Passive and Connected Transformation
• The Sequence Generator transformation generates numeric values
• Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers

If a situation comes to use the DB Level Sequence  instead of the Sequence Generator Transformation in Informatica Here are the possible ways.

1.Create a Sequence in Oracle.(considering the underlying DB as Oracle)
2.Access the Nxtval of that sequence in  nInformatica by using below methods.

Using SQL Transformation:

Below is the mapping flow 

1.Connect all input ports  from source to SQL  Transformation.

 2.Add one extra output port which is attached with sequence generator query
3. Connect all ports from SQL tranformation to Relational Target. Once we do that we are done with the mapping.

 Use Stored Procedure Transformation

 Below is the mapping flow:

 

 1.Create a procedure or function in the DB .Here i'm using function.

CREATE OR REPLACE function SCOTT.getseqval return integer
is
next_val integer;
begin
select supplier_seq.nextval into next_val from dual;
return next_val;
end getseqval;
/

2.Import the function into informatica using Stored procedure transformation.

a.Select Stored Procedure transformation, import stored procedure dialog box opens.connect to the db and select the function and click ok.

3.In Expression transformation take an extra output port and write the expression  :SP.GETSEQVAL()

 

 4.Connect the ports from Exp to Target.

Hope this helps.

Please leave your comments , suggestions or Queries.