How to run Python in SSIS!

Learn how to run a Python Script in SSIS using the Execute Process Task component.

►► The Starter Guide for The Modern Data Stack (Free PDF)

Timestamps:
0:00 - Intro
0:05 Review Python script
0:33 Using Execute Process Task
0:55 Configuration
2:04 Using variables

Title & Tags:
Run a Python Script using Execute Process Task | SQL Server Integration Services (SSIS) Tutorial
#kahandatasolutions #sqlserverintegrationservices #dataengineering

[KahanDataSolutions]

Simplify “modern” architectures + better understand common tools & components

[SuperKerr]

Excellent video! Very precise and well explained. Thanks!

[mwredfern]

great video. I would just note that your local machine holding the python app and py's will not run correct, if your machine is asleep.

to get around this, i have the python exe and py's and the ssis jobs loaded on a remote server, that never sleeps. now i can set the job schedule for times outside the 9-5 m-f timeframes.

thanks the great video!!

[gabrielsantiagoarnesano]

wow, really cool and well explained. is it possible to run a python script and return maybe some variables from the program (like a dataframe maybe?) while is running? or i need to generate an output like a csv and then manipulate it? thanksss

[marcusvincent]

How about if I want an if else statement to raise Exception? Is there a way to get the output of the python script into a variable? Then pass as a constraint to the next process?

[elen]

This good video, just add that you must give permissions to the SQL agent to the folders where is the executable and the file.

On the other hand, I get the same error " The process exit code was "1" while the expected was "0"."

[amitdighe]

Thanks for this video, how do we access a variable passed from Execute Process Task into actual Python script?

[dhirajr]

Hi thanks for the vid. Does this also work when scheduling within sql agent? If i try this it hangs

[amritakumari]

Thanks, for the video. I tried scheduling the a python script in the SQL server 2014. It ran successfully in SSIS. When I scheduled it, it's running successfully but not performing anything in the python script. Any help would be appreciated. Also, If there a syntax to run directly from SQL Agent job via cmdexec will be helpful. Hoping for prompt response.

[cedricdubreuil]

Thanks ! Does anyone have a link to learn how to pass data to & from this process task please ?

[rmontenaro]

He uses the Variables in running time. What we can not do is let the field empty, like you did at "bla bla bla.py" and when you run it the expressions get the variables valor.

[maxpower]

[Execute Process Task] Error: In Executing "File.py" at "C:\Folder", The process exit code was "1" while the expected was "0".

This is driving me nuts

[taiphan]

I run fine in SSIS with python simple python scripts. but when i use import pyodbc on the script file it gave me an error with

[Execute Process Task] Error: In Executing "C:\Program Files (x86)\Python39-32\python.exe" "LoadingScripts.py" at "D:\MyDir\Testprocess", The process exit code was "1" while the expected was "0".


here is my script file
import sys, os
import pyodbc

print("Testd")


can you help?

[gustavoleo]

Thanks for sharing!

Fantastic!

By chance do you think it's possible to use on python package as a source?
DBF files are terrible to integrate, for example instead of using the outdated Visual FoxPro OLEDB

dst = r'D:\CIDADE.DBF'
from dbfread import DBF

from pandas import DataFrame

dbf = DBF(dst)

frame = DataFrame(iter(dbf))



print(frame)

Thanks in advance!