Using pmrep to export an Informatica folder

Sometimes you need to export the contents of a PowerCenter folder for offline storage. This post will show you how to quickly export a PowerCenter folder's contents using the pmrep command's ObjectExport function.

The nice thing about this tip is I give you a script (download here) that will export a folder's contents as individual XML files and export one XML file containing all the folder's contents. This gives you flexibility when you need to import the objects back into the repository, you can pick and choose from the individual files or import all the folder's contents with the one file.


A note on methodology, there are many ways to accomplish this task, the technique I’m going to demonstrate is my preferred method.

This tip assumes you have some experience with scripting on MS Windows Server and you are moderately familiar with the PowerCenter “pmrep” command. I encourage you to further research the pmrep command and its functions, you can find more information in the PowerCenter Command Guide.

Overview of ObjectExport
If you are not familiar with pmrep's ObjectExport function, here's a look at the syntax:

pmrep ObjectExport -n objName -o objType -f FolderName -m -s -b -r -u objName.xml
ObjectExport exports objects by name, to export multiple objects from a folder you can either supply names one at a time, re-executing the command for each name or you can supply an input file containing a list of object names. You also must supply the object name's folder (-f). The last parameter is the output file name.

ObjectExport creates XML that can be imported back into PowerCenter. The switches -m -s -b –r are required to get dependent objects (tables, shortcuts, non-reusable objects) for the object you are exporting. For some objects, if you do not export its dependent objects, it will not re-import into the repository correctly.

Create A List Of Objects
My script uses an input file to supply the list of objects to export. I created the list by executing a PowerCenter Query and filtering on Object type FOLDER and my folder name.

You could also use the pmrep function's ListObjects or ListObjectDependencies to create your input file but I like the format of the output file created by the Query.

To create a query: open PowerCenter Designer (or Repository Manager or any of the tools) and select the menu options: Tools > Queries.

On the resulting dialog, click New to create a new query. For "Query Conditions" fill in the following:
Variable Value
Query Name Folder_Backup
Query Type Shared
Parameter Name Folder
Operator Is Equal To
Value 1 our_folder

Save the Query and run a test to see the results are what you expect.

To save the query results to an output file your ObjectExport script can use, you can manually save the results (File > Export) or execute the query within a script using this syntax:

pmrep  executequery -q %INFA_QUERY_NAME% -t shared -u %OUTPUT_PATH%\export_results.txt
Script
Now that we have a file containing a list of folder contents, it's a simple matter of "reading" the file and executing pmrep ObjectExport for each object name in the file.

Here's the (Windows batch script) syntax for reading the query output and executing pmrep ObjectExport. The pmrep ObjectExport syntax shown below is the same as above except parameter values are used for name, object type and folder.

FOR /F "tokens=2,3,4* delims=, " %%i in (query_results.txt) do 
pmrep ObjectExport -n %%j -o %%k -f %%i -m -s -b -r -u %%j.xml
To output one xml that represents all objects in a folder, change the pmrep ObjectExport –n switch to –i query_results.txt and remove the switches –o and –f

pmrep ObjectExport -i query_results.txt -m -s -b -r -u All_Folder_Objects.xml
The script for this article puts all the pieces together for you, including the a statement for executing the query.

Caveats
This article's script uses the environment variable technique described here. To run the script you would have to implement the env. variables or hard code the values.

Conclusion
I hope you find this tip (and script file) useful. If you have questions or suggestions post them as a comment below.


Thank you for your support
 or  PayPal




IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

