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.
Note: This tip is part of a series on administration scripts.
These scripts automate the process to grant permissions to PowerCenter Folders, Connections and Query objects. In my company’s PowerCenter development environment, we allow each developer to access all folders and connection objects. Our security structure is designed so all developers are in a security group, these scripts grant access to the security group.
We use this process because if a developer creates a new folder and does not grant access to the developer security group, other developers will not have access to the folder. We run these scripts daily so new folder, connection and query objects are accessible to everyone.
The Sequence Generator transformation is great sometimes but I cannot find a way to reset the generated sequence based on external conditions. For example, when a value in the current row is different from the value in a previous row, I would like to reset the Sequence number.
Using some information I found on Informatica’s Knowledge base, I created a reusable Expression that will increment or reset a counter (sequence number) based on an input port's value. The counter will increment when a chosen input port's current row is equal to the previous row and reset the counter when the current row's value does not match the value from a previous row.
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.
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:
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:
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.
