topic Re: Call Windows powershell script from tSystem in Talend Studio

Call Windows powershell script from tSystem

Anonymous — Sat, 16 Nov 2024 13:06:09 GMT

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.

Re: Call Windows powershell script from tSystem

Anonymous — Wed, 09 Feb 2011 07:04:37 GMT

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

Re: Call Windows powershell script from tSystem

Anonymous — Thu, 10 Feb 2011 23:40:22 GMT

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.

Re: Call Windows powershell script from tSystem

Anonymous — Fri, 11 Feb 2011 01:24:28 GMT

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

Re: Call Windows powershell script from tSystem

Anonymous — Fri, 11 Feb 2011 17:55:44 GMT

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: https://community.talend.com/t5/Design-and-Development/Calling-Excel-Macro-from-Talend-Job/td-p/105758

Please help.
Peter.

Re: Call Windows powershell script from tSystem

Anonymous — Sat, 12 Feb 2011 03:24:28 GMT

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

Re: Call Windows powershell script from tSystem

Anonymous — Sat, 12 Feb 2011 08:39:40 GMT

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.

Re: Call Windows powershell script from tSystem

Anonymous — Sat, 12 Feb 2011 17:13:26 GMT

Do you close Excel object ?
Post your script.

Re: Call Windows powershell script from tSystem

Anonymous — Mon, 14 Feb 2011 08:11:58 GMT

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.

Re: Call Windows powershell script from tSystem

Anonymous — Mon, 14 Feb 2011 11:14:02 GMT

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

Re: Call Windows powershell script from tSystem

Anonymous — Wed, 16 Feb 2011 16:49:03 GMT

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.

Re: Call Windows powershell script from tSystem

Anonymous — Wed, 16 Feb 2011 19:11:44 GMT

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

Re: Call Windows powershell script from tSystem

_AnonymousUser — Wed, 30 Nov 2011 13:48:35 GMT

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

Re: Call Windows powershell script from tSystem

_AnonymousUser — Wed, 14 Nov 2012 15:07:28 GMT

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.

Re: Call Windows powershell script from tSystem

Anonymous — Fri, 23 Nov 2012 10:28:46 GMT

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