Want to upload a file to SFTP Server running Python on SQL Server that is installed along with SQL Server 2017

Question

Hi All,
Want to upload a csv file to SFTP Server using Python installed along with SQL Server. I have an SSIS PKG to generate the file on a shared path and then I have to upload the file to SFTP server so that Salesforce process can consume it.

Please help with Python code.

I noticed that If you try to run the below code in SSMS It is throwing an error (Added the error message at the end): I got this code from the Microsoft website (List Python packages): https://learn.microsoft.com/en-us/sql/machine-learning/tutorials/quickstart-python-create-script?view=sql-server-ver15
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pkg_resources
import pandas
dists = [str(d) for d in pkg_resources.working_set]
OutputDataSet = pandas.DataFrame(dists)
'
WITH RESULT SETS(([Package] NVARCHAR(max)))
GO

import pandas are the problem here. It should be import pandas as pd
So correct code is:
--List Python packages
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pkg_resources
import pandas as pd
dists = [str(d) for d in pkg_resources.working_set]
OutputDataSet = pd.DataFrame(dists)
'
WITH RESULT SETS(([Package] NVARCHAR(max)))
GO

Also, these are the Python Packages installed on SQL Server: If I have to install some new packages that are not listed below how should I do this? For example I want to install pysftp or Paramiko. Please help

--List Python packages
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pkg_resources
import pandas as pd
dists = [str(d) for d in pkg_resources.working_set]
OutputDataSet = pd.DataFrame(dists)
'
WITH RESULT SETS(([Package] NVARCHAR(max)))
GO

Package
alabaster 0.7.10
Babel 2.4.0
blaze 0.10.1
bleach 1.5.0
bokeh 0.12.5
Bottleneck 1.2.0
cffi 1.9.1
chest 0.2.3
click 6.7
cloudpickle 0.2.2
colorama 0.3.7
conda 4.3.22
configobj 5.0.6
cryptography 1.7.1
cycler 0.10.0
Cython 0.25.2
cytoolz 0.8.2
dask 0.14.1
datashape 0.5.4
decorator 4.0.11
dill 0.2.5
docutils 0.13.1
entrypoints 0.2.2
et-xmlfile 1.0.1
Flask 0.12.1
Flask-Cors 3.0.2
h5py 2.7.0
HeapDict 1.0.0
html5lib 0.999
idna 2.2
imagesize 0.7.1
ipykernel 4.6.0
ipython 5.3.0
ipython-genutils 0.2.0
ipywidgets 6.0.0
itsdangerous 0.24
jdcal 1.3
Jinja2 2.9.6
jsonschema 2.5.1
jupyter 1.0.0
jupyter-client 5.0.1
jupyter-console 5.1.0
jupyter-core 4.3.0
jupyter-kernel-gateway 2.0.0
llvmlite 0.16.0
locket 0.2.0
lxml 3.7.3
MarkupSafe 0.23
matplotlib 2.0.0
menuinst 1.4.2
microsoftml 1.4.0
mistune 0.7.4
mpmath 0.19
multipledispatch 0.4.9
nbconvert 5.1.1
nbformat 4.3.0
networkx 1.11
nltk 3.2.2
notebook 5.0.0
numba 0.31.0
numexpr 2.6.2
numpy 1.12.1
numpydoc 0.6.0
odo 0.5.0
olefile 0.44
openpyxl 2.4.1
pandas 0.19.2
pandasql 0.7.3
pandas-datareader 0.2.1
pandocfilters 1.4.1
partd 0.3.7
path.py 10.1
pathlib2 2.2.1
patsy 0.4.1
pickleshare 0.7.4
Pillow 4.1.0
pip 9.0.1
prompt-toolkit 1.0.14
psutil 5.2.1
py 1.4.33
pyasn1 0.2.3
pycosat 0.6.1
pycparser 2.17
pycrypto 2.6.1
pycurl 7.43.0
Pygments 2.2.0
pyodbc 4.0.16
pyOpenSSL 16.2.0
pyparsing 2.1.4
pytest 3.0.7
python-dateutil 2.6.0
pytz 2017.2
PyWavelets 0.5.2
pywin32 220
PyYAML 3.12
pyzmq 16.0.2
qtconsole 4.3.0
requests 2.13.0
requests-file 1.4.1
revoscalepy 9.2.0
ruamel-yaml -VERSION
scikit-image 0.13.0
scikit-learn 0.18.1
scipy 0.19.0
seaborn 0.7.1
simplegeneric 0.8.1
six 1.10.0
snowballstemmer 1.2.1
SQLAlchemy 1.1.9
sqlparse 0.1.19
statsmodels 0.8.0
sympy 1.0
tables 3.2.2
testpath 0.3
toolz 0.8.2
tornado 4.4.2
traitlets 4.3.2
unicodecsv 0.14.1
wcwidth 0.1.7
Werkzeug 0.12.1
wheel 0.29.0
widgetsnbextension 2.0.0
win-unicode-console 0.5
xlrd 1.0.0
XlsxWriter 0.9.6
xlwt 1.2.0
sphinx 1.5.4
setuptools 27.2.0

