09-25-2012 06:35 AM - edited 03-01-2019 08:55 AM
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
09-25-2012 12:47 PM
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
09-26-2012 02:02 AM
Same output calling it as above.
10-03-2012 07:45 AM
Made some progress but still not totally there yet. Had to apply one mystery fix (see
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.
10-05-2012 07:04 AM
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
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
10-05-2012 07:24 AM
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
10-05-2012 08:13 AM
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
10-09-2012 09:08 AM
Can you post the errors that you're seeing?
10-19-2012 12:46 PM
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide