Updated February 21, 2023
Introduction to SSIS SFTP
The following article provides an outline for SSIS SFTP. Secure File Transfer Protocol (SFTP) is a globally recognized secure protocol for transferring and accessing data over just a secure channel. In SFTP mode, the information and route are encrypted, preventing unwanted access by attackers. It’s primarily used between corporations to transmit secure and highly confidential data.
What is SSIS SFTP?
WinSCP is a free freeware that provides a command-line utility for communicating with SFTP sites.
We must take the following steps:
- First, go to http://winscp.net/eng/download.php and download the WinSCP client.
- C: Program FilesWinSCPWinSCP.Com is the location of the command-line utility.
- Write a script in a text editor (say mydemo.txt). The following example may provide some inspiration:
option batch on
option confirm off
open SFTP://user:password@ipaddress:port
cd myfold
option transfer binary
get *.dat c:\myfold
mv *.dat ./backup/*
close
exit
Next, invoke using the following statement.
"C:\Program Files\WinSCP" -script=c:\mydemo.txt
SFTP, or secure FTP, is a file transfer application that leverages SSH. It encodes both instructions and data, unlike normal FTP, preventing passwords and sensitive information from being sent across the internet in cleartext.
- Save this file, and we’ll set up our Execute Process Task to run it.
- WinSCP is available in two variants. WinSCP.exe (the application with a nice GUI) and WinSCP.com are two of them (the command line interface of the executable)
- We’ll utilize the command-line interface to program the component and customize the default behavior by overriding it with a script file.
- Set the working directory to the native location of the executable on local and provide the properly qualified URL to the script as an input. Whenever the package is run, it will retrieve all documents in the remote directory that end in.txt and then transfer each of them to the local path.
- Many excellent examples of developing scripts to work with ftp/SFTP files and folders can be found on the WinSCP website. The Scripting Language is stated below:
This could be used to transfer files
option confirm off
open SFTP://username:[email protected]:22 -hostkey="ssh-rsa 2048 XX: XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX"
put "C:\Project\Vendor\Files\UnsubscribeEmails_%TIMESTAMP#yyyymmdd%.txt" "/subscriptions/unsub/"
Features
- Use an ADO.net table to performance of the two FTP directory activities such as creating, deleting, and retrieving folder information.
- Before sending credentials and any other information to a remote server, support for SFTP Server fingerprint verification and SSL Certificate Thumbprint verification.
- Support for SSH File Transfer with Public/Private Keys.
- Support for SSL/TLS encryption for explicit and implicit FTP connections
Using SSIS SFTP
While Integration Services offers an ftp job, it does not support downloading files from an SFTP site while working in SSIS. While trying to utilize the ftp job to pull data from an SFTP site, this can be perplexing. There is no explicit notification that we are just using the wrong component. I could also change the port to a regular SFTP port, but the site still won’t connect.
The scripting features of WinSCP were employed to get around this constraint. WinSCP is a great application with a user-friendly interface for communicating with ftp/SFTP sites, an intuitive and simple scripting language, and choices for even more secure protocols.
SFTP connection manager to make a connection.
To explain how it works, I’ve created a package that extracts data and uploads it to an SFTP server. There are three parts to the package. The first is an information flow activity that pulls data from SQL Server and saves it as a flat file on the file system. The second job is an Execute Process task, which starts a batch file that transfers the file using the WinSCP script. The next task is to save the produced file to an Archive folder. The procedure is scheduled daily, so each file’s name refers to the date.
Here is the FileZilla client to connect to the server:
The following stages will be included in a solution for this scenario:
- To our destination folder, save the PSFTP.exe file.
- Using Windows environment language, create a batch file with logic to download the text file.
- To start PSFTP.exe, create an SSIS package with an Execute Process Task.
- To connect to the SFTP site for our example, I utilized the FileZilla client. As a result, we’re linked to our distant SFTP location in the image below.
Step-1
Download the pSFTP.exe program from the Putty Download Page to a local location.
Step-2
Construct a batch file
In the local directory, create a new file and type the following command:
cd mdfold
get employee.txt
First, the Windows command cd is used to change the directory. Next, we need to access the SFTP site’s mdfold folder, so we write the code as cd mdfold, which tells the process to go to the mdfold directory.
The Windows command gets used to download the file from a server. We’ve written the code as getting Employee.txt, allowing the batch file to get the file from the SFTP location.
Step-3
To launch the batch file, construct an SSIS package with the Execute Process Task:
First, create a new Integration Services project in Visual Studio.
Then, drag and drop an Execute Process Task to the Designer from the SSIS Toolbox Menu.
The arguments in the Execute Process Task will now be verified, and the process will appear without any validation problems, as shown here. When we execute the Execute Process Task, we can observe that it opens a command window with PSFTP.exe, links to the SFTP site with the credentials we gave as parameters, and then runs the batch file to download the file to our local folder. This will launch PSFTP.exe while it is running, as shown below.
PSFTP will close once it has completed its duty, just like the other SSIS tasks.
Conclusion
This article explains how simple SFTP is to use and how the software can perform various activities while connecting to various types of Internet servers. The work properties are minimal for simplification, but we try to include those properties required for successful configuration control.
Recommended Articles
This is a guide to SSIS SFTP. Here we discuss how simple SFTP is and how the software can perform various activities. You may also look at the following articles to learn more –
