Posted by : Arjun Lagisetty Tuesday, September 6, 2016

Recently, I moved to a company which where all the hosts were windows based. So, all my pretty linux scripts were rather unloved. So, I had to dig into powershell and get my hands dirty. Powershell is Awesome.

Requirement:

Run a load plan from remote windows machine (Our enterprise scheduler).
Wait for the load plan to complete.
Based on the output of the load plan, run another job.

Challenge:

As most of you are aware that there is no script provided by Oracle to run a load plan remotely. So, I had to create a script to run load plan. This script had to kick off the load plan and wait for load plan to complete then exit the code. I went with powershell instead of bat because of the many pre-existing cmd-lets I can leverage. 

I use OdiInvoke webservice installed with standalone agent to trigger the load plan and to monitor the status. 

Solution:

Download the script here 


All command should be run in PowerShell NOT cmd

This script is well documented. Documentation can be seen using  the command 

Get-Help .\startlpremote_ps.ps1 -full

Sample command to execute the script.

\startlpremote_ps.ps1 -lp_name LOAD_PLAN_NAME -context GLOBAL -work_repository WORKREP1 -agent_url http://myhost:20910/oraclediagent -odi_user SUPERVISOR -odi_password welcome1 -log_level 5 -synchronous 1 -lp_status_throw_error = 1

Some of the notable features (These help in chaining commands in some external scheduler):
  • Ability to run the command synchronously, i.e. wait for the response of the load plan before exiting the powershell
  • Ability to report Error in the load plan as an error in the powershell.

Note:


This script has been tested in powershell V2 on windows server 2008 and on ODI 11.1.1.6.0 stand alone agent. Since, the commands used are pretty based this should work in other environment with little or not modification. 

Sample run:

  • I created a test load plan called LP_WAIT_3_MINS, which waits only 90 seconds before it errors out. 
  • Run script with parameter synchronous 1  and lp_status_throw_error = 1
Script waits for the Load plan to complete and then throws an error statement. 




Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty