working with flat file source_simple

7/28/2019 Working With Flat File Source_Simple

1/13

Working with Flat File Source, LookUp & Filter Transformation

This tutorial shows the process of creating an Informatica PowerCenter mapping and workflow which

pulls data from Flat File data sources and use LookUp and Filter Transformation.

For the demonstration purpose lets consider a flat file with the list of existing and potential

customers. We need to create a mapping which loads only the potential customers but not the

existing customers to a relational target table.

While creating the mapping we will cover the following.

Create a mappingwhich reads from a flat file and creates a relational table consisting of newcustomers

Analyze a fixed width flat fileConfigure a Connected Lookup transformationUse a Filter transformation to exclude records from the pipeline.

I. Connect to the Repository

1. Connect to the repository.2. Open the folder where you need the mapping built.

II. Analyze the source files

1. Import the flat file definition(say Nielsen.dat) into the repository.2. Select SOURCES | IMPORT FROM FILE from the menu.3. Select Nielsen.dat from the source file directory path. Hint : Be sure to set the Files of

type: to All files (*.*) from the pull-down list, before clicking on OK.

1. Set the following options in the Flat File Wizard:
http://www.disoln.org/2012/11/Working-with-Flat-File-Source-LookUp-Filter-Transformation.htmlhttp://www.disoln.org/2012/11/Working-with-Flat-File-Source-LookUp-Filter-Transformation.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.htmlhttp://www.disoln.org/2012/07/understand-powercenter-source-analyzer.htmlhttp://www.disoln.org/2012/07/understand-powercenter-source-analyzer.htmlhttp://www.disoln.org/2012/07/understand-powercenter-source-analyzer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.htmlhttp://www.disoln.org/2012/11/Working-with-Flat-File-Source-LookUp-Filter-Transformation.html


2/13

2. Select Fixed Width and check the Import field names from first line box. Thisoption will extract the field names from the first record in the file.

3. Create a break line or separator between the fields.4. Click on NEXT to continue.


3/13

5. Refer Appendix A to see the structure of NIELSEN.DAT flat file.

4. Change field name St to State and Code to Postal_Code. Note : The physical data file willbe present on the Server. At runtime, when the Server is ready to process the data (which

is now defined by this new source definition called Nielsen.dat) it will look for the flat file

that contains the data in Nielsen.dat.

5. Click Finish.6. Name the new source definition NIELSEN. This is the name that will appear as metadata

in the repository, for the source definition.

III. Design the Target Schema

Assumption: The target table does not exist in the database

1. Switch to Target Designer.2. Select EDIT | CLEAR if necessary to clear the workspace. Any objects you clear from the

workspace will still be available for use in Designers Navigator Window, in the Targets

node.
http://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Target-Designer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Target-Designer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Target-Designer.html


4/13

3. Drag the NIELSEN source definition from the Navigator Window into the workspace toautomatically create a target table definition. You have just created a target definition

based on the structure of the source file definition. You now need to edit the target table

definition.

4. Rename the table as Tgt_New_Cust_x.

5. Enter the field names as mentioned in the Figure below .Change the Key Type forCustomer_ID to Primary Key. The Not Null option will automatically be checked. Save the

repository.
http://www.disoln.org/2012/07/understand-powercenter-source-analyzer.htmlhttp://www.disoln.org/2012/07/understand-powercenter-source-analyzer.htmlhttp://www.disoln.org/2012/07/understand-powercenter-source-analyzer.html


5/13

6. The target table definition should look like this

7. Create the physical table in the Oracle Database so that you can load data. Hint : Fromthe Edit table properties in Target designer, change the database type to Oracle.

IV. Create the mapping and drag the Source and Target

1. Create a new mappingwith the name M_New_Customer_x2. Drag the source into the Mapping Designer workspace. The SourceQualifier should be

automatically created.
http://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.htmlhttp://www.disoln.org/2012/08/Understand-Informatica-PowerCenter-Mapping-Designer.html


6/13

3. Rename the Source Qualifier as SQ_NIELSEN_x4. Drag the target (Tgt_New_Cust_x) into the Mapping Designer workspace

V. Create a Lookup Transformation

1. Select TRANSFORMATION | CREATE.2. Select Lookup from the pull-down list.3. Name the new Lookup transformation Lkp_New_Customer_x.

4. You need to identify the Lookup table in the Lookup transformation. Use theCUSTOMERS table from the source database to serve as the Lookup table and import it

from the database.

5. Select Import to import the Lookup table.