41 Response to "Using pmrep to export an Informatica folder"

  1. Jay K 12/28/11, 2:01 AM
    Thanks Jeff for sharing the information. Found it useful :)
  2. Anonymous 12/28/11, 3:54 AM
    Hi Jeff,
    as said "You could also use the pmrep function's ListObjects or ListObjectDependencies to create your input file but I like the format of the output file created by the Query."

    But I could not find an option to write the output of the ListObjects or ListObjectDependencies to a file. Can you share that info?
  3. J. Kinzer 1/4/12, 9:54 PM
    Thanks for the comment! On Windows, you can use the > redirect like this

    pmrep listobjects -f arjun -o workflow> C:\wf.txt

    See the Informatica KB article # 16595 for more on this
  4. Anonymous 1/6/12, 6:15 PM
    Thanks for the helpful post. Do you have something similar to import the XMLs back into the repository?
  5. J. Kinzer 1/7/12, 8:49 AM
    Hello, I do have a script to import the XML back into the repository. I will do a post on that soon, maybe in 3-4 weeks.
  6. Anonymous 1/10/12, 4:51 AM
    Thanks a lot Kinjer, This post is very much helpfull

    Thanks Again,
    Suneel
  7. J. Kinzer 1/29/12, 5:32 PM
    For anyone interested, I just posted the companion script to this, Import objects back into the repository

    http://jeffkinzer.blogspot.com/2012/01/using-pmrep-to-import-informatica.html
  8. Anirudh rathi 4/17/12, 5:53 AM
    a quick question : while migrating whether the objects get reused / replaced? can we have the option to either replace/reuse...if yes how can we do that?
  9. J. Kinzer 4/17/12, 7:17 PM
    Anirudh, when importing objects back into the Repository, you can specify in the 'control file' either to reuse or replace objects, by object type I believe, see this link: http://jeffkinzer.blogspot.com/2012/01/using-pmrep-to-import-informatica.html
  10. Anirudh rathi 4/19/12, 6:31 AM
    This comment has been removed by the author.
  11. Anirudh rathi 4/19/12, 7:13 AM
    This comment has been removed by the author.
  12. Anirudh rathi 4/19/12, 7:17 AM
    Thank you so much for the quick reply.
  13. Anirudh rathi 4/19/12, 9:42 AM
    I have an issue. I was working on deployment group with the help of control file(PMREP).I Am able to migrate objects within folder. How can I deploy across 2 repositories.Can you please share your mail-id so that i can send you the control file am using.
  14. Unknown 6/29/13, 1:37 AM
    Hi Jeff,
    While importing the exported file as described by you , I am getting an error in parsing of xml file . I am using 9x version of informatica .
  15. J. Kinzer 7/4/13, 4:19 PM
    Nimish, email me the error and the file you are importing. Any logs or screen captures you can give me would be most helpful
  16. Unknown 9/5/13, 1:24 AM
    Hi Kinzer,

    I have a folder SALES_INFO. There are 25 workflows in it. But I want to export only 10 workflows. What should be the syntax of pmrep for this scenario? Can I take those 10 names manually into a textfile and supply that textfile as input in pmrep objectexport command ?

    Thanks,
  17. J. Kinzer 9/6/13, 2:52 PM
    Peter, yes, you could do that. One way is to use the suggestion from the article and build a list querying the folder and remove the objects you don't want. Or, find another way to build the query filtering on file name or object type or build the file manually
  18. Fasee 1/27/14, 9:45 PM
    Hi Jeff, the export of tasks like email, command , decision is not happening as it throws a error, can you share the exact pmrep command to export the tasks.
  19. J. Kinzer 1/28/14, 8:30 AM
    Fasee, please send me a screen capture of the error or a log file. Are these Reusable task?
  20. J. Kinzer 1/28/14, 9:28 AM
    Fasee, the script for loop should look like this:

    FOR /F "tokens=2,3,4,5,6* delims=, " %%i in (%OUTPUT_PATH%\query_results.txt) do pmrep ObjectExport -n %%j -o %%k -t %%l -f %%i -m -s -b -r -u %OUTPUT_PATH%\xml\%%j.xml >>%OUTPUT_PATH%\objExport_log.txt

    I will change the download file
  21. Fasee 1/28/14, 9:49 AM
    Thanks lot Jeff, your posts are really helpful :)
  22. Mr. Günther 4/2/14, 5:19 AM
    Hi,
    Thank for this great article, which is very helpful.
    However I have an issue with objectnames containing Æ,Ø and Å (danish letters).
    When exporting to separate files it fails:

    Informatica(r) PMREP, version [9.5.1 HotFix2], build [231.0621], Windows 64-bit
    Copyright (c) Informatica Corporation 1994 - 2013
    All Rights Reserved.
    This Software is protected by U.S. Patent Numbers 5,794,246; 6,014,670; 6,016,501; 6,029,178; 6,032,158; 6,035,307; 6,044,374; 6,092,086; 6,208,990; 6,339,775; 6,640,226; 6,789,096; 6,820,077; 6,823,373; 6,850,947; 6,895,471; 7,117,215; 7,162,643; 7,243,110; 7,254,590; 7,281,001; 7,421,458; 7,496,588; 7,523,121; 7,584,422; 7,676,516; 7,720,842; 7,721,270; and 7,774,791, international Patents and other Patents Pending.

    Invoked at Wed Apr 02 11:03:09 2014

    The source object DB2zOs_FDCT.da0010bp_S93TDã0010 cannot be found. Either the object name is invalid or the object is deleted. If object name is for source object, it should have the dbdname prefix.
    Failed to execute objectexport.

    But creating the AllFolder file works great.

    Any Ideas?

    Kind Regards

    Flemming
  23. Anonymous 4/3/14, 4:21 AM
    Hi Jeff,


    Thanks a lot for the script. however can you kindly send the sample Query used (INFA_QUERY_NAME) for exporting selected objects and also the result of the query. (In this case (query_results.txt). This would be highly helpful for me.


    Email: Menta.VenkataAvinash@cognizant.com
  24. J. Kinzer 4/3/14, 8:24 AM
    Menta, The query is built using Informatica's query builder tool. (located under the Tools > Queries... option on the designer, workflow manager, repository manager, etc.). You can build and run a query to see how it works, then output the results to a text file, this will give you an idea of how this works with the script
  25. Anonymous 4/4/14, 12:21 AM
    Hi Jeff,

    Your solution has worked perfectly fine for me. Thanks a lot for all the help. You were a saviour. Thanks again.

    Regards,
    Avinash
  26. J. Kinzer 4/4/14, 8:34 AM
    Avinash, great to hear. When you just have a few objects you can always build the query file manually. File format is: guid, Folder, obj_name, obj_type, Sub_type, version_number
  27. Anonymous 7/21/14, 4:31 AM
    Hi Jeff. Great articles. Not just this 1, but all of yours that I have gone through. Thanks a lot.

    I am trying to create an export_object script which exports random objects. Eg. If I want to export:
    Workflow A from Folder XYZ
    Workflow B from Folder 123
    Workflow C from Folder 000

    How can I get it done using windows command prompt (unix not present) only?

    The issue I am facing is in creating 'query_results.txt' file without using informatica client at all and containing different workflows in different folders.

    Can you kindly help out?
    My email Id is 'jatin.luthra@outlook.com'.

    Thanks.
  28. J. Kinzer 7/21/14, 7:07 AM
    jatin.luthra@outlook.com, did you try creating the query_results.txt manually with just the objects you need? I think this would work for different libraries.
  29. Anonymous 8/23/16, 5:24 AM
    Can you please share the unix script syntax for reading the query output and executing pmrep ObjectExport .

    Thanks in advance.

    Rajasekhar
  30. J. Kinzer 8/23/16, 10:02 AM
    Sorry Rajasekhar, I do not have a UNIX example. I'm sure it would not be too difficult to replicate. Download the script and ask a UNIX guru to help you translate it. Good luck
  31. Unknown 3/27/17, 8:46 AM
    I am not able to download this migration script. Please help me.
  32. J. Kinzer 3/27/17, 8:57 AM
    To the person that posted "I am not able to download this migration script. Please help me"

    Please give me a name and email address or try a different browser. Also, some companies block the download, try the above download link from outside your company firewall
  33. Unknown 3/28/17, 12:06 AM
    Thanks a lot Kinzer. I am Vikash Chandra and my mail id is tanmay.vikash@gmail.com
  34. J. Kinzer 3/28/17, 7:19 PM
    Vikash, I sent you the script in email but is seems to be blocked by some type of app you have called Boxbe. Please try to download the script from outside your company firewall or from a different browser. The download works for others.
  35. Unknown 3/29/17, 3:21 AM
    Hello Jeff,

    Thanks a lot Jeff, I got a enough visibility from these scripts.
    I gone through the attachment. I have one question- suppose I have one workflow which consist of 10 session, I want to replace three session and remaining 7 I want to reuse. So in that case do I have to create control file manually every time whenever we are this migration .

    Regards
    Vikash
  36. J. Kinzer 3/31/17, 4:57 PM
    Vikash, I'm not sure I know the correct answer to your question. I'm not sure you can specify by session name to replace/reuse.
  37. Unknown 4/17/17, 5:41 AM
    Hi Jeff, can you please help me with unix script for the same. When i am doing it in linux its throwing below error : syntax error at line 8: `tokens=2,3,4* delims=, ' unexpected
  38. J. Kinzer 4/23/17, 5:52 PM
    Jayashree, that part of the script is Windows specific, you would have to use a UNIX equivalent.

    For example replace that with however you would parse a text in UNIX script. Sorry I can't help further, I do not know UNIX scripting.
  39. Anonymous 5/29/17, 2:33 PM
    is there a way to export informatica folder information including the run time paramaters included. just the same as we see in designer.
  40. J. Kinzer 5/29/17, 9:54 PM
    Hello, thank you for the comment/question. I'm not sure I know what you mean by "export informatica folder information including the run time parameters". Can you be more specific?
  41. Anonymous 5/30/17, 7:42 AM
    i would like to export all the folders with embedded/resolved parameters, just the same way as we see in Informatica Designer. is that possible is there a script?

Post a Comment