Call Windows powershell script from tSystem

One Star

Call Windows powershell script from tSystem

Hello,
I need to call an Excel macro from Talend. In order to do that I wrapped my commands in PowerShell script which I can call either from powershell or cmd.
Example below runs fine:
C:\ETL_Talend\macros>cmd /c powershell C:\ETL_Talend\macros\Talend_run_Excel_macro.ps1
However, when I run the same line from Talend's component tSystem I get an error:
***************
Starting job CallExcelMacro at 15:39 08/02/2011.
'powershell' is not recognized as an internal or external command,
operable program or batch file.
connecting to socket on port 3443
connected
disconnected
Job CallExcelMacro ended at 15:39 08/02/2011.
***************
Please help.
Thanks,
Peter.
Community Manager

Re: Call Windows powershell script from tSystem

Hi
Try to add the path of powershell command to system variable PATH or specify the path of powershell command via 'use home directory'.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Call Windows powershell script from tSystem

Thank you Shong.
I added a path to powershell to my system PATH env. variable - I no longer get error I mentioned.
However, job making a call never complets. Only after I kill Talend job Excel macro really runs and does what I expect.
Please help.
Thank you,
Peter.
Community Manager

Re: Call Windows powershell script from tSystem

Hi
Sorry, I don't understand your problem now. If the command works fine on windows cmd, it should work also in tSystem.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Call Windows powershell script from tSystem

That is the problem - tSystem does not work.
It appears that the job is running but never exits and completes. There are no error messages.
I followed sugestion from that post: http://www.talendforge.org/forum/viewtopic.php?id=7810

Please help.
Peter.
Community Manager

Re: Call Windows powershell script from tSystem

Hi
I am not family with Excel macro, there should be a problem in powershell script, maybe emaxt6 can help you. I will notify this post to him.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Call Windows powershell script from tSystem

Hi
There is a problem while executing batch file through Java. If there are multiple line in the batch file, the program just go on and on. It never stops.
You can test this by writing a simple Java program and calling a batch file. This is not an issue of Talend
I have experienced this issue with Java programs although never tried in Talend.
Six Stars

Re: Call Windows powershell script from tSystem

Do you close Excel object ?
Post your script.
One Star

Re: Call Windows powershell script from tSystem

Yes, I close everything and make sure that Excel process is not running. There isknown problem with Excel process not being properly stopped by powershell, I take care of that by simply killing it. Here is my powershell script content:
***********************************
# $excel = new-object -comobject excel.application
$excel = new-object -com Excel.Application
$workbook = $excel.workbooks.open("C:\ETL_Talend\macros\IRI\Test.xlsm")
$excel.Run("TestMacro")
$workbook.Save()
$workbook.Close()
$excel.Quit()
::ReleaseComObject($excel)
Remove-Variable excel
Stop-Process -name EXCEL
*********************************
But it seems the problem is not with Excel macro but with how Talend interacts with Powershell. I wrote a simple powershell script which just executes a few powershell commands - I bump into the same problem - Talend job never exits.
Six Stars

Re: Call Windows powershell script from tSystem

Launch it as a separate process, ie. in tSystem
"cmd /c start powershell -noninteractive -File c:\\temp\\test\\launch.ps1"
One Star

Re: Call Windows powershell script from tSystem

emaxt6, the command you suggested does not work as is. It seems working fine when I remove -File parameter.
"cmd /c start powershell -noninteractive c:\\temp\\test\\launch.ps1"
Thanks a lot for your help!
Peter.
Six Stars

Re: Call Windows powershell script from tSystem

anyway I've opened a bugtraq http://www.talendforge.org/bugs/view.php?id=18916 to let the Talend team investigate...
I think powershell waits on its standard input descriptor from parent job before proceeding ...

bye
One Star

Re: Call Windows powershell script from tSystem

Try using this syntax when laucnhing a powershell from a batch file
echo . | powershell -file tour_ps_file.ps1
One Star

Re: Call Windows powershell script from tSystem

Hi Peter,
As you have experience in powershell and running VB Macro,Can you please write down the steps to create a powershell and calling that in talend(All required steps).
Appreciated your help.
Thanks,
Pranij

Hello,
I need to call an Excel macro from Talend. In order to do that I wrapped my commands in PowerShell script which I can call either from powershell or cmd.
Example below runs fine:
C:\ETL_Talend\macros>cmd /c powershell C:\ETL_Talend\macros\Talend_run_Excel_macro.ps1
However, when I run the same line from Talend's component tSystem I get an error:
***************
Starting job CallExcelMacro at 15:39 08/02/2011.
'powershell' is not recognized as an internal or external command,
operable program or batch file.
connecting to socket on port 3443
connected
disconnected
Job CallExcelMacro ended at 15:39 08/02/2011.
***************
Please help.
Thanks,
Peter.
One Star

Re: Call Windows powershell script from tSystem

Hi,
Iam also trying to run the VBA Macro from talend ....Can Somebody help me with Steps...