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

Amarnadh G 1 Reputation point
2020-10-11T14:13:17.867+00:00

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!")**

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
Developer technologies Transact-SQL
{count} votes

5 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,926 Reputation points
    2020-10-12T09:08:55.68+00:00

    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.


  2. Amarnadh G 1 Reputation point
    2020-10-14T10:36:43.833+00:00

    32248-image.png

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

    Python Process task configured as below:
    32344-image.png


  3. Tom Phillips 17,771 Reputation points
    2020-10-19T13:25:03.137+00:00
    0 comments No comments

  4. Stefan Hoffmann 621 Reputation points
    2020-10-19T14:02:30.133+00:00

    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.

    0 comments No comments

  5. Amarnadh G 1 Reputation point
    2020-10-23T09:52:55.833+00:00

    @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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.