using exec() for running powershell script in BODS 4.1

[Former Member]

Hello,

I am trying to run a powershell script by calling it within BODS script using exec(),

$Time_stamp=exec('PowerShell.exe','\Droptest\ora\test\oracle\cron\etl\scripts\GetTimeStamp.PS1', 8);

print( 'TIME_STAMP-1 IS '||$Time_stamp);

When i run the job, it get hang at this exec function & then I have to kill the job from the "monitor" tab of dataservices.

After killing the job, in the trace log it shows the result for the print as "     99: 20140306001502".

Ideally the script should retun the timestamp "20140306001502". The script file is perfect as when I run this script from the cmd prompt of powershell then it returns the correct value.

So I am facing 2 problems,

1) The job gets hang at the above exec function

2) what is the correct syntax of exec() for running the powershell script so that it returns the correct value without getting hang.

Thanks.

[Former Member]

Hi,

I've got similar problem with  exec() function that would hang when using PowerShell as a command shell. After reading a lot of similar complaints in different environments, I've conconted a Powershell call that does not hang. Out of all options, the most important was "-InputFormat None".

My example includes call of the custom PowerShell script with parameters.

print(exec('PowerShell','-NoLogo -Noninteractive -InputFormat None -NoProfile -ExecutionPolicy Bypass -Command "& \'Q:\DataFiles\dmDSM\Shell\SSAS_ProcessDatabase.ps1\' -serverName \'mssqlnamehere\' -databaseName \'olapdatabasenamehere\'"',8));

Hope this helps.

Thanks,

VD

[Former Member]

Hi Dvn,

I run my powershell scripts a little different.  I use cmd to launch powershell and pass the .ps1 as a parameter.  But I also use the -wait parameter.  DS will kick off the script and keep going, so I had issues with my job as its dependent on the output of the powershell script.  By putting in -wait, the job waits for the script to complete before continuing.  Perhaps this will help with your job hanging.

exec( 'cmd.exe', 'start /wait C:\WINDOWS\System32\WindowsPowerShell\v1.0\powershell.exe -file "E:\\*.ps1 -wait', 8);

[Former Member]

Hello,

Sorry for the late reply, I will surely test the script the way you have suggested.

Thanks for your help & time.

Dvn