4

I had been trying to run python from SSIS. SO i needed to create a package in sql server. I can run small scripts in sql server but I am not sure how to run scripts.

Below works. But my python code is in test_db.py How do I run that python script in sql server?

EXEC sp_execute_external_script @language = N'Python', 
@script = N'print(3+4)'

STDOUT message(s) from external script:

7

Hadi
  • 36,233
  • 13
  • 65
  • 124
Doodle
  • 481
  • 2
  • 7
  • 20
  • you could try CLR integration https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-overview?view=sql-server-2017 – Samuel A C Sep 04 '18 at 06:34
  • Does `EXEC sp_execute_external_script @language = N'Python', @script = N'import test_db'` work? Assuming that `test_db.py` is in the same folder as your SQL script – TuanDT Sep 04 '18 at 06:56

2 Answers2

0

If the Python engine is installed on the server where you are trying to run this script, you can use the execute process task and call the python.exe. Pass the .py file as an argument to the task and that will run the script as well.

rvphx
  • 2,324
  • 6
  • 40
  • 69
0

There are two approaches to execute python scripts from SSIS:

(1) Executing Python Script using Execute Process Task

You can use execute the python script from an Execute Process Task to a Flat File then read from the flat file to SQL Server, you can refer to the following link for more information:

(2) Using IronPython

IronPython is an open-source implementation of the Python programming language which is tightly integrated with the .NET Framework. IronPython can use the .NET Framework and Python libraries, and other .NET languages can use Python code just as easily.

You can use a Script Component to integrate IronPython library:

I didn't used this library before and i don't know if i can help. I have read a comment wrote by @billinkc linking to the answer below which contains an amazing guide on how to do that:

References

Hadi
  • 36,233
  • 13
  • 65
  • 124