Error Message:

Msg 39004, Level 16, State 20, Line 0
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:

Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "C:\PROGRA~1\MICROS~1\MSSQL1~1.MSS\MSSQL\EXTENS~1\MSSQLSERVER01\9E9B6AD5-3EFC-40D8-8721-6B9E22EA786A\sqlindb.py", line 24, in transform
OutputDataSet = pandas.DataFrame()
UnboundLocalError: local variable 'pandas' referenced before assignment

SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

Completion time: 2021-02-24T15:49:28.0308356+11:00

Answer 1

Fixed it:)

This link will help you how to Change the default R or Python language runtime version on your SQL Box. https://learn.microsoft.com/en-us/sql/machine-learning/install/change-default-language-runtime-version?view=sql-server-2017

The following lists the versions of the R and Python runtime that are included in the different SQL Server versions.

TABLE 1
SQL Server version Service Cumulative Update R runtime versions Python runtime version
SQL Server 2016 R Services RTM - SP2 CU13 3.2.2 Not available
SQL Server 2016 R Services SP2 CU14 and later 3.2.2 and 3.5.2 Not available
SQL Server 2017 Machine Learning Services RTM - CU21 3.3.3 3.5.2
SQL Server 2017 Machine Learning Services CU22 and later 3.3.3 and 3.5.2 3.5.2 and 3.7.2
In my case, I had both 3.5.2 and 3.7.2 and the default was 3.5.2 so 1st I changed the default to 3.7.2 using this CMD commands:

--For example, to configure Python 3.7 as the default version of Python for the instance MSSQLSERVER01 on SQL Server 2017:
cd "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\PYTHON_SERVICES.3.7\Lib\site-packages\revoscalepy\rxLibs"

.\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES.3.7" /instance:MSSQLSERVER01

--For example, to remove the Python 3.5 folder from the instance MSSQLSERVER
.\RegisterRExt.exe /cleanup /python /pythonhome:"C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES" /instance:MSSQLSERVER

and then I ran these:
Run the below 2 commands in CMD (Run as Administrator) on the Machine Where your SQL Server is Installed
For example, if Machine Learning Services was installed using defaults, and machine learning was enabled on the default instance, the path is:

cd "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES.3.7"

Step 2: Install the package using pip

Use the pip installer to install new packages. You can find pip.exe in the Scripts subfolder of the PYTHON_SERVICES folder. SQL Server Setup does not add the Scripts subfolder to the system path, so you must specify the full path, or you can add the Scripts folder to the PATH variable in Windows.

python -m pip install --upgrade pip
scripts\pip.exe install pysftp

and now It is working.

Answer 2

Step 1: Locate the Python library
Run this in SSMS to find the Path where Python is installed on your SQL Server 2017:
--Get location of packages used by Python for SQL Server.
EXEC sp_execute_external_script
@language =N'Python',
@script=N'import sys; print("\n".join(sys.path))'
GO
Run the below 2 commands in CMD (Run as Administrator) on the Machine Where your SQL Server is Installed

For example, if Machine Learning Services was installed using defaults, and machine learning was enabled on the default instance, the path is:

cd "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES"

Step 2: Install the package using pip

