tSystem component on remote engine does nothing

[hamdi_bourbia]

Hi,

I have an issue with the tSystem component on remote engine. 

My use case is the following : I have to parse an Excel file, but it has a picklist and dynamic cells which update accordingly. So I loop over the picklist values. At each iteration:

1/ I use a tJava to update the picklist pragrammatically and force Excel to update its buffer (cell values) at opening

2/ A 1st tSystem calls a script "command_open.bat" which opens the Excel file

3/ A 2nd tSystem calls a script "command_save_close.bat", which calls a VBA script "CloseExcelWB.vbs" to save the buffer and close the file

You can find the content of these 3 scripts below.

On the studio (Cloud data integration 7.3.1), it works fine. When I parse the data inside the excel file I can see cells are updated. But on the remote engine (v2.10.4), which is on the same machin as the studio, the 2 tSystem components actually do nothing, without error message (TMC logs below as well). So I just have the picklist updated (the tJava part), but neither of the other cells.

I would have liked to attach the picture of my job but it does not accept .pnj, .jpg or .pdf ...  

Thank you for any help!

Best regards,

== command_open.bat :

start "C:/Program Files/Microsoft Office/root/Office16/EXCEL.EXE" "tmp.xlsm"

== command_save_close.bat:

ping localhost -n 5 

"C:/Users/svc_talend/Documents/Distrigo/tool/CloseExcelWB" "tmp.xlsm"

== CloseExcelWB.vbs

Dim objExcel

Dim objWB

dim i

dim WB_To_Close

dim msg

if wscript.arguments.Count = 0 then 

 Call Syntax

 wscript.quit

end if

WB_To_Close = wscript.arguments.item(0)

set objExcel = GetObject(, "Excel.Application")

If UCase(WB_To_Close) = "ALL" then

 Do While ObjExcel.Workbooks.Count > 0

  set objWB = objExcel.Workbooks(1)

  objWB.Save

  objWB.Close

 Loop

Else

 for i = 1 to objExcel.Workbooks.Count

  set objWB = objExcel.Workbooks(i)

  if UCase(objWB.Name) = UCase(WB_To_Close) then

   objWB.Save

   objWB.Close

   exit for

  end if

 next

End If

if objExcel.Workbooks.Count = 0 then 

 objExcel.Quit

end if

wscript.quit

Sub Syntax()

 msg = "Syntax: " 

 msg = msg & vbCrLF & "CloseExcelWB [All | WB Name to Close.xls?]"

 msg = msg & vbCrLF & " the All argument will save and close all WBs"

 msg = msg & vbCrLF & " WB Name is not case sensitive"

 msg = msg & vbCrLF & " Each WB to be closed is saved first"

 msg = msg & vbCrLF & " If no more WBs are in the instance of Excel, then"

 msg = msg & vbCrLF & "  the instance of Excel is closed, otherwise"

 msg = msg & vbCrLF & "  the Excel application is not closed."

 msgbox msg

End Sub

== TMC logs

INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Command to execute: 'cmd /c command_open_sellin.bat'.

INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Setting the parameters.

INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - The command has been executed successfully.

INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Root directory: 'C:/Users/svc_talend/Documents/Distrigo/tmp'.

INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Executing the command.

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Command to execute: 'cmd /c command_save_close_sellin.bat'.

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Setting the parameters.

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Executing the command.

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tLogRow_12 - Content of row 1: 2021-07-30 15:00:39|20210730150025_ZX9P6|20210730150025_ZX9P6|20210730150025_ZX9P6|INSIDBOARD|LoadKPI_distriGO_toPowerSlide_SellIn|Production_8229832789887098276|4|tWarn|tWarn_9|Il repassera par la|42

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Root directory: 'C:/Users/svc_talend/Documents/Distrigo/tmp'.

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tLogRow_12 - Printed row count: 1.

WARN | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tWarn_9 - Message: Il repassera par la. Code: 42

INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - The command has been executed successfully.

[Anonymous]

This sounds like it could be related to the permissions your remote engine is set up with. Take a look at this help article to see if this gives you any ideas....

https://help.talend.com/r/en-US/Cloud/remote-engine-gen2-quick-start-guide/accessing-local-files-from-your-remote-engine

[hamdi_bourbia]

Hi,

Thank you. The solution seems to work with remote engine Gen2, but we have standard remote engine. I don't find the "default" folder in the remote engine installation directory :

[hamdi_bourbia]

here is my remote engine installation directory:

[hamdi_bourbia]

I've tried to find a similar guide to access local files on standard remote engines, without success for now. Should I re-install the remote engine, Gen2 version this time ? I already have some jobs running on it but this can be done ... I need to find a solution quite soon to be able to automate the jobs I have made for a client (theoretical launch day already long passed ...). Best regards

[hamdi_bourbia]

Hi Rhall,

Sorry to insist, but should I expect an answer any time soon please ?

Regards,