Source Qualifier Transformation Continued…
- Source tab will contain the source table details.
- Metadata Extensions tab is to add any new column, but we will not be able to add any new column in the SQ but only using expression transformation.
- In this source qualifier, source filter we need to pass a variable / parameter (marked with $$ symbol) to capture the updates / inserts from the source.
- Double click on the source qualifier transformation, click on the properties tab and in the source filter line – click on the value column and mention the filter condition as below,
NSE.DATE>TO_DATE (‘$$MAX_NSE_TBL_TRNSCTN_DT’, ‘MM/DD/YYYY HH24: MI: SS’) which indicates if any records gets loaded in the source NSE will be either inserted (if it is new) or updated (if it is old record) in the target table NSE_TBL. - To get this parameter (‘$$MAX_NSE_TBL_TRNSCTN_DT’) we need to create a parameter mapping which will fetch the maximum transaction date (‘TRNSCTN_DT’) from the table NSE_TBL after each successful run.
- EXPRESSION transformation – used to pull in the records in the appropriate column name ( as the columns required in NSE_TBL).
- Expression transformation can be either passive or active – An active transformation can change the number of rows as output after the transformation, while a passive transformation does not change the number of rows and passes through the same number of rows that was given to it as input.
- Expression transformation has four tabs – Transformation, Ports, Properties and Metadata Extensions.
- Transformation tab is to mention the details or summary of what we are performing with this transformation e.g. ltrim of one date column, setting sysdate as default etc.
- Ports is the tab where we mention the ports from the Source Qualifier. Here the ports can be made as input port or output port using the functions available based on the requirement.
- Properties is for tracing level in the session log.
- Metadata Extensions is for adding any new column in the target.
- To create an expression transformation, in the mapping designer choose transformation – create. Select the expression transformation. Enter the name as per the standards in this case we can have it as EXP_NSE_TBL and click OK.
- Create the input ports. As we have the input ports available we can use the link columns in the layout menu to link all the columns from the Source Qualifier to the expression EXP_NSE_TBL. Then click and drag the each column used in the transformation calculation. With this method designer copies the port.
- Other method is to create the ports manually using the Edit dialog box.
- In order to reuse the expression we need to create each port manually only within the transformation.
- Repeat the above 3 steps for each port we wanted to create. In this case we need to do for 7 columns from the source and one column needs to be added which is the SYS_DT with the data type as date /time, precision 19,scale 0, marked as OUTPUT port and the expression as SYSDATE.
- Create the output ports (O) we need, making sure that the respective port – data type is assigned that matches the expression result value.
- Click the small button that appears in the expression dialog and enter the expression in the expression editor.
- Check the expression by clicking validate. If necessary make any corrections or syntax errors and save the expression, exit the expression editor.
- Connect the output ports to the next transformation.
- Select the tracing level in the properties tab based on the amount of information required in the session log and Click on Repository Save.
- The primary key column of the table NSE_TBL from the expression transformation should be passed onto the look up transformation to make sure if the record from the source is an insert or update.
Note: Informatica and its related products are Trademarks of Informatica Corporation. For more details, please visit their official website www.Informatica.com.
