Scenario 12: How to Create Parameter File Dynamically?

Scenario 12: How to Create Parameter File Dynamically?

Let's Chat: There could be n no of case for which we need to create Parameter File dynamically. Here I am taking a simple case for explanation.

CASE: Our source file name changed for each and every run. Let's say Customer_US for United States Customer one day and Customer_Australia for Australian Customer second day and so on.
We have only one source folder where these files comes through FTP. Let's say Infa_Shared/SrcFiles/Cust

Theory/Analysis:
First we need to list out files in Source directory and write into file. Yes, through redirecting dir command output to a file. Treat this file as a source for a mapping which we will be using to create parameter file.
One point we have take care here If more than one files are there in Source Folder then we need to stop creating parameter file because we can pass only one file name through parameter file for processing of actual mapping.

Practically

Step 1:
Create source metadata manually in Source Analyzer.
Sources Menu-->Create
Source File Name: CUST_PARAM
Database Name: Flat_File
Database Type: Flat File
Create one column: File_Name  Data Type String  Prec 50

Step 2:
Create target metadata manually in Target Analyzer
Targets Menu-->Create
File_Name: Cust_Param_File
Database Type: Flat File
Create one column: Rows  Data Type String  Prec 500

Important Note: We need to decide our Session/Workflow name of Actual Session/Workflow which is going to use this parameter file, because we have to pass these value hard coded in mapping for header section of parameter file.

Ex: Session_Name: S_Actual_Session
      Workflow_Name: WKF_S_Actual_Workflow


Step 3: Dag and drop your created source and target to mapping designer.

Step 4: 
Aggregator
Drag and drop File_Name from Source Qualifier to Aggregator
Create two ports
Total_Record (Output Port) = COUNT(FILE_NAME)
Dummy (Output Port)= 1
(No Need to select any port as a Group Port)

Step 4:
Expression
Drag and drop File_Name from Source Qualifier to Expression
Create one Port
Dummy (Output Port) = 1

Step 5:
Sorter
Drag and drop File_Name and Dummy column to Sorter from expression
Select File_Name as key

Note: We need to sort the data before sending it to joiner as we are joining two pipelines of same source qualifier.

Step 6:
Joiner
Drag and Drop columns from aggregator and expression to Joiner.
Join Condition:  Dummy = Dummy 1
Join Type: Normal Join

Step 7:
Expression
Drag and Drop Total_Record and File_Name Port.
Create three output port.

ERROR_HANDLING (Output Port) = IIF(TOTAL_RECORD > 1, ABORT('More than Two Files are there in source folder'))

HEADER: (Output Port) =  '[SCENARIOS.WF:WKF_Actual_Workflow.ST:Actual_Session]'

O_FILE_NAME: (Output Port) = '$InputFile_Name='||FILE_NAME

Step 8:
Normalizer
Create Normalizer (We need Normalizer as two columns values we have to write in two rows)
Right Click-->Edit-->Normalizer Tab

Column_Name   Level    Occurs     Datatype     Prec    Scale
Row                     0           2                String          500       0

Connect Header to Row1 and File_Name to Row 2

Step 9
Connect Row column from Normalizer to Target.

Step 10:
In workflow manager we need to create a command task before session for creating source file for mapping which we are using to create parameter file.
Command Task
Create a Command Task in Workflow Manager
Command 1=dir C:\Informatica\9.5.1\server\infa_shared\SrcFiles\CUST\*.* /b > C:\Informatica\9.5.1\server\infa_shared\SrcFiles\Parameters\PARAM_SRC.txt

Set Error Handling Properties in Properties tab: Fail Task if any Commands fail (Check)

Note: My server is windows server so I have written DOS command. Unix Server user please convert command above for Unix.

Step 11: 
We need to set link condition for a link which is connecting command task to session.
$Command.PrevTaskStatus=Succeeded

Step 12:
Set Source and Target File Name in Session as below.
Source: PARAM_SRC.txt
Target: CUST_PARAM.pmf
Note: I have set Output and Source File Directory as same: $PMSourceFileDir\Parameters
Means I am reading and creating Parameter file in same directory.

Yahoo!!! Our Parameter file is ready in  $PMSourceFileDir\Parameters with name of  CUST_PARAM.pmf. Use it in your actual mapping.

Below step you have to add in your actual mapping in which you are using this dynamically created parameter file.

Step 13:
As we have to take care about clean up of current parameter file and source directory location for next run so, we need to use one more command task after your actual session. Let's say it S_Actual_Session.

Command Task
Command 1: del /Q C:\Informatica\9.5.1\server\infa_shared\SrcFiles\Parameters\*.*
Command 2: del /Q C:\Informatica\9.5.1\server\infa_shared\SrcFiles\CUST\*.*

Set Error Handling Properties in Properties tab: Fail Task if any Commands fail (Check)

Crate a link condition from you session to command task $Actual_Session.PrevTaskStatus=Succeeded

BINGO!!!

Feel free to post your doubt/your marks for my effort in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com