Ways to download file from FTP using TSQL

Prolems\Concerns

There are many many ways to play with FTP download, but when coming to TSQL level, it’s kind of “a tiny wave” in the brain that how to do it natively.

No, no native option existing in TSQL. But, ways to do it by TSQL code, there are many ones:

• WinSCP commands
• Batch script (cmd, powershell)
• Python code?
• …

Let’s exploring them…

T-sql-and-FTP-files?

P/s: I won’t talk about the option using CLR functions as I don’t like it much.

Solution

In this post, to make it easy, we will use the simplest FTP server — that you can quickly setup with using FileZilla Server.

Before we begin, let’s create a server and some files in there:

• Create folder named as “FTP” and put some “dummy” files in

Let’s say that somebody has already uploaded some SQL files and asked me to download them, to then apply them to a DEV database:

local-ftp-folder-with-dummy-files

• Install Filezilla server and open its interface — FileZilla Server Interface, then click to create FTP user:

user: ftpuser
password: ftpuser

filezilla-server-create-a-user

filezilla-server-create-a-user-general-menu

filezilla-server-create-a-user-shared-folders-menu

• Open WinSCP to try connecting to FTP server

ftp-session-info

ftp-connected

BEGIN…

Let’s say we will download above files to local folder named C:\Temp\

Way 1 — using WinSCP

Why using this? — This is a very recommended one, with all supports for FTP, SFTP, FTPs, etc.

Documentation: https://winscp.net/eng/docs/start

Playing:

Need preparing following parameters:

• Local path to winscp.com executable, named as WinSCPFolder

Normally it will be under:

C:\Program Files (x86)\WinSCP\
or C:\Program Files\WinSCP\

• FTP host, named as FtpHost = localhost
• FTP user and password, named as FtpUser = ftpuser, FtpPassword = ftpuser
• FTP folder, named as FtpFolder = /, which is root folder
• FTP file mask, named as FtpFileMask= *.sql, which is to download all files with extension of .sql
• Local folder, named as LocalFolder = C:\Temp, which is the location where downloading files to.
• Log folder (optional), named as LogFolder = LocalFolder by default
• Log file — calculated value, named as LogFilePath = LogFolder + FtpDownload_log_yyyyMMdd_HHmmss.log, name it as your way

OK, go to build the command:

cd “LocalFolder”
“WinSCPFolderWinSCP.com” /log=”LogFilePath” /command
    “open ftp://FTPUser:FTPPassword@FTPHost"
    “get “”FTPFolder*”” -filemask=FTPFileMask”
    “exit”

Our sample will produce result below (just use & to let all be in 1 line):

cd “C:\Temp\” & “C:\Program Files (x86)\WinSCP\WinSCP.com” /log=”C:\Temp\FtpDownload_log__20200614_114342.log” /command “open ftp://ftpuser:ftpuser@localhost" “get “”/*”” -filemask=*.sql” “exit”

Run in command prompt to verify:

winscp-ftp-download-command-line

Good? Then put it in SQL:

DECLARE @vCommand nvarchar(4000) 
SET @vCommand = ‘cd “C:\Temp\” & “C:\Program Files (x86)\WinSCP\WinSCP.com” /log=”C:\Temp\FtpDownload_log__20200614_114342.log” /command “open ftp://ftpuser:ftpuser@localhost" “get “”/*”” -filemask=*.sql” “exit”’
EXEC xp_cmdshell @vCommand--, no_output

And result:

winscp-ftp-download-sql

winscp-ui-ftp-download-sql

Review the log file to see details: FtpDownload_log__20200614_114342.log

Done. That’s it! Go build a SQL wrapper, or go use FtpDownload-WinSCP.sql as reference.

Way 2 — using (native) cmd shell

Why using this? — We should use it for the simple cases with FTP server only. For SFTP, we have another option here which won’t talk about in this post.

Documentation: https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/ftp

Playing:

Preparing parameters:

• FTP host, named as FtpHost = localhost
• FTP user and password, named as FtpUser = ftpuser, FtpPassword = ftpuser
• FTP folder, named as FtpFolder = /, which is root folder
• FTP exact file name, named as FtpFileName= Countries-20200614–090721.sql
• Local folder, named as LocalFolder = C:\Temp\, which is the location where downloading files to.

OK, go to build the script file: C:\Temp\FtpDownload_RUN.txt

open FTPHost
FTPUser
FTPPassword
get "FTPFolder/FTPFileName" "LocalFolder\FTPFileName" 
quit

Our sample will produce result below:

open localhost 
ftpuser
ftpuser
get "/Countries-20200614-090721.sql" "C:\Temp\Countries-20200614-090721.sql" 
quit

