Sunday 2 March 2014

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

15 comments:

  1. Hi harsha...here in the step 3, that is Transaction control Tr....How you have assigned NEXT_File=0, as this is variable port..how u can call in TCT? I am not able to do this? Can u please explain me

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  2. I am extremely sorry Lakshman Reddy.

    I usualy do proof reading after publishing but this error I missed :-(

    NEXT_FILE is an Output Port it's not a Variable Port. We are using NEXT_FILE port to take the value of NEW_DEPT port which is variable port.

    Convert NEXT_FILE to Output Port and you are good to go..

    Let me know if you are still getting any error.

    It's a request to all my dear readers, If you found any error or my solution does not work please let me know. I will rectify those errors immediately.

    Thanks once again.

    ReplyDelete
  3. Hi Harsha,
    Thank you for your valuable reply, I got the output but here i have a problem....In DEPT10.txt.20 or 30 only one employee details are being displayed not all the employees who are in dept 10,20 or 30 are displaying....Can i know wt error i may have done?

    Thank You in Advance

    ReplyDelete
    Replies
    1. According to Error: Mistake could be at Transaction Control Transformation, instead of writing NEXT_FILE=0 may be you have written as NEXT_FILE=1.

      Kindly verify it and let me know if its still not working. I will send .xml file for you which you can Import in your repository and analyse. Kindly drop me a mail @ hpandey.harsh@gmail.com if you are still getting error.
      Thanks

      Delete
  4. Hi Harsh,
    I am trying to load data via simple mapping.The source has 14 records.
    SQ is nt reading any records at all bcz of which no transformation is happening and no records in target..what can be the possible reason behind this..please help.

    ReplyDelete
    Replies
    1. Hi, Sumit
      Are you getting error as well? Check you logs for error.

      If your connection is pointing to right schema and you have table as well then it should read the data.

      Second Check: Have you override your default SQL in Source Qualifier, If yes then have you put any condition as well. Copy the Query run it at database directly.. are you able to fetch the data?

      Hope this two check list will be helpful. If not then could you please send me you mapping I will look into it and send you back after correction, if it is required.

      Thanks.

      Delete
  5. Thanks for the suggestion Harsh.
    No i am not getting any error.
    I have a query.In connection details at session level, do i need to provide User DSN values or the one i created in relational connection?
    I have attached the session log and problem statement on informatica communities page.
    Request you to please check:
    https://community.informatica.com/message/108751#108751

    ReplyDelete
    Replies
    1. Hi, Sumit
      DSN connection is used to read metadata whereas Relational Connection is used to read/load actual Data. You have to use relational connection at Session level.

      After seeing your log now only one possibility... Whatever relational connection you are using in that schema also source table is there but there won't be any data over there. Verify your Relational Connection and make sure those are pointing to correct schema. Second Check.. Try with load type NORMAL.... (Have you tried creating another mapping with same source and target and logic?) If not then give a try..

      Delete
  6. Hi Harsh

    how are the files name gettins assigned to the text files , are you assigning the names to any parameter and same is passed as name in session level ?

    ReplyDelete
    Replies
    1. Hi, Neer

      As it's a blog for learning with practical approach so, please always try to implement it practically. you will have minimum doubt. (SORRY FOR HARD WORDS IF ANY)

      Now Answer for you question... I am not passing Target file name through parameter. Just read STEP 1 and STEP 2 and implement it.... you will have idea how we are passing Target File name.

      Feel free to mail me if you stuck in implementation.

      Thanks

      Delete
  7. i am using the same code and a java transformation to generate the lines without any space between the records. workflow monitor shows source 20 target 20 rejected 0. but when i open the out file it has only 4 records. there is a expression between tc and target and session log shows 20 records in that expression . still only 4 records inserted.

    ReplyDelete
    Replies
    1. Your error point is ""there is a expression between tc and target"""

      There should not be any transformation between TARGET and TC.

      Delete
  8. Hi Harsha,
    Can u please send the .xml file for these scinaro at b.radharapu@gmail.com.
    thanks in advance

    ReplyDelete
  9. Our Informatica Trainers are working professionals with minimum 10+ Years of Expertise in Informatica Domain and provide training with real-time projects.
    For More Info: Informatica Training in Gurgaon

    ReplyDelete