Jeff's Cluttered Mind

Here are some notes on an Informatica PowerCenter 9.6.1 64 bit server install.  This also includes the setup of the Informatica Cloud Secure Agent (64)

Most of the install went smooth.  However I want to list some of the problems I encountered.

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 a VB script for the purpose of converting Excel to CSV.

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 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 reusable email task you can use for the “On Failure E-Mail” property for any Informatica session. If the session fails an email is sent to an address you specify. The content of the email is generic but you get all relative information about the session’s failure, plus the session log as an email attachment.

Download the task and import it into a PowerCenter folder. Next set the “On Failure E-Mail” for a session to "reusable" and chose the email task.

Summary:   This tip will show you how to augment the pmrep command's backup function with environment variables to dynamically create the backup file names.  I've included a visual basic script that can be used to delete old backup files from any folder.  The result is a script you can schedule on your Windows server for unattended repository backups.

• An example backup script and the visual basic script to delete files based on age can be downloaded here.
• To fully utilize my example script you should be using the techniques described in my post about using Server Environment Variables

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.

Informatica can easily output target data in comma separated value format (CSV) but sometimes you require output formatted in native Excel.  Here's a reusable Visual Basic Script that will convert a CSV file into a nicely formatted native Excel file.

The script can be ran individually or as this tip will demonstrate, can be evoked from a PowerCenter Session's "Post-Session Success Command" property.

Here's an overview of what the script can do:

• Adjust width of the columns (auto fit)
• Sets the Header row (row 1) to Bold Font
• Freezes the Header row
• Adds Data Filters to each column of Header Row
• Sets the Header Row Background color to gray
• Saves AS native Excel format into a new file name

The "Dependences" report in PowerCenter is great for showing where objects (especially sources and targets) are used. Recently however, one of my team members almost deleted a Flat File source definition because it showed NO dependencies. After double checking they found where the Flat File object was used as a lookup!!

If you frequently use scripts to run PowerCenter commands pmrep and pmcmd, make sure you are using server environment variables for repetitive command parameters.

Benefits of using environment variables:

• Eliminate the need to change scripts when migrating from test to production.
• Reduce need to change scripts when upgrading to new version of PowerCenter.
• Better security: user id and passwords will not be in clear text. Of course you will want to restrict who can edit the server's environment variables and limit who can place scripts on your server.

If you need a workflow to run every day, but only during working hours, for example you need a workflow to only run if the time is between. 8:00 a.m. to 6:00 p.m., try this technique.

Use a Decision task to compare a workflow's start time to a range of hours/minutes you specify.

The general idea is, using a Decision task you compare the workflow start time with a predetermined time, then within the link to the session that occurs after the decision task,  you evaluate the result of the decision and if it is TRUE, the workflow will continue running, else it will end without error.