none
SSIS Execute Process Task not running Python script RRS feed

  • Question

  • I've searched high and low for a solution and none of them worked. Some of the more common "solutions" are to use a batch file that launches Python and pass the .py script as an argument; launch the Python executable in the Execute Process Task passing the .py script as an argument; check permissions; etc.

    My Python script runs perfectly fine outside of SSIS. Creating a batch file that changes working directory and calls Python with my .py script as an argument runs fine outside of SSIS. Even boiling the task down to a basic command (e.g., mkdir) in a DOS prompt and calling that from the Execute Process Task with cmd.exe doesn't work. It appears that SSIS does not want to execute anything from the Execute Process Task suggesting it's a priv (or some type of configuration) issue, but I can't see how that's possible because I'm trying everything on my local machine where I'm an administrator. I'm not even trying to do anything that accesses network resources; it's all simple local stuff just to see how the Execute Process Task works and then incrementally escalating the complexity after the "plumbing" works, but I can even get the basic plumbing working.

    What is the missing ingredient here to get this Execute Process Task working?

    Thanks in advance.

    Miscellaneous info: Dell Latitude running Windows 10 Pro version 1903; SQL Server 2016 v13.0.4259.0 (X64) running on a Windows 2012 R2 server; Visual Studio Professional 2015 w/SSDT.

    Thursday, September 26, 2019 6:41 PM

Answers

  • I just solved the problem!!! :)

    I started from scratch and created a new project/solution and everything worked the way it's supposed to work. Don't know what was wrong with my other project/solution, but when all fails ... reboot or recreate. Recreating my solution did the trick.

    Thanks for the quick responses folks. It is appreciated.

    P.S. Now that this feature is working for me as expected, I abandoned the intermediate batch file. I'm using my preferred method by calling the Python executable and passing the Python script (.py file) as an argument. Both approaches (cmd.exe w/batch file and Python.exe w/.py script) work in the newly-created project/solution, but I prefer the direct call to Python.
    Friday, September 27, 2019 4:13 PM

All replies

  • HI there,

    1. Please share a screen shot of the SSIS Execute Process Task settings and content of your batch file.
    2. Also, show the same batch file successful execution at the command prompt.

    Thursday, September 26, 2019 6:45 PM
  • Also do share the error message please.


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)

    Thursday, September 26, 2019 7:06 PM
  • Hi The Data Scientist,

    Please refer to the similar issue: SSIS Execute Process Task Python script .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 27, 2019 7:48 AM
  • Mona,
    That's one of the many posts I've read and tried, but it didn't work. Unfortunately, most of the posts on the Internet have not worked for me. I'll post screenshots in support of the other responses above. Thank you.
    Friday, September 27, 2019 1:18 PM
  • Yitzhak,
    As you can see from the DOS window, the batch file executes successfully (and it completes the action I want, which is simply renaming a file at this point). Once I get this Execute Process Task working, I plan on integrating other scripts (Python) into our ETL processes. But, if I can't get it working, then I'll have to resort to other methods of much less interest.


    Friday, September 27, 2019 2:32 PM
  • Vaibhav,
    That's what makes this thing more challenging ... I'm not getting any feedback as to why it's not working. As you can see from the screenshots above, no errors are reported in VS. I'm not a VS expert so there may be somewhere else I can look (like a log file somewhere), but I don't get any immediate feedback within VS to work with. Thanks for any help.

    Friday, September 27, 2019 2:34 PM
  • Hi The Data Scientist,

    Please put your ~\…\*.bat file in the Executable: instead of ~\…\cmd.exe

    Friday, September 27, 2019 3:32 PM
  • Same results: No errors, but didn't do what's in the batch file. :(

    Friday, September 27, 2019 3:37 PM
  • Hi The Data Scientist,

    it is weird.

    You can find me on LinkedIn and Skype.

    Friday, September 27, 2019 3:41 PM
  • I'm glad I'm not alone in thinking this is weird. This is pretty simple stuff for a relatively routine feature (i.e., use a tool for something and call a script outside of the tool to accomplish something else). Easy-peasy, right? I thought so, but after trying multiple things and reading MANY different posts (most of which are just people blindly guessing or repeating what someone else said/wrote), I realized that there's something unusual. It might be a tiny little thing, but I don't have a clue what that tiny little thing is (yet). My best guess is that something inside VS (or SSIS) isn't allowing it to run an external script (like a priv issue). I am an administrator of my own machine and doing everything local (nothing's touching the network/database ... yet) so not sure how that could be the case. When trying (learning) new features, I always start with the basics to understand the capabilities at my disposal and then evolve the solution from there. But I'm stuck at the starting gate on this one.

    Thanks for throwing ideas at me. Keep them coming if you have more... :)
    Friday, September 27, 2019 3:53 PM
  • I just solved the problem!!! :)

    I started from scratch and created a new project/solution and everything worked the way it's supposed to work. Don't know what was wrong with my other project/solution, but when all fails ... reboot or recreate. Recreating my solution did the trick.

    Thanks for the quick responses folks. It is appreciated.

    P.S. Now that this feature is working for me as expected, I abandoned the intermediate batch file. I'm using my preferred method by calling the Python executable and passing the Python script (.py file) as an argument. Both approaches (cmd.exe w/batch file and Python.exe w/.py script) work in the newly-created project/solution, but I prefer the direct call to Python.
    Friday, September 27, 2019 4:13 PM