Use the pip installer to install new packages. You can find pip.exe in the Scripts subfolder of the PYTHON_SERVICES folder. SQL Server Setup does not add the Scripts subfolder to the system path, so you must specify the full path, or you can add the Scripts folder to the PATH variable in Windows.

scripts\pip.exe install pysftp

You might be prompted to elevate permissions to complete the install. As the installation progresses, you can see status messages in the command prompt window.

https://dzone.com/articles/adding-python-packages-to-sql-server-2017
https://stackoverflow.com/questions/61214462/how-to-install-and-update-python-libraries-into-sql-ml-server-instance
https://sqlundercover.com/2018/09/03/installing-external-modules-into-sql-servers-python/

Answer 3

Step 1: Locate the Python library
Run this in SSMS to find the Path where Python is installed on your SQL Server 2017:
--Get location of packages used by Python for SQL Server.
EXEC sp_execute_external_script
@language =N'Python',
@script=N'import sys; print("\n".join(sys.path))'
GO
Run the below 2 commands in CMD (Run as Administrator) on the Machine Where your SQL Server is Installed
For example, if Machine Learning Services was installed using defaults, and machine learning was enabled on the default instance, the path is:
cd "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES"
Step 2: Install the package using pip
Use the pip installer to install new packages. You can find pip.exe in the Scripts subfolder of the PYTHON_SERVICES folder. SQL Server Setup does not add the Scripts subfolder to the system path, so you must specify the full path, or you can add the Scripts folder to the PATH variable in Windows.
scripts\pip.exe install pysftp
You might be prompted to elevate permissions to complete the install. As the installation progresses, you can see status messages in the command prompt window.
https://stackoverflow.com/questions/61214462/how-to-install-and-update-python-libraries-into-sql-ml-server-instance
https://dzone.com/articles/adding-python-packages-to-sql-server-2017
https://sqlundercover.com/2018/09/03/installing-external-modules-into-sql-servers-python/

It allowed me to install but now getting this error when I try to run a simple:

EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pysftp as pftp
print("My Name")'

Any help appreciated:

Error:
Msg 39004, Level 16, State 20, Line 0
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:

Error in execution. Check the output for more information.
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\paramiko\transport.py:33: CryptographyDeprecationWarning: Python 3.5 support will be dropped in the next release of cryptography. Please upgrade your Python.
from cryptography.hazmat.backends import default_backend
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "C:\SQL-MSSQLSERVER-ExtensibilityData-PY\MSSQLSERVER01\0B77C9DC-6C37-4FEB-8F53-96AE65A1D675\sqlindb.py", line 29, in transform
import pysftp as pd
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pysftp__init__.py", line 12, in <module>
import paramiko
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\paramiko__init__.py", line 22, in <module>

Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
from paramiko.transport import SecurityOptions, Transport
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\paramiko\transport.py", line 129, in <module>
class Transport(threading.Thread, ClosingContextManager):
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\paramiko\transport.py", line 190, in Transport
if KexCurve25519.is_available():
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\paramiko\kex_curve25519.py", line 30, in is_available
X25519PrivateKey.generate()
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\cryptography\hazmat\primitives\asymmetric\x25519.py", line 39, in generate
from cryptography.hazmat.backends.openssl.backend import backend

Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\cryptography\hazmat\backends\openssl__init__.py", line 7, in <module>
from cryptography.hazmat.backends.openssl.backend import backend
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\cryptography\hazmat\backends\openssl\backend.py", line 117, in <module>
from cryptography.hazmat.bindings.openssl import binding
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\cryptography\hazmat\bindings\openssl\binding.py", line 16, in <module>
from cryptography.hazmat.bindings._openssl import ffi, lib
ImportError: DLL load failed: The specified module could not be found.

SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 587, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 358, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

Completion time: 2021-02-24T17:44:54.7788833+11:00

Answer 4

I am confused - if you have an SSIS package that is creating the file, why not use something like WinSCP in the SSIS package to upload files to sFTP? WinSCP includes a .NET provider that can be included in SSIS and you can use a script task to upload.

It seems like a lot of extra work to use SSIS to call into SQL Server - to call out to Python - to upload a file.