Using Excel files as the source for Informatica mappings can be more trouble than it's worth. You have to setup a connection and name a range in the Excel document which can be difficult to maintain. I much prefer to read text or CSV files, they're simpler to work with and less error prone. I created this VB script for the purpose of converting Excel to CSV.Read more »
Here are some SQL scripts you can use to query the repository. Most of these I got from an Informatica employee, others I put together. Use them as a base to create your own queries or reports.
One word of caution, use a profile with READ ONLY permission to access the repository and NEVER update the repository using SQL unless advised to do so by Informatica support.
Here’s the companion script for the “Using pmrep to export an Informatica folder” article. Use this script to import objects into PowerCenter.
One use for scripting an import would be to schedule it during off hours, for example moving development code to production during off hours. You may also need to export some objects, then do a "mass change" or Find/Replace on the XML file, then export them back in (for example, export all sessions, find/replace a connection).
Try this tip for managing PowerCenter connections.
Parametrize connections and use one parameter file to define them. Use the one parameter file in all your workflows. This will bring consistency to development and make it easy when connection changes are required.
Here's a companion tip to the "VBScript to convert CSV to XLS" post.
This script will combine two Excels files into one workbook. The files will be treated as separate worksheets inside the workbook. The worksheet names will be the same as the file names.
You may find this useful when an Inforamtica process outputs multiple flat files and you have converted them into native Excel format but only wish to present one file to the end user (or for archiving or reporting purposes).
Here is a sample mapping to illustrate how the Transaction Control transformation can dynamically create multiple flat file outputs, each with a unique name and data set.
The mapping reads a source table whose key is “Group Id”, and outputs a separate target file for each Group Id data set. The file names are also generated dynamically based on the Group Id.
Informatica allows options for sending email at the session level (on Session Success or Failure) but there is not a standard way to send email from the mapping. By using a web service (written in a third party language such as .Net or Java) to send email, you can generate email from a mapping based on conditional logic and include relevant "pipeline" data in the email body. An additional advantage, if you are using HTML email, you have unlimited options to format the body of the email.
Here is a technique that can be used to unschedule workflows automatically or conditionally. This process uses the pmrep command to unschedule any workflows specified in a CSV file.
I have a copy of this workflow scheduled on our development server using the scheduling option "Run on Integration Service initialization". I do this because any restart of the Informatica service will “re-schedule” unscheduled workflows.
The workflow runs one mapping and calls one command task.
- J. Kinzer
- Tennessee, United States
- I work in Technology for a U.S. company. I currently work with Informatica PowerCenter and Informatica Data Quality in an SAP, Unix, DB2 environment. This blog will mostly contain technical tips and observations about Informatica PowerCenter, DB2 and SQL Server databases. Please comment on any post to share your opinion or feel free to email me.