Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
Informatica

Working with Informatica Flatfiles

 
Updated on Sep 30, 2020

In this article series we will try to cover all the possible scenarios related to flatfiles in Informatica.

Let us look into the first requirement

Requirement 1:

Generate respective flatfiles containing employee information for each department. That is, for each and every department we will generate flatfiles in a dynamic manner from our employee database table (we won't have to know how many departments we have beforehand).

  • Select Source Analyzer and Import the Relational database table emp.
  • Next open the Target Designer.Select the EMP table, from the Sources of your Informatica folder found on the Repository Navigator.Just drag and drop the EMP table to the Target Designer. Next Edit the target definition as follows.
  • Rename the Select table to FF_EMP_DEPTNO.
  • Change the Database type to Flat File.
  • Change the desired Flat file information fields. In this case we set it to Delimited.
  • Click on Advanced Flat file information and set the Column Delimiters as ,
  • Next select the Columns tab. On the right-hand corner click the icon Add FileName column to this table. A new port or column FileName with string datatype is created.
Add FileName column target flatfile
Add FileName column target flatfile
  • Apply the above target definition changes.
Target Instance Flatfile Definition
Target Instance Flatfile Definition

So our source table EMP and target flatfile instance FF_EMP_DEPTNO are now ready, so we are good to go to set the mapping logic to generate dynamic flatfiles per department.

Check the Informatica mapping screenshot as below:

Mapping using Transaction Control Tx
Mapping using Transaction Control Tx
  • First of all sort the employee records based on the deptno in the source qualifier.
Source Qualifier SQL Query
Source Qualifier SQL Query
  • Next in the expression transformation we need to flag records based on the change in deptno. In the expression transformation add the following ports:

    - FileName as Output Port
    - COMP_DEPTNO as Variable Port
    - FLAG as Output Port
    - PREV_DEPTNO as Variable Port
Expression Transformation- Change in dept flag
Expression Transformation- Change in dept flag

Now find the expressions for all the above ports of the expression transformation.

FileName:-
'FF_DEPT_' || TO_CHAR(DEPTNO) || '.csv'

COMP_DEPTNO:-
IIF(DEPTNO  PREV_DEPTNO, 1, 0)

FLAG:-
COMP_DEPTNO

PREV_DEPTNO:-
DEPTNO
  • Now find the Transaction Control Condition, based on which the commit is issued to place the end of file for one dept and opens to write new file.
IIF(FLAG = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
Transaction Control Transformation
Transaction Control Transformation

So when there is change in the deptno, then the commit is issued to end the current file and a new file is open to write emp information of new deptno.

Also note that with the introduction of Transaction Control Transformation the Commit Type is set to User Defined.

Session Commit Type- User Defined
Session Commit Type- User Defined

We are done with our first requirement.

Requirement 2:

Now we try the reverse scenario i.e. load the database employee table with the information in the form of flatfiles received from source systems. These flatfiles have employee information based on each department; Also the number of files or departments is not know in Advance. Note, that the part of the flatfile name bears the dept information that also need to be mapped to deptno column of the employee table.

Here we go:

  • Select Source Analyzer to Import FlatFile definition.
  • Select the option Import from File.
  • Next select the file FF_DEPT_10.csv in TgtFiles directory, considering that the file we generated from our previous example will be the source definition in this case.
  • Next select Flat File Type as Delimited.
  • Enter FF_EMP_DEPTNO as the name of this source definition.
  • Select start import at row 1, since we don't have any header record.
  • Next select the delimiter as Comma.
  • Next modify the field names and corresponding datatypes accordingly.
  • Next in the Properties tab check the option Add Currently Processed Flat File Name Port. When this option is selected, a port specifying currently processed file name gets added to the source definition.
Add Currently Processed Flat File Name Port
Add Currently Processed Flat File Name Port
  • Apply the above source definition changes.
Source Flatfile Definition
Source Flatfile Definition

Our Source flat file definition is ready. There are also other easy ways to create this source file definition like export/import and modify emp table or create flatfile option.

  • Next import the EMP table in the Target Designer. Alternatively you can drag and drop the source definition of EMP imported in the earlier example.

So our source flatfile definition FF_EMP_DEPTNO and target table instance EMP are now ready, so we are good to go to set the mapping logic to extract employee information from different department specific flatfiles.

Check the Informatica mapping screenshot as below:

Mapping using Currently Processed FlatFile Name and Indirect Load
Mapping using Currently Processed FlatFile Name and Indirect Load
  • Next in the expression transformation we will extract the deptno information contained in the filename. Also note we have treated hiredate as varchar2(10) in source flatfile definition. Hence we need to convert it to date before loading to EMP target table. In the expression transformation add the following ports:

    - OUT_HIREDATE as Date/Time Output Port
    - DEPTNO as Output Port
Expression Transformation- Extract DEPTNO
Expression Transformation- Extract DEPTNO

Now find the expressions for all the above ports of the expression transformation.

OUT_HIREDATE:-
TO_DATE(HIREDATE, 'MM/DD/YYYY')

DEPTNO:-
TO_DECIMAL(
SUBSTR(CurrentlyProcessedFileName,  
INSTR(CurrentlyProcessedFileName, 'FF_DEPT_', 1)
)
)

Next let us look at Mapping tab of the Session property.

  • Set Source filetype as Indirect.
  • Set Source file directory as $PMTargetFileDir\, as this is currently where our file resides.
  • Set Source filename as FF_EMP_DEPTNO.txt
File Reader Properties
File Reader Properties

Next comes the question of setting the filelist in the Indirect file, from where Integration service will read the files. We can use pre-session command to write the list of files present in the directory in the indirect file.

  • Use a Pre-Session Command to generate the Indirect file with the list of files at runtime.
PreSession Command to create indirect file
PreSession Command to create indirect file

Find below a sample Windows command to do the same

dir /b /on $PMTargetFileDir\FF_DEPT*>$PMTargetFileDir\FF_EMP_DEPTNO.txt

Hence our second requirement is also done.

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles