Transferring classified documents over the internet can be tricky and dangerous. One successful solution used by many is Secure File Transfer Protocol (SFTP). In short, SFTP is a technology that encrypts both commands and data using separate control and data connections between the client and the server. It allows users outside of secure corporate networks to download and upload documents safely and securely.
Using SFTP on Windows is fairly straight forward:
1) Download and install the WinSCP software on your local machine and the server where you want to execute the SSIS package (please note, it's very important to have it installed in both locations).
2) Enter the specified Host Name, Port Number, User Name and Password and select Login
3) Start transferring any files you want over the secure connection
A more involved SFTP task is automating a file download/publish. Whatever the reason, businesses may need to send out or receive files on a daily, weekly or monthly basis over SFTP. To meet this requirement, one can use SQL Server Integration Services with ease.
To implement an automated SFTP transaction please follow the below steps:
1) First you will need the SSIS Extension for SFTP tasks. Make sure that you have closed all of your Visual Studio windows before downloading and installing the extension
2) Open up Visual Studio and when you are ready to transfer your file using SFTP, right-click in the package designer window and select the SSIS Toolbox
3) Simply drag and drop an SFTP Task into the package designer window
4) The trickiest step in designing your SFTP task is defining the parameters and configuring the properties in the SFTP Task Editor. The SFTP Task Editor only accepts variables for the property fields. First you will need to create seven variables within the project that map to the corresponding values for each property. Navigate View >> Other Windows >> Variables and create your project variables.
|HostName||This should match exactly with the host name entered in the WinSCP Login window|
|PortNumber||This should match exactly with the host name entered in the WinSCP Login window|
|UserName||This should match exactly with the host name entered in the WinSCP Login window|
|Password||This should match exactly with the host name entered in the WinSCP Login window|
|LocalFile||This is the file path of the downloaded/uploaded file on the server where the SSIS package is executed|
|RemoteFile||This is the file path of the downloaded/uploaded file on the SFTP server|
5) The last step is to open up the SFTP Task Editor and select each of your variables
from the drop down menu. You can choose to “Send File(s)”, “Receive File(s)”,
To test the newly created package, right-click on task and select “Execute Task”.
Posted by Brian Pohl