At Datasource Consulting, we operate with a high level of integrity, an expert level of knowledge, and a relentless focus on delivering value to our clients.

Enjoy the following blog articles and let us know if there are topics which interest you that aren’t found in our articles. We enjoy hearing from our readers.

Using PowerCenter Command-line Tasks: Manipulating Workflows

Posted by Jerry Perez on Apr 25, 2016 1:52:39 PM

PowerCenter Workflows

Command-line tasks are great components that can facilitate reusability, standardization and scalability of processes tied to PowerCenter workflows. Building a library of reusable command tasks to be used throughout a Subject Area can be beneficial for such tasks as managing dependencies, tracking objects or interface/integrate with processes or tools external to PowerCenter.

Whenever possible, research and identify reusable processes best suited for your environment to minimize clutter and one-off processes and maximize reusability. The sample list illustrated on image (A.1) shows three command tasks that use the PowerCenter command-line tools to Start, Schedule and Un-schedule workflows.

PowerCenter Command-line Tasks image 1

The following shows how to create a reusable set of tasks set as pre or post session shell commands in a dummy reusable session that can be added to any workflow within a subject area. This approach is used due to the limitations of using stand-alone command tasks.

Stand-alone command tasks are limited to service, service process, workflow, and worklet variables. Session parameters, mapping parameters, or mapping variables in standalone Command tasks cannot be used. The Integration Service does not expand these types of parameters and variables in standalone Command tasks. With pre/post session command tasks any parameter or variable type defined in the parameter file can be used. (PowerCenter Designer Guide: Tasks > Command Task > Using Parameters and Variables)

Developing the Reusable Process:

PowerCenter Command-line Tasks image 2

Create a dummy mapping whose only purpose is to hold the necessary parameters needed to be passed to the command task. Let’s call this mapping “m_DUMMY” and add a dummy source and target to ensure it’s a valid mapping (A.2).

 

PowerCenter Command-line Tasks image 3

In the mapping’s Parameters and Variables Declaration window add the following Parameters (A.3).

Create a set reusable Sessions that will use as their source mapping the dummy mapping we just created. These Sessions will allow parameters to be passed to them from a parameter file and thus passing them to the command tasks that will be created. Replicating the mapping Session and the naming convention used for each allows a simple way of distinguishing what they do.

PowerCenter Command-line Tasks image 4

Now let’s create a set reusable Command Tasks that will be used by the Sessions created. To make this process as reusable (and secure) as possible, create a set of scripts that will call the PowerCenter PMCMD command instead of writing the commands directly in the Task. This is done for two reasons; first, if these tasks live in multiple Subject Areas and let’s say a change to permissions occurs then each task would have to be changed. If scripts are used instead and such change occurs they could then be modified without touching the Command Tasks. Second, if security is really an issue (it generally is) then the directory where these scripts reside can be secured so that only administrators and the Informatica service account may have access to it. Another way to securely do this may be to create a generic Informatica native account that only has permissions to execute workflows and use it instead of an administrator account.

NOTE: Scripts for this example have been created in the External Procedures directory ($PMExtProcDir). These scripts were written under windows and the values prefixed with a percentage symbol (%1, %2, %3) are parameters passed to them.

 

Script/Task 1: Execute a Workflow (cmd_Informatica_Workflow_Execute)

<Script Code [cmd_Informatica_Workflow_Execute.bat]>
pmcmd startworkflow -sv <IntegrationServiceName>
-d < DomainName >
-u <User>
-p <Password>
-usd <UserSecurityDomainVar>
-f %1 %2

<Task Command>
$PMExtProcDircmd_Informatica_Workflow_Execute.bat $$EXE_FOLDER $$EXE_WORKFLOW

 

Script/Task 2: Schedule a Workflow (cmd_Informatica_Workflow_Schedule)

<Script Code [cmd_Informatica_Workflow_Schedule.bat]>
pmcmd scheduleworkflow -sv <IntegrationServiceName>
-d < DomainName >
-u <User>
-p <Password>
-usd <UserSecurityDomainVar>
-f %1 %2

<Task Command>
$PMExtProcDircmd_Informatica_Workflow_Schedule.bat $$SCH_FOLDER $$SCH_WORKFLOW

 

Script/Task 3: Un-schedule a Workflow (cmd_Informatica_Workflow_Unschedule)

<Script Code [cmd_Informatica_Workflow_Unschedule.bat]>
pmcmd unscheduleworkflow -sv <IntegrationServiceName>
-d < DomainName >
-u <User>
-p <Password>
-usd <UserSecurityDomainVar>
-f %1 %2

<Task Command>
$PMExtProcDircmd_Informatica_Workflow_Unschedule.bat $$SCH_FOLDER $$SCH_WORKFLOW

 

Now that the Scripts and Tasks have been created, open each Session and assing as a Pre (or Post) Session command of “Resusable” type their corresponding Command Task (A.5).

PowerCenter Command-line Tasks image 5

To use this process in a workflow simply add one of the reusable Sessions created and add the necessary parameters in its parameter file.

For example if the Session s_m_cmd_WORKFLOW_EXECUTE is added to Workflow wf_Workflow in Subject Area MASTER_JOBS then the Parameter file entry would look like this:

[MASTER_JOBS.WF:wf_Workflow.ST:s_m_cmd_WORKFLOW_EXECUTE]
$$EXE_FOLDER=<FOLDER_NAME>
$$EXE_WORKFLOW=<WORKFLOW_NAME>

When the Session executed the Command Task will be called to run the script that executes the specified workflow in the parameter file.

Find out more about Informatica PowerCenter.

Topics: Informatica PowerCenter

Written by Jerry Perez