Jeff's Cluttered Mind

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.

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.

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