Problem using SSIS Execute Process task to run Python script which imports pandas

Question

I am trying to execute a python script via the SSIS Execute Process task. After all the usual teething problems and attendent google searches, managed to get a basic script working as follows:-

from tkinter import messagebox
messagebox.showinfo("Title", "Hello from Python script!")

The script is called TestScript.py and runs correctly from the command line. It also runs correctly from within SSIS with the following SSIS setings:-

Execute Process task settings

However, when I add the following line to the script,

  import pandas as pd

it fails with the following error message:-

[Execute Process Task] Error: In Executing "C:\ProgramData\Miniconda3\python.exe" "TestScript.py" at "C:\SFTP_Uploads\TTF_test", The process exit code was "1" while the expected was "0".

Clearly the issue is to do with the pandas import, but I am uncertain as to why this should be when the script runs perfectly from the command line.

Uncertain where to go, and hopefully not an obvious issue. Any suggestions greatly appreciated.

Environment is SQL Server 2016 on Windows Server 2019; Python 3.8.3, Pandas 1.1.3

DavidT · Accepted Answer · 2020-10-28 12:04:22Z

2

A good night's sleep works wonders. Here is how I have solved this problem.

When I stated above that the the scipt was running OK under the commnand prompt, this was actually the Anaconda prompt and not the normal windows CMD prompt.
Running under the normal windows prompt with this command "C:\ProgramData\Miniconda3\python.exe TestScript.py" the program failed with an error message indicating that it couldn't locate pandas
I therefore created a batch file which first of all loads the anaconda prompt and then invokes the script. Here is the batchfile:-

@echo off REM launch the Anaconda prompt and the name of the python program in one go %windir%\System32\cmd.exe /C "C:\ProgramData\Miniconda3\Scripts\activate.bat C:\ProgramData\Miniconda3 && python.exe TestScript.py"

Then supplied the above batch file as the executable in the SSIS task and all works fine

answered Oct 28, 2020 at 12:04

DavidT

313 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DavidT Over a year ago

Further to above, this link adds some helpful information as to why the above workaround was necessary. docs.conda.io/projects/conda/en/latest/user-guide/tasks/…

DataGirl Over a year ago

I'm having the same error, but I'm not using Anaconda. And, where I work, I can't use the command prompt to test the code. :( I'm going to try this out.

Joseph Betts Over a year ago

This was really useful info! Thanks so much! - For me I was using the "mysql-connector" package which wasn't installed when running my program from the command line. To install it correctly I ran from the command line "C:\Python310\python.exe -m pip install mysql-connector", then everything worked a dream executing from SSIS :-)

Collectives™ on Stack Overflow

Problem using SSIS Execute Process task to run Python script which imports pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related