Powershell vs Tidal6

[Joe Fletcher]

Hi,

I'm working on automating some excel work using powershell. Environment is TES 6.0.3 on solaris with the agent on a windows7 x64 system.

The job calls a simple one-line bat script which is "powershell -command c:\tidal\autoexcel.ps1".

The script works fine when called from a command window however when TES executes it I get a load of errors which I'm assuming are environment related. In essence it looks like excel can't find a load of embedded functions.

The sample code can be found here. Its the charting of the weather data in Excel section.

http://www.simple-talk.com/dotnet/.net-tools/com-automation-of-office-applications-via-powershell/

I've tried creating an env.bat file to reproduce my command line working environment but it hasn't helped.

Any ideas anyone?

Cheers

Joe

[Jason Javitz]

Have you tried putting this in the Command window?

C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe

and this in the command parameters?

-command c:\tidal\autoexcel.ps1

[Joe Fletcher]

Same output calling it as above.

[Joe Fletcher]

Made some progress but still not totally there yet. Had to apply one mystery fix (see

http://social.technet.microsoft.com/Forums/en/winserverpowershell/thread/875d2056-5005-43a4-aea9-2c9280edb281)

which was to do "mkdir c:\windows\system32\config\systemprofile\desktop".

This  seems to help however I'm then running into assorted bits of windows  "security". For example when executed from a command shell it behaves  fine but from with the TES job it appears that some embedded functions  are blocked. I'm more of a unix person rather than windows so I'm  seeking professional help to find out why.

I've  tested some other basic samples and its consistent insofar as its  always unreliable in an inconsistent way. Same job can fail with  different powershell errors between runs, despite having the same input  data. Most problems seem to be with the internal security model which is not something I'm familiar with.

There's  clearly a lot of potential with Powershell so I think its worth  pursuing but I'm going to need to broaden my skillset quite  considerably.

[Marc Clasby]

We are running 5.31 but ipowershell should behave the same in 6x

Powershell tips

It can be tricky sometimes with powershell (especially gettign remote execution working) but if you are getting security issues you probably are restricted and need "signed" scripts.. Which depending on your policies may be required... but it wasn't worth the pain to our company.

check it by running a powershell command: get-executionpolicy

We set ours to unrestricted (use set-executionpolicy unrestricted)

If you are running 64 Bit you need to do for both  32 bit and 64 bit versions

if you intend to do remoting useEnable-PSRemoting and follow the prompts

Here's an example where we reach out to an application server (FTP server) and trigger it to run an FTP task for us (send a file):

Invoke-Command -computername -scriptblock {"" '-start:""' -p:SourceFileFromAPI: -waitsecs:60 -noxfers:1}

Quotes can be a real pain with how Tidal Agents interprets the string so don't get frustrated if it works via powershell command line but won't work as a job. We discovered a defect in the way tidal handled quotes with powershell and worked with Cisco to put in a fix. we run agent 3.0.2.03 (If you read the readme files you'll find there's a switch:

3.0.2.03

HOT - CSCtv19905 - Fix to BUG00620 (substituting ticks with quotes, compat with old agent) caused
     problems for some customers.
FIX - Added new tagent.ini parameter, substticks=y|n, to control.  Yes is default for compatibility
     with old agent.

Path to Agent install \Agent\bin\tagent.ini

[config]

substticks=n

[TIDAL_AGENT_1]

Other tips

We installed powershell 2.0 on source and target, you need to keep versions consistent

You can run mutiple statements in a job... reducing the number of jobs with a semicolon ; Last statement does not have a semi-colon

Use Write-Host like you would "echo" to insert comments (all are picked up in output)

Exit Codes: you can force and standardize exit codes easily by using exit(###); as one of your statements, which is a cool way to trigger multiple actions like sending different "scenario" emails based on results with just one job, etc)

You can run "agentless" with powershell. How about stopping or starting services remotely?

set-service -computerName Targetserver -name TargetServices -status Running -passthru

We also run all our file operations with powershell (it is much more flexible and reliable that other commands)

We rarely use scipts, we mostly put basic commands or create functions directly in parameters

Function copy-file-force              
{  Bunch of Commands }

copy-file-force  Param1, Param2, etc

powershell in combination with powershell makes a poweful combination for rapid job development.

We have about 4000 powershell jobs defined and active in our environment

[Joe Fletcher]

Thanks for this. I'll try substticks option to see if it helps. I'm concentrating mostly on getting excel to work since that's where the bulk of the work will lie. I'm hoping it will mostly be a case of

open the sheet

run the macro(s)

save/close

If we can get that to work in a stable fashion we'll have a win.

Cheers

[Marc Clasby]

if all else fails you can stick the path to the xls C:\BAT\AUTO_MACRO.xls then call l the bat file on the command line

as long as you excel is set to auto_open() run macro and close when it is done.

We put the Excel local to the agent

it will either always work or hang active (if you didn't safegard for user prompts like save as a target but target exists)..

It is not the best solution but it has worked for me in a pinch

if you need the vb you can do this:

Sub Auto_Open()

' Auto_Open Macro

'

    Application.Run "Macro Name"

    Application.Quit

End Sub

[Brian Dickens]

Can you post the errors that you're seeing?

[jstanlick]

For us to get Powershell to work in Tidal we had to create a command file with the call to the powershell executable and pass the powershell script to the command file. The command file itself is simple:

:: Starts localization of environment variables.

SETLOCAL

powershell.exe -ExecutionPolicy bypass -File %*

:: Ends localization of environment variables.

ENDLOCAL

EXIT %ERRORLEVEL%

To invoke it, your Tidal job simply calls the command file and passes the parameters to it.

powershell.cmd script.ps1