How to seperate header and Footer from Flat File using Informatica

Scenario:
Source File is flat file which contains Header, Detail and Footer. Scenario is to Load the Header records into Header table ,Detail records into Detail table and Footer records into Footer table.

Source File (Source.txt)
H Company Name : Header
#####################

   Acc_num      Amount
D 123                 200
D 456                 300
D 345                 200

#####################
F No. of records : 3


Solution:

Follow the steps:

1. Import the file.

2. Create a mapping with source as file and Source Qualifier for the file.

3. Connect the Source Qualifier to an Expression Transformation.

4. In Expression Transformation take 1 port as Input port which is File, type of port will be string and length is max length of one row in Source File.

5. In Expression Transformation create three variable ports and three out ports
    v_Header=IIF(SUBSTR(File,1,1)='H',1,0)
    o_Header=v_Header
    v_Footer=IIF(SUBSTR(File,1,1)='F',1,0)
    o_Footer=v_Footer
    v_Detail=IIF(SUBSTR(File,1,1)='D',1,0)
    o_Detail=v_Detail

6. Connect Expression Transformation to a Router Transformation. Router Transformation will have  three groups.
    a. IIF(o_Header=1) -- Pass this group data to target Header Table
    b. IIF(o_Detail=1)  -- Pass this group data to target Detail Table
    c. IIF(o_Footer=1) -- pass this group data to target Footer Table
   
 

Informatica Administrator Training

Day 1 :

00 :  Introduction
 1 : Domain Architecture
 2 : Best Practices
 3 : Installing Informatica 9
 4 : Configuring PowerCenter 9.1 Services

Day 2 :

 5 : Security: Users and Groups
 6 : Security: Privileges and Roles
 7 : Security: Permissions
 8 : PowerCenter Repository and Client Applications Management

Day 3 :

  9 : Web Hub Service Management
 10 : PowerCenter Repository Metadata Deployment
 11 : Command Line Programs
 12 : Subject Area Implementation

Day 4 :

 13 : Metadata Reporting
 14 : Reference Table Management
 15 : Domain Management
 16 : License Administration

Best Practices when building Maps

When you build maps try to use the following steps.
Sometimes this can help clarify "how" to architect the database.  Before beginning ask the following questions:

1. How much data is there passing through this map?
2. What are the source, target, aggregator, and lookup sizes (in terms of Rows and Megabytes of space)?
3. What is the time frame this map is expected to run in?
4. Are we loading data over the network?
5. Are we waiting on a flat file?  More than one?
6. Is there a way to balance the size of the source / target and maybe eliminate the lookups by utilizing Database Routines?
7. Is the speed such a problem that we must eliminate aggregators?
8. Can we tune the SQL on the source qualifier?
9. Do we need to generate new sequence ID numbers for each row?

How to handle apostrophe in Informatica?

In informatica CHR(39) refers ' (apostrope).
Let we want to bring the output as "Tarun's  Blogs" then the variable expression must be defined as below

v_out= 'Tarun'||CHR(39)||'s Blogs' 

Problems in Source Definition field with zero length

If the view field is defined as NULL value then the INFA source definition consider the field width as ZERO .

How this creates problem?

This creates problem while importing the code to another environment.

Solution:
To avoide this problem, Import the code through the LABEL.Copy the whole folder.

How to serach Informatica mappings from meta data table suing Sql Query

SELECT DISTINCT C.SUBJ_NAME, B.MAPPING_NAME FROM
OPB_SUBJECT C, OPB_MAPPING B, OPB_WIDGET_INST A
WHERE C.SUBJ_ID = B.SUBJECT_ID AND B.MAPPING_ID = A.MAPPING_ID
AND upper(A.instance_NAME) LIKE upper('%MAP%')

How to run a single session multiple times in a flow?

 

There is a workflow with three sessions.
S1 ---> S2 ---> S3.
How to repeat 5 successful runs for S2(session 2) without connecting the same
session 5 times in the flow (like S1 then 5 times S2 and then S3)..

session s1-->session s2-->assignment task-->decision task-->cmd task
event wait (file watcher)                                   session s3

1. First time session s2 will be run after session s1 normaly.
2. Assignment task will increase the value of workflow level variable to count run of session s2.
3. Decision task will be used to skip cmd task after 5 successful run of session2 and to run session s3.
4. cmd task will create a touch file (blank file) on some location.
5. Event wait (file watcher) will be used to start session s2 again.
6. if we want 5 times to run session s2 then s3 ,so after 5 times run of session s2, cmd task will be  skipped and session s3 will run this time.