How to Pass Parameter Values at Runtime in Informatica Mapping Parameter

user_13864157 0
Viewed 481

I have a scenario like we need to load data from source file to target table from a particular date [like LOAD_DATE], So I’ll create a mapping parameter for LOAD_DATE and pass that in Source Qualifier query. My query looks like this.

SELECT * FROM my_TABLE where DATE >= ‘$$LOAD_DATE’

So here I need to pass parameter values for ‘$$LOAD_DATE’ from another external database. I know that I need to pass the values from the Parameter file.

But my requirement is not to hardcore the values in the Parameter file but to feed it in runtime from another database. I will appreciate your help and thoughts on this.

informatica-powercenter informatica unix
1 Answers

You dont have to hardcode.
You can do it like this - option 1. Create a mapping to create the param file in particular format.

  • Read for the other DB.
  • In expression transformation create below port which will generate actual param string. Pls note, we need to add new line so its recognized like a actual param file.
out_str = '[<<name of folder . name of workflow or sessoin>>]' || chr(12) ||
   '$$LOAD_DATE='|||| CHR(39) ||<<date value from another DB>>|| CHR(39)
  • Then link above port to a flat file target. Name the output file as session_param.txt or whatever suitable. Pls make sure the parameter is generated correctly.

Use above file as a parameter file in your actual workflow.

Option 2 - You can join another table with original table flow. This can be difficult and need to change existing mapping.

  • Join the another table from another DB with main table based on a dummy condition. make sure you get distinct values of LOAD_DATE from another table. Make sure you always have 1 value from this DB.
  • Once you have the LOAD_DATE field from another table, you can use it in filter transformation to filter the data.
  • After this point you can add your original mapping. Whole mapping should be like this-
SQ_MAIN_TABLE ----------------------->|
sq_ANOTHER_TABLE --DISTINCT_LOAD_DT-->JNR--FIL on LOAD_DT --><<your mapping logic>>
user_64305230
Related