Verify Informatica objects properties with MS Excel accurately

Today we will discuss some trick or tip...whatever you call it...But this will really helps a lot.

 People who works on Informatica might come across this situation frequently. Just spend some time on this post to gain knowledge. This will really helps you a lot.

Issue: I have developed 100 mappings for a project requirement. This includes, 

100 mappings design -200 sessions( One for incremental and one for Full load) - 200 workflow.

 Before moving to Testing phase, I need to verify the above developed objects accurately.

Verification includes the below.

1) Mapping Level: 

    a) Need to verify "sql override"
    b) Mapping name according my project naming convention.

2) Session Level:

    a) Incremental and full load session naming convention.
    b) Session Log File Name
    c) Parameter File name
    d) Recovery Strategy
    e) DTM buffer size
    f) Default buffer block size
    g) Source and Target connection values
    h) Target load type
    i) Truncate target table option
    j) Reject file name

3) Workflow Level:

   a) Failure strategy 

      - Fail parent if this task fails
      - Fail parent if this task does not run

I was worried on how to verify these many options individually for each and every objects.

After spending some time found some clues to do it quickly. This saves me lot of time and was so happy after this.

Solution: All just we need to open the exported xml using Ms-Excel and create report on the Informatica objects.

Follow the below steps to verify your informatica objects.

Step 1: Connect to Repository manager, open the folder and select your workflows and export it.

Step 2: Right click on file and select 

"Open with" -> "Microsoft Office Excel" 

Note: Before opening, remove the below line from the XML document.

<!DOCTYPE POWERMART SYSTEM "powrmart.dtd">

Step 3: Now the excel popups a window saying select option. Just select last option "Use the XML Source task pane".

Step 4: All elements existing XML file be displayed as Tree structure at right pane shown as below.

Step 5: Now we are almost done. All just we need to know intelligently to select the XML elements. Basically XML document contains every information. As shown in the below image, I am going to select some session properties.

Step 6: Place your cursor in the first cell and double click the desired XML tag in the Right pane. Repeat the same steps for other Tags.

Step 7: Finally Click on "Refresh All" button in the Data tab of Excel.

Step 8: This will fetch complete information based on the selected as follows. Apply filter on the properties column and select the properties you want to verify.

 Here I am selecting "Source connection value","Target connection value","Session Log file name".

Step 9: Finally you will get the report as show below.

Cool trick right..!!! 

When you have bunch of objects to verify the properties, this option will help you a lot.

CHEERS :)