Steps in ETL process in Informatica

1. Creating / Import a Source definition.
1. Select the Shared folder. Right click on it and select open.
2. Shared folder will become bold. It means we are now connected to it.
3. Click on tools-> Source Analyzer
4. Now we will import the source table definitions in shared folder.
5. Click on Source -> Import from database
6. In box displayed, give connection information for source database.
7. Click Connect. Tables in source database will be displayed.
8. Select the tables of use (for e.g., EMP table in Oracle) and click OK.
9. Table definition will be displayed. We can edit it as per need.
2. Creating a Target definition.
1. Connect to the Shared folder. Tools-> Target Designer
2. Now drag the EMP table definition from left side pane to target designer.
3. We will see the EMP table definition in Target Designer.
4. Right click EMP -> Edit -> Click on rename lets say EMP_Tgt
5. Apply -> Ok.
6. Now we will create this table in target database.
7. Click Target -> Select generate/ execute SQL.
8. Click on connect and give login information for target database.
9. Then select the options of table generation.
10. Click Generate/Execute button.
11. Repository -> Save
3. Design a mapping (We can create with or without Transformation)
1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping -> Create -> Give mapping name. For e.g.: m_basic_mapping
4. Drag EMP from source and EMP_Tgt from target in mapping.
5. Link ports from SQ_EMP to EMP_Tgt.
6. Click Mapping -> Validate
7. Repository -> Save
4. Create a session for each mapping.
1. Open Workflow Manager -> Connect to repository
2. Open the folder with same name in which we created mapping. Make sure folder is bold.
3. Now click tool -> Task Developer
4. Click Task -> Create -> Select Session task and give name. For e.g.: s_m_basic_mapping
5. Select the correct mapping from the list displayed.
6. Click Create and done.
7. Now right click session and click edit.
8. Select mapping tab. Go to SQ_EMP in source and give the correct relational connection for it.
9. Do the same for EMP_Tgt.
10. Also for target table, Give Load Type option as Normal and Also select Truncate Target Table Option.
11. Task -> Validate
5. Create a wokflow.
1. Now Click Tools -> Workflow Designer
2. Workflow -> Create -> Give name. For e.g.: wf_basic_mapping
3. Click ok
4. START task will be displayed. It is the starting point for Informatica server.
5. Drag session to workflow.
6. Click Task-> Link Task. Connect START to the session.
7. Click Workflow -> Validate
8. Repository Save.
6. Finally start the workflow.
1. Go back to Workflow Manager. Select the workflow and right click on the workflow wf_basic_mapping.
2. Select Start Workflow.
3. You can view the status in Workflow Monitor.