Update Strategy TransformationIn the last blog , we learnt about the Update Strategy transformation in Informatica , steps to create it , configuring the port and properties. We also discussed the business scenario of Update Strategy Transformation in Informatica. Now this is the time to apply Update Strategy Transformation in a sample Informatica mapping. Its a very simple but detailed example of Update Strategy Transformation in Informatica.

Problem Statement :

Create a mapping to update final EMP_ALL  table with the help of  input EMP_DELTA table with the help of  Update Strategy Transformation.To simplify the process , Change type flag is added.

Source Data:

EMP_DELTA Table Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT CHANGE_TYPE
14 ROOSEVELT CPA

9

10/12/1995

35000

2000

1

U

5

LINCOLN TECH

6

6/23/1994

22500

1400

4

D

9

JACKSON CEO

1/1/1990

75000

4

I

8

GRANT ENGINEER

10

3/30/1997

32000

3

U

2

JEORGE TECH

8

10/12/1995

50000

2

I

EMP_ALL  Orignal Table Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT

14

ROOSEVELT CPA

9

10/12/1995

35000

1

5

LINCOLN TECH

6

6/23/1994

22500

1400

4

8

GRANT ENGINEER

10

3/30/1997

32000

5

Target Data:

EMP_ALL  Resultant Table Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT

14

ROOSEVELT CPA

9

10/12/1995

35000

2000

1

8

GRANT ENGINEER

10

3/30/1997

32000

3

9

JACKSON CEO

1/1/1990

75000

4

2

JEORGE TECH

8

10/12/1995

50000

2

Sample Data for above problem can be get from here 

Solution:

Below are the steps to create full mapping.

  • Create a new mapping m_Update Strategy_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP_DELTA) and Target (EMP_ALL) to the mapping.
  • Add Update Strategy Transformation by “Go to Toolbar->click Transformation -> Create. Select the Update Strategy transformation”
Transformation Creation Step 1

Transformation Creation Step 1

Update Strategy Transformation Step 2

Update Strategy Transformation Step 2

Update Strategy Transformation Step 3

Update Strategy Transformation Step 3

  • Enter name “updt_EMP_ALL” and click Done.
  • Drag all required port  from SQ_EMP_DELTA to updt_EMP_ALL Transformation .
    Update Strategy Transformation Step 4

    Update Strategy Transformation Step 4

  • Click the Transformation tab and configure transformation properties.
Update Strategy Transformation Step 5

Update Strategy Transformation Step 5

  • Click on Expression editor for “update strategy expression” and below condition there

DECODE(CHANGE_TYPE,’I',DD_INSERT,’U',DD_UPDATE,’D',DD_DELETE)

  • Update Strategy Transformation Step 6Note: See that ‘Forward Rejected Rows‘ is checked. So that Forward Rejected Rows to Next Transformation/Written to Rejected/Bad File. If not it will write it to session log file.
  • Click OK to close updt_EMP_ALL update Strategy Transformation
  • Connect output ports of updt_EMP_ALL  Update Strategy Transformation to  target EMP_ALL
  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping ( by clicking on Repository-> Save or you can also press Ctrl+S)
  • Generate the workflow and run it

Over all Mapping:

Update Strategy Transformation Full Mapping

Update Strategy Transformation Full Mapping

So, in this article we have gone through step by step example of Update Strategy Transformation in Informatica mapping,  learnt the  step to add it , configure the port , properties , adding update strategy expression ports. Hope you enjoyed the article. Please feel free to give your feedback.







Alisha Lamba

About Alisha Lamba

My name is Alisha Lamba , a 24 yr old Software Techie . I love to write article on various technology used in IT Industry. I am, great music Fan, and love watching movies. I also love to watch Cricket

2 Thoughts on “Example of Update Strategy Transformation in Informatica : Example 1

  1. Pingback: Example of Update Strategy Transformation in In...

  2. Nice one..Thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation