SQL Server 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.

1_SQL_Server_SFTP_with_SSIS_Execute_Process_Task

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:

2_SQL_Server_SFTP_with_SSIS_Execute_Process_Task

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

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

6 Comments on “SQL Server SFTP with SSIS Execute Process Task”

  1. After doing the above steps,my package is executing fine,but the desired result (recieve files from remote path to local machine) is not achieved.

    when i run my script in the command prompt,it runs fine and establishes connection to my sftp server.i’ve also set me WorkingDirectory to the same path as my WinSCP executable.

    Am i missing something?

  2. Hi Raunak,

    I’m passing the Winscp exe file (C:\program files) path in “Executable”

    Specifying the folder path in which my txt/bat file in “Arguments”

    lastly,the working directory is set to a folder on my local machine.

    Am i missing something here?its exactly the same as you’ve mentioned in your blog above.

    Thx in advance for your help

    Appreciated

  3. Hi,

    Can we directly read a csv file from SFTP location without downloading to local system from SSIS? I can use FTP task component but it will download files from FTP location. My requirement is to read files from SFTP location without downloadinng them elsewhere due to confidentiality/misuse of data.

    Regards,

    Chaitanya

  4. Hi Rj,

    Am using filezilla to download the files from SFTP using Execute process task in SSIS.,

    After i configured everything as mentioned in the article am getting the below error

    -s and -c cannot be processed at the same time

    can you guide me

    Regards,
    Ragu

Leave a Reply

Your email address will not be published. Required fields are marked *