python "the process exit code wass 1 while expected was 0" in SSIS

Question

I have below Python Script which runs good at python IDE and at package level execution but failing at job level even i assigned user role as SQLAgentOperatorrole still failing .

• • Python Script

**import os
import win32com.client as win32
def closeFile():
try:
os.system('TASKKILL /F /IM excel.exe')
except Exception:
print("XlApp Not closed!")

Refresh excel

if os.path.exists("C:\Users\GUNAKALA\Desktop\WorkDay_Daily.xlsm"):
xl = win32.dynamic.Dispatch('Excel.Application')
wb=xl.Workbooks.Open(Filename = "C:\Users\GUNAKALA\Desktop\WorkDay_Daily.xlsm", ReadOnly=1)
xl.Application.Run("Ref_all")
wb.Save()
wb.Close()
#xl.Application.Quit()
closeFile()
del xl

PRINT FINAL COMPLETED MESSAGE

print("Macro refresh completed!")**

Answer 1

@Stefan Hoffmann Thanks for your comment, but
real problem is not about Excel but SSIS can't able to run my python Script at calling itself failed .

Can you provide how to call python from SQL server job agent? Thats all i need.

Answer 2

Running Office automation in a service is not a good idea as Erland already wrote. You need to consider, that this service runs under a Windows account with different permissions than yours, where the Python script works.

As it is a about Excel, look at the Excel Source in SSIS and consider instead of running a macro doing the post-processing in SSIS.

When you run the SSIS account with the appropriate permissions, then you maybe able to run a macro successfully using automation. But some actions in Excel require that Excel is visible (sic.). So you cannot use this as general approach. And you need to code your macro in a way, that it never opens any dialog, e.g. for error messages.

And finally, why do you save the WorkBook after running your macro? You opened it as read-only.
I can imagine that this is part of the problem.

Answer 3

Please see:
https://www.mssqltips.com/sqlservertip/5911/run-python-scripts-in-sql-server-agent/

Specifically "sys.exit(0)"

Answer 4

Problem in above screenshot is at first step Run_Python.Pls help in this regard.

Python Process task configured as below:

Answer 5

Hi @Amarnadh G ,

The following links will be helpful:

How to post argument to python script being called in SSIS Execute Process Task?

SSIS Execute Process Task not running Python script

SSIS Execute Process Task Python script

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.