Talend community forum / Call Windows powershell script from tSystem

Peter · 2011-02-09 01:16:05

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.
[statistics] connecting to socket on port 3443
[statistics] connected
[statistics] disconnected
Job CallExcelMacro ended at 15:39 08/02/2011. [exit code=0]
***************

Please help.
Thanks,
Peter.

Last edited by Peter (2011-02-09 01:17:50)

shong · 2011-02-09 08:04:37

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

Peter · 2011-02-11 00:40:22

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.

shong · 2011-02-11 02:24:28

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

Peter · 2011-02-11 18:55:44

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.

Last edited by Peter (2011-02-12 01:34:18)

shong · 2011-02-12 04:24:28

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

lijolawrance · 2011-02-12 09:39:40

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.

emaxt6 · 2011-02-12 18:13:26

Do you close Excel object ?

Post your script.

Peter · 2011-02-14 09:11:58

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()
[System.Runtime.Interopservices.Marshal]::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.

emaxt6 · 2011-02-14 12:14:02

Launch it as a separate process, ie. in tSystem

"cmd /c start powershell -noninteractive -File c:\\temp\\test\\launch.ps1"

Peter · 2011-02-16 17:49:03

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.

emaxt6 · 2011-02-16 20:11:44

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

Last edited by emaxt6 (2011-02-16 20:12:00)

PPM · 2011-11-30 14:48:35

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

user1108 · 2012-11-14 16:07:28

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

Peter wrote:
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.
[statistics] connecting to socket on port 3443
[statistics] connected
[statistics] disconnected
Job CallExcelMacro ended at 15:39 08/02/2011. [exit code=0]
***************

Please help.
Thanks,
Peter.

praveen1108 · 2012-11-23 11:28:46

Hi,

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

Announcement

Unanswered posts

#1 2011-02-09 01:16:05

Call Windows powershell script from tSystem

#2 2011-02-09 08:04:37

Re: Call Windows powershell script from tSystem

#3 2011-02-11 00:40:22

Re: Call Windows powershell script from tSystem

#4 2011-02-11 02:24:28

Re: Call Windows powershell script from tSystem

#5 2011-02-11 18:55:44

Re: Call Windows powershell script from tSystem

#6 2011-02-12 04:24:28

Re: Call Windows powershell script from tSystem

#7 2011-02-12 09:39:40

Re: Call Windows powershell script from tSystem

#8 2011-02-12 18:13:26

Re: Call Windows powershell script from tSystem

#9 2011-02-14 09:11:58

Re: Call Windows powershell script from tSystem

#10 2011-02-14 12:14:02

Re: Call Windows powershell script from tSystem

#11 2011-02-16 17:49:03

Re: Call Windows powershell script from tSystem

#12 2011-02-16 20:11:44

Re: Call Windows powershell script from tSystem

#13 2011-11-30 14:48:35

Re: Call Windows powershell script from tSystem

#14 2012-11-14 16:07:28

Re: Call Windows powershell script from tSystem

Peter wrote:

#15 2012-11-23 11:28:46

Re: Call Windows powershell script from tSystem

Board footer