Thursday, March 14, 2013

How to load the name of the current processing flat file along with the data into the target using informatica mapping?

We will create a simple pass through mapping to load the data and "file name" from a flat file into the target. Assume that we have a source file "customers" and want to load this data into the target "customers_tgt". The structures of source and target are

Source file name: customers.dat

Target: Customers_TBL

The steps involved are:
  • Login to the powercenter mapping designer and go to the source analyzer.
  • You can create the flat file or import the flat file.
  • Once you created a flat file, edit the source and go to the properties tab. Check the option "Add Currently Processed Flat File Name Port". This option is shown in the below image.

  • A new port, "CurrentlyProcessedFileName" is created in the ports tab.
  • Now go to the Target Designer or Warehouse Designer and create or import the target definition. Create a "Filename" port in the target.
  • Go to the Mapping designer tab and create new mapping.
  • Drag the source and target into the mapping. Connect the appropriate ports of source qualifier transformation to the target.
  • Now create a workflow and session. Edit the session and enter the appropriate values for source and target connections.
  • The mapping flow is shown in the below image

The loading of the filename works for both Direct and Indirect Source filetype. After running the workflow, the data and the filename will be loaded in to the target. The important point to note is the complete path of the file will be loaded into the target. This means that the directory path and the filename will be loaded(example: /informatica/9.1/SrcFiles/Customers.dat). 

If you don’t want the directory path and just want the filename to be loaded in to the target, then follow the below steps:
  • Create an expression transformation and drag the ports of source qualifier transformation into it.
  • Edit the expression transformation, go to the ports tab, create an output port and assign the below expression to it.

     INSTR(REVERSE(CurrentlyProcessedFileName), '/') - 1

  • Now connect the appropriate ports of expression transformation to the target definition.

No comments:

Post a Comment