6. Enter the ODBC Data Source, Username, Owner name, and Password for the SourceDatabase and Connect.

7. In the Select Tables box, expand the owner name until you see a TABLES listing.8. Select the CUSTOMERS table.


7/13

9. Click OK.10.Click Done to close the Create Transformation dialog box.

Note : All the columns

from the CUSTOMERS table are seen in the transformation.

11.Create an input-only port in Lkp_New_Customer_x to hold the Customer_Id value, comingfrom SQ_NIELSEN_x .

1. Highlight the Cust_Id column from the SQ_NIELSEN_x2.

Drag/drop it to Lkp_New_Customer_x.

3. Double-click on Lkp_New_Customer_x to edit the Lookup transformation.4. Click the Ports tab, make Cust_Id an input-only port.


8/13

5. Make CUSTOMER_Id a lookup and output port.

12.Create the condition for lookup.1. Click the Condition Tab.2. Click on the icon.3. Add the lookup condition: CUSTOMER_ID = Cust_Id.

Note: Informatica takes its best guess at the lookup condition you intend, based

on data type and precision of the ports now in the Lookup transformation.

13.Click the Properties tab.


9/13

14.At line 6 as shown in the figure below, note the Connection Information.

VI. Create a Filter Transformation

1. Create a Filter transformation that will filter through those records that do not match thelookup condition and name it Fil_New_Cust_x.

2. Drag all the ports from Source Qualifier to the new Filter. The next step is to create aninput-only port to hold the result of the lookup.

3. Highlight the CUSTOMER_ID port from Lkp_New_Customer_x .4. Drag it to an empty port in Fil_New_Cust_x .5. Double-click Fil_New_Cust_x to edit the filter.6. Click the Properties tab.7. Enter the filter condition: ISNULL(CUSTOMER_ID). This condition will allow only those

records whose value for CUSTOMER_ID is = null, to pass through the filter.

8. Click OK twice to exit the transformation.9. Link all ports except CUSTOMER_ID from the Filter to the Target table.

Hint : Select the LAYOUT | AUTOLINK menu options, or right-click in the workspace

background, and choose Auto link. In the Auto link box, select the Name radio button. This


10/13

will link the corresponding columns based on their names.

10.Click OK.11.Save the repository.12.Check the Output window to verify that the mapping is valid.13.Given below is the final mapping.

VII. Create the Workflow and Set Session Tasks Properties

1. Launch theWorkflow Managerand connect to the repository.2. Select your folder.3. Select WORKFLOWS | CREATE to create a Workflow as wf_New_Customer_x.4. Select TASKS | CREATE to d create a Session Task as s_New_Customer_x.5. Select the M_New_Customer_x mapping.
http://www.disoln.org/2012/09/understand-informatica-powercenter-Workflow-Designer.htmlhttp://www.disoln.org/2012/09/understand-informatica-powercenter-Workflow-Designer.htmlhttp://www.disoln.org/2012/09/understand-informatica-powercenter-Workflow-Designer.htmlhttp://www.disoln.org/2012/09/understand-informatica-powercenter-Workflow-Designer.html


11/13

6. Set the following options in the Session Edit Task:1. Select the Properties tab. Leave all defaults.

7. Select the Mapping tab.1. Select the Source folder. On the right hand side, under Properties, verify the

attribute settings are set to the following:

1. Source Directory path = $PMSourceFileDir\2. File Name = Nielsen.dat (Use the same case as that present on the server)3. Source Type: Direct

Note : For the session you are creating, the Server needs the exact path,

file name and extension for the file as it resides on the Server, to use at

run time

2. Click on the Set File Properties button.


12/13

3. Click on Advanced.

4. Check the Line sequential file format check box.

5. Select the Targets folder.1. Under Connections on the right hand side, Select the value of

Target Relational Database Connection.

6. In the Transformations Folder, Select the Lkp_New_Customer transformation.1. On the right hand side, in Connections, Select the Relational Database

Connection for the Lookup Table. Figure


13/13

8. Run the Workflow.9. Monitor the Workflow.10.View the Session Details and Session Log.11.Verify the Results from the target table by running the query SELECT * FROM

Tgt_New_Cust_x;
http://www.disoln.org/2012/09/Understand-Informatica-PowerCenter-Workflow-Monitor.htmlhttp://www.disoln.org/2012/09/Understand-Informatica-PowerCenter-Workflow-Monitor.htmlhttp://www.disoln.org/2012/09/Understand-Informatica-PowerCenter-Workflow-Monitor.html

working with flat file source_simple

Documents