Scenario 4: How to create Target Files Dynamically.

Scenario 4: How to create Target Files Dynamically.
OR

(How to load all Employees data based upon Deptno in different target file through single Target Instance and Single Mapping.)

Ex: If Deptno=10 then create Target file as DEPT10.txt

       If Deptno=20 then create Target file as DEPT20.txt

       If Deptno=30 then create Target file as DEPT30.txt

Theory/Analysis:

First we need to use FileName column. Then sort data based upon deptno and compare it. If new record deptno and old record deptno is not equal then raise a flag(Condition A). Now we need to group all same deptno altogether so, we need to develop one more condition like if (Condition A) flag is 'Y' and new record deptno and old record deptno is equal then set flag 'Y' (Condition B). Hard code file name like DEPT||DEPTNO||.txt for file name column. In Transaction Control Transformation develop expression like if Condition B is false then TC_Commit_After else TC_Continue_Transaction. (Phewwww :-) I know it's confusing let's do it practically)

Practically:

Step 1

Target Designer

Select Your Target-->Edit-->Column Tab--> In Right Corner you will see an Icon to create FileName Column. (Near Up Arrow/Down Arrow you can see this)

Note: As I am taking Relation Source so, I am overriding SQL in Source Qualifier.
SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, EMP.HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO
FROM  EMP
ORDER BY EMP.DEPTNO
Instead of this you can also use Sorter Transformation

Step 2

 Expression

 Create Six Ports

COMPARE_DEPT (Variable Port) = IIF(DEPTNO  !=  OLD_DEPTNO, DEPTNO, OLD_DEPTNO)

COMPARE_DEPT_TC (Variable Port) = IIF(DEPTNO  !=  OLD_DEPTNO, 1,0)

NEW_DEPT (Variable Port) = IIF(DEPTNO = OLD_DEPTNO AND COMPARE_DEPT_TC=1, 1, 0)

OLD_DEPTNO (Varibale Port) = COMPARE_DEPT

NEXT_FILE (Output Port)=NEW_DEPT

File_Name (Output Port) = 'DEPT'||DEPTNO||'.txt'

Step 3

 Transaction Control Transformation

Transaction Control Condition=IIF(NEXT_FILE=0, TC_COMMIT_AFTER,TC_CONTINUE_TRANSACTION)

BINGO!!!

isn't it simple :-)

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com