SQL Server: SFTP with SSIS Execute Process Task

SFTP with SSIS Execute Process Task

Hello,

SFTP, or secure FTP, is a program that uses SSH to transfer files. Unlike standard FTP, it encrypts both commands and data, preventing passwords and sensitive information from being transmitted in the clear over the network. It is functionally similar to FTP, but because it uses a different protocol, you can't use a standard FTP client to talk to an SFTP server, nor can you connect to an FTP server with a client that supports only SFTP (Source: Knowledge Base – Indiana University.)

Now, since it is evidently clear that one cannot configure the standard FTP Task to establish a SFTP session, this leaves user with two options

• Create a C# or VB program that will initiate the SFTP call, perform the required operation and also exit the session gracefully.
• Use out of box SFTP client in conjunction with SSIS tasks to perform any required operation(For example: Fetch the ETL load from remote SFTP server and load them into staging area for ETL operations)

Here I let us have a walkthrough for option B which is more elegant (as far as a database developer is concerned)

Let’s create a new SSIS package and drag the Execute Process Task on the designer pane.

Also, you should have a SFTP client installed on your local machine for the Execute package task to consume the executable with any command arguments, if required.

Before we proceed ahead, we should have a batch script file for the SFTP client to execute.

Say, I need the SFTP client to connect to the remote host and get all files from a specified remote directory to my local development environment at a local directory.

See below for the script:

Save this file and now we shall configure our Execute Process Task to execute this file.

WinSCP comes in two flavors. One which is WinSCP.exe (the executable with a neat GUI) and WinSCP.com (the command line interface of the executable)

Since we need to program the component and control the default behavior by overriding it with a script file, we shall use the command line interface.

Pass the fully qualified path to script file as the argument and set the working directory to the native location of the executable on local.

The package when executed will get all files ending with .txt extension found in the remote directory and later transfer each of them to the local path.

That’s all. Happy learning

Find us on Facebook at http://www.FaceBook.com/SQLServerGeeks and Twitter at @SqlServerGeeks

Thanks | Raunak Jhawar | t:@raunakjhawar