Looping in ODI

Scenario:
Generate separate file for each department with list of Employees.

Approach:
1.Create an interface and generate scenario
2.Create procedure for looping for each deparment

Pre-requisites:
1.You need to know how to create the logical and Physical Schema
2.You should aware of the creating project
3.You should aware of how to create model

Execution
1.Creating interface
 (a.)Expand your project and then
              expand the first folder
                     Right click on interface and create new interface
     
 (b.)Enter
              Name : Whatever you want
              Select the Logical schema and context
 (c.)Go to Mapping tab
              Drag and drop the Employees datastore and Departments datastore from Model in Source side.
              Drag and drop the FileDatastore in Target side

        Filter condition Should be department id.
       (DEPARTMENTS.DEPARTMENT_ID = #dept_no)
         
(Note : you can get these tables from HR Schema)
 (d.)Create variable for store dept_no
             Right click on variable
                    Create new variable
                         Name: dept_no

 (e.)Create file datastore as follows

For Resource name we mentioned : Employee_List_#dept_no.csv
Why we are mentioning #variable name at the end means we need to generate different files for different department.

 (f.)Make sure that you need to select corresponding knowledge module
IKM SQL to File Append

(g.)Generate Scenario for the interface
      Right click on interface and select generate Scenario
           It pop up as follows

         Click ok.
2.
(a.)Create the procedure to call the scenario
     Right click on procedure
              Create new Procedure as follows
                 

    Command on target 
    Technology : ODI Tools
  Command:
             OdiStartScen "-SCEN_NAME=EMPLOYEE_LIST" "-SCEN_VERSION=001" "-BLOG.dep_no=#deptNo"
     Command on Source
   Technology : Oracle
   Schema : <logical_schema>

Command:
 SELECT DEPARTMENT_ID  deptNo FROM DEPARTMENTS

here we are passing department id from source to target.
That means we are assigning the value to that particular variable.

3.Create a package
          Declare the variable and procedure drag and drop into that package.
 Package looks as follows:

4.Execute it.

Output: