Informatica – Loading a Flat File into a target Database

Loading data from a Flat File into the database is a frequent task for all Database/data warehouse developers.
This article details how to do the same using Informatica Powercenter.

Table Creation and Source File.

The following is the script I used to create the table. As I already have an EMP table in my application, I used SCOTT_EMP as the name for this table. If you have worked with Oracle Database before, you might notice this is the default emp table that comes in demobld.sql file.

This is the CSV File that I’ll load into the table above. You can download it if you wish to follow along these steps and load the data yourself.

emp_data

And here are the contents of the file…

As you can see, the fields in the file are delimited by comma (hence CSV file ). The first row in the file has the names of the fields (header) and the actual data starts at row no. 2. If you have a different file format that you are trying to load (with a different delimiter), I’ll let you know in a short while where you can specify that.

Source, Target and Mapping

The first thing that you’d need is to import the source and Target definition.

  1. Once logged into Designer, go to Tools> Source Analyzer
  2. Click Sources> Import from File
  3. Select your file and the following options.
    1. Delimited (we’ll select the delimiter later)
    2. Name for this Source => FF_SCOTT_EMP (FF indicates it is a flat file source, and then the name of the table)
    3. Start import at Row => 1 (Since we have headings at row 1 and data starts at Row 2)
    4. Check import Field Names from First Row. Click Next
    5. Select the appropriate Delimiter (in our case, COMMA) and make sure the data is aligned right In the GRID. Click Next.
    6. Click on each field and make sure you have the datatype and precision selected correctly.
    7. Eg, EMPNO, DEPTNO should be nuemeric, Hire Date should be Date and so on. Click Next and confirm to import the Source Definition.
    8. Go to the properties tab and Change the Date format used to “MM/DD/YYYY” instead of the default format.
    9. Save the changes to the folder (repository -> Save or CONTROL+S).
  4. Once logged into Designer, go to Tools> Target Designer.
  5. Click on targets -> Select import from Database.
  6. Enter your Database Connection Details, Select your table and Click Next.
  7. Your target will be imported. Save the changes
  8. Now that the Source and target have been imported, the next step is to map them.
  9. Click Tools > Mapping Designer, Click on Mappings > Create to create a new mapping.
  10. Let’s name it m_SCOTT_EMP (standard: m_<<Target_Name>>)
  11. Drag and drop the Source from the “FlatFile” group in your folder . Drag and drop the target, Scott_emp into the mapping Designer.
  12. Map the corresponding Fields and Save the changes.

Session and Workflow

Create a session ( Workflow Manager ) using this mapping, say s_m_SCOTT_EMP. Change the following parameters for the session.

  1. Click on Connections tab and select the appropriate connection name for the session. (if you don’t have one, go to Connnections Menu and add the necessary connection).
  2. Include the directory name and the file name in the “Source File Directory” and “Source File Name” under the mapping tab for the source.
  3. In the config Object Tab, set “Number of errors” to 1.

Create a workflow with the start task that links to this session and run the workflow.

Verify the data in the target.

Connect to the Workflow Monitor and Check your workflow’s session log to make sure everything is as expected…More specifically, you should look for the following lines at the end of your session log.

Hope this helps..
Rajesh.