Here is a sample mapping to illustrate how the Transaction Control transformation can dynamically create multiple flat file outputs, each with a unique name and data set.
The mapping reads a source table whose key is “Group Id”, and outputs a separate target file for each Group Id data set. The file names are also generated dynamically based on the Group Id.
There are two “secrets” to the mapping, the Transaction Control transformation, which allows dynamic creation of the output target files and the “FileName” port in the flat file target, which allows you to dynamically name the output files.
Overview of the mappingOnce you’ve imported the sample mapping into your repository, open it in the mapping designer workspace. Notice the flow of the mapping:
- Order the Source rows by the Key
- Compare the Current Row’s Key to the Previous (exp_Srt_Output)
- If Current Row’s Key is the same as the Previous “Continue” writing to the target (exp_Srt_Output)
- If Current Row’s Key is different than the Previous, commit or close the output & begin a new one (exp_Srt_Output)
Dynamic File NameInformatica has an option for flat file Targets called “FileName”, the sample mapping uses this in conjunction with the Transaction Control to generate dynamic output file names.
To use the “FileName” in your own mappings, open a flat file in Target Designer then click the “Columns” tab. Notice on the icon bar in the top right, there is a button that looks like an “F” surrounded by dotted lines
. Clicking this button adds a new column called FileName. This Designer Guide explains how it works: “When you configure a FileName port, the Integration Service overrides the Output Filename session attribute with the value in the FileName column”
To see how FileName is used in the sample mapping, open exp_Srt_Output transformation and notice how the FileName port is created by using the Group Id port.
Running the Sample Mapping
- Download the objects and import them into a test folder on your development repository:
- Place the “Infa_eMail.csv” file into your default $PMSourceFileDir\ folder
- Execute the wf_Example_FF_Tricks workflow, the output files will reside in your default $PMTargetFileDir\ folder