Run in command prompt to verify:

ftp -s:”C:\Temp\FtpDownload_RUN.txt”

cmd-shell-ftp-download-command-line

Go SQL:

DECLARE @vCommand nvarchar(4000)
SET @vCommand = ‘ftp -s:”C:\Temp\FtpDownload_RUN.txt”’
EXEC xp_cmdshell @vCommand

Before that, we can use echo command to build the script file:

DECLARE @vCommand nvarchar(4000)
SET @vCommand= 'echo open localhost > ”C:\Temp\FtpDownload_RUN.txt”'
EXEC xp_cmdshell @vCommand
SET @vCommand= 'echo open ftpuser >> ”C:\Temp\FtpDownload_RUN.txt”'
EXEC xp_cmdshell @vCommand
SET @vCommand= 'echo open ftpuser >> ”C:\Temp\FtpDownload_RUN.txt”'
EXEC xp_cmdshell @vCommand
SET @vCommand= 'echo open get "/Countries-20200614-090721.sql" "C:\Temp\Countries-20200614-090721.sql" >> ”C:\Temp\FtpDownload_RUN.txt”'
EXEC xp_cmdshell @vCommand
SET @vCommand= 'echo quit >> ”C:\Temp\FtpDownload_RUN.txt”'
EXEC xp_cmdshell @vCommand

And result:

cmd-shell-ftp-download-sql

winscp-ui-ftp-download-sql

Done! SQL wrapper as reference: FtpDownload-CMD.sql

Way 3 — using python

Why using this? — Linux enviroment!? Here that I’m using windows enviroment, but obviously it would work for Linux.

Documentation: https://docs.python.org/3/library/ftplib.html

Playing:

Preparing parameters:

• FTP host, named as FtpHost = localhost
• FTP user and password, named as FtpUser = ftpuser, FtpPassword = ftpuser
• FTP folder, named as FtpFolder = /, which is root folder
• FTP file mask, named as FtpFileMask= *.sql, which is to download all files with extension of .sql
• Local folder, named as LocalFolder = C:\Temp, which is the location where downloading files to.

OK, go to build the Python script: ftpdownload.py

from ftplib import FTP
import fnmatch as fm
ftp = FTP()
ftp.connect(host="FTPHost")
ftp.login(user="FTPUser", passwd="FTPPassword")
ftp.cwd("FTPFolder")
files = ftp.nlst()
files = (file for file in files if fm.fnmatch(file, "FTPFileMask"))
for file in files:
    print(f"Downloading {file}")
    with open("LocalFolder" + file, "wb") as fp:
        ftp.retrbinary("RETR " + file, fp.write)
ftp.quit()
print("Done")

Our sample will produce the result below: C:\Temp\ftpdownload.py

from ftplib import FTP
import fnmatch as fm
ftp = FTP()
ftp.connect(host="localhost")
ftp.login(user="ftpuser", passwd="ftpuser")
ftp.cwd("/")
files = ftp.nlst()
files = (file for file in files if fm.fnmatch(file, "*.sql"))
for file in files:
    print(f"Downloading {file}")
    with open("C:\\Temp\\" + file, "wb") as fp:
        ftp.retrbinary("RETR " + file, fp.write)
ftp.quit()
print("Done")

Run in command prompt to verify:

cd C:\Temp\
python ftpdownload.py
or:
python "C:\Temp\ftpdownload.py"

python-ftp-download-command-line

Go SQL:

EXEC sp_execute_external_script
@language =N'Python',
@script= N'
from ftplib import FTP
import fnmatch as fm
ftp = FTP()
ftp.connect(host="localhost")
ftp.login(user="ftpuser", passwd="ftpuser")
ftp.cwd("/")
files = ftp.nlst()
files = (file for file in files if fm.fnmatch(file, "*.sql"))
for file in files:
    print(f"Downloading {file}")
    with open("C:\\Temp\\" + file, "wb") as fp:
        ftp.retrbinary("RETR " + file, fp.write)
ftp.quit()
print("Done")'

If you would have PERMISSION DENIED issue, please try to grant permission to LocalFolder for ALL APPLICATION PACKAGES

fix-permission-denied-issue-python-sql

And result:

python-ftp-download-sql

Done. SQL wrapper as FtpDownload-Python.sql.

Way 4 and more — Would you like to suggest…?

Give it in comment.

Conclusion

Hope this post helps to have ideas how to deal with FTP in TSQL when you’re under request to deal with. World is as wide, ways are around to be exploring as further.

Next post will be Generate mock/fake data with TSQL

If you would have any requests please don’t hesitate to let me know via datnguyen.it09@gmail.com.