Jeff's Cluttered Mind

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 mapping

Once 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)

The source input must be ordered by the “Key” so it can be a trigger for the Transaction Control. The transformation exp_Srt_Output evaluates the current & previous row keys, when they match, the current row is output to the existing output target, when the keys do not match the Transaction Control performs a Commit causing a new transaction to begin, and writes the current row to a new target file.

Dynamic File Name

Informatica 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:
  
  • m_Example_FF_Tricks_1.XML
  • s_m_Example_FF_Tricks_1.XML
  • wf_Example_FF_Tricks.XML
• 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

References

Informatica KB articles 31086-contents of previous row and 31085-Splitting a flat file using a transaction transformation where used as sources for this article

Thank you for your support

or