SSIS SFTP: Four Ways to Transfer Files Over SFTP From an SSIS Package, and What Breaks Under the SQL Agent

SSIS includes an FTP Task, and it does not support SFTP. That distinction matters: SFTP runs over SSH and is not the same protocol as FTP or FTPS.

As a result, nearly every SSIS package that exchanges files with a partner over SFTP uses one of four approaches: WinSCP from an Execute Process Task, SSH.NET in a Script Task, a third-party SSIS component, or the OpenSSH client that ships with Windows. All four work. The differences appear later, during deployment, SQL Agent execution, server upgrades, or the first partially completed transfer. This guide walks through each option, explains why packages that work in Visual Studio fail under the SQL Agent, and shows how to remove SFTP from the package altogether.

Why SSIS has no SFTP task

The built-in FTP Task and FTP Connection Manager speak traditional FTP. They do not support SFTP, and Microsoft provides no SFTP equivalent in the SSIS toolbox. Package authors call an external program, write custom code, or install a commercial component.

WinSCP from an Execute Process Task

For many teams, WinSCP offers the best balance of reliability, flexibility, and operational simplicity. Install WinSCP on the SSIS server, write a WinSCP script, and run WinSCP.com from an Execute Process Task. The task's Executable is C:\Program Files (x86)\WinSCP\WinSCP.com and its Arguments are /script="C:\ssis\scripts\deliver.txt" /log="C:\ssis\logs\deliver.log" /ini=nul. The script:

open sftp://svc_ssis@sftp.harborline.example/ ^
  -hostkey="ssh-ed25519 255 SHA256:..." ^
  -privatekey="C:\ssis\keys\svc_ssis.ppk"

put "C:\ssis\out\orders.csv" "/inbound/orders.csv.part"
mv "/inbound/orders.csv.part" "/inbound/orders.csv"
exit

The -hostkey argument pins the SFTP server's SSH host key, so the package never connects to an unexpected server. If the partner changes its host key, the transfer fails until someone verifies and updates the fingerprint, which is exactly the behavior you want. The upload-then-rename keeps the partner from processing a half-written file; the rename happens on the remote server and is typically atomic.

In the Execute Process Task, set FailTaskIfReturnCodeIsNotSuccessValue to true and SuccessValue to 0. Without that, WinSCP can report a failure while the package continues as though nothing went wrong. When the filename changes per run, build the Arguments property with an SSIS expression, and keep credentials out of the command line, where other processes and logs can see them.

WinSCP gives you explicit host-key verification, private-key authentication, transfer logging, useful exit codes, resume support, and no custom SSIS assembly to deploy. Its drawback is that it becomes one more dependency to install and maintain on every server that can run the package. The WinSCP command line and scripting post covers the script mode in depth.

SSH.NET in a Script Task

A C# Script Task connects directly with the SSH.NET library, referenced through the Renci.SshNet namespace:

using Renci.SshNet;
using System.IO;

var keyFile = new PrivateKeyFile(@"C:\ssis\keys\svc_ssis.key");

using (var client = new SftpClient("sftp.harborline.example", "svc_ssis", keyFile))
{
    client.Connect();

    using (var stream = File.OpenRead(@"C:\ssis\out\orders.csv"))
    {
        client.UploadFile(stream, "/inbound/orders.csv");
    }

    client.Disconnect();
}

This keeps the transfer logic inside the package and gives complete control over retries, remote filenames, validation, and error handling. The tradeoff is deployment. Assemblies referenced by SSIS Script Tasks have to be available wherever the SSIS runtime resolves them, which depending on the SQL Server version means the Global Assembly Cache or a specific runtime location, and every server that can execute the package, development, test, production, and failover nodes, needs the same compatible version installed. Teams with an established assembly deployment process are comfortable with that. Everyone else finds an external executable easier to support. You also implement host-key verification yourself; a successful SSH connection is not proof that you reached the correct server. The C# SFTP post covers SSH.NET in depth.

A third-party SFTP task

Several vendors sell SSIS component suites with an SFTP Task and SFTP Connection Manager that integrate with the designer: visual configuration, native SSIS variables and expressions, built-in logging and error handling, password and key authentication, upload, download, rename, and directory operations, and vendor support. The cost is operational rather than technical: a license for each SSIS server, an installer on every development and execution host, a component version compatible with your SQL Server release, and an upgrade plan whenever SQL Server or SSIS changes. A commercial task fits when many packages use SFTP and the organization values vendor support. For one or two transfers it adds more infrastructure than it removes.

The OpenSSH client included with Windows

Windows Server 2019 and later can provide the OpenSSH client, including sftp.exe. An Execute Process Task calls it with a batch file of SFTP commands and a key:

sftp -b C:\ssis\scripts\deliver.sftp -i C:\ssis\keys\svc_ssis svc_ssis@sftp.harborline.example

where deliver.sftp holds put C:\ssis\out\orders.csv /inbound/orders.csv. OpenSSH uses OpenSSH-format keys, which helps when the same keys already serve Linux systems and automation. The operational issue is host-key verification: sftp.exe takes no fingerprint argument and reads the known_hosts file of the Windows account running the process, so the SQL Agent service account, or the proxy account, has to have the partner's host key in its known_hosts in advance. Preload and verify the correct key rather than disabling host-key checking. OpenSSH is a good no-license option whose dependence on account-specific configuration makes service-account execution less obvious to troubleshoot. The SFTP automation post covers batch mode in detail.

Why it works in Visual Studio but fails in the SQL Agent

An SFTP package often works perfectly in Visual Studio and fails the moment the SQL Agent runs it. The reason is rarely SSIS. It is the execution identity and environment.

The job runs under a different Windows account. In Visual Studio, external tools run as you; the SQL Agent runs the package as its service account or a configured proxy, with different user profiles, environment variables, SSH configuration, known_hosts files, WinSCP saved sessions, mapped drives, and file permissions. A saved WinSCP session in your profile is not available to the Agent account, and neither is your known_hosts. Use explicit paths and store nothing in a user profile.

The service account cannot read the key. It needs read access to the private key and the input file, and write access to output, temporary, and log directories. Grant access to that account alone; private keys are never readable by Users or Everyone.

Mapped drives do not exist. Z: exists in your interactive session and not in the Agent's. Use local or UNC paths such as \\fileserver\ssis-outbound\orders.csv with both share and NTFS permissions granted to the execution account.

The runtime architecture differs. The 32-bit and 64-bit SSIS runtimes resolve executables, registry settings, providers, and assemblies differently. Confirm whether the Agent step uses the 32-bit runtime, which WinSCP is installed, and where custom assemblies live.

The external process fails without failing the package. An Execute Process Task has to treat a nonzero exit code as a failure, or the SFTP client fails while the workflow continues. Capture the tool's log, check its exit code, and make the task fail loudly.

A failed upload leaves a partial file. Upload to a temporary name, confirm completion, rename to the final name, and archive or delete the local file only after success, and make the process idempotent so a retry never delivers a duplicate.

The more important question: does SFTP belong in the package?

All four approaches make SSIS responsible for more than moving data. The package, or the server it runs on, owns the partner's SFTP credentials, the client private key, host-key verification, retry behavior, transfer logs, partial-file handling, late or missing-file detection, and client software and library upgrades. That is acceptable for a few stable integrations. As the number of partners grows, file transfer becomes an infrastructure problem rather than an ETL problem.

Taking SFTP out of SSIS with Files.com

A Files.com Remote Server Mount connects a partner's SFTP server to a folder on your Files.com site. Files.com manages the partner credentials, can generate the SSH key pair so the private key never lands on the SSIS host, pins the partner's host key and disables the connection if it changes, passes every operation through in real time, and records the transfers in the audit log.

The package no longer touches the partner's SFTP server. It uploads to the mounted folder through the Files.com CLI from an Execute Process Task, over HTTPS with one API key and resumable, parallel transfers:

files-cli upload "C:\ssis\out\orders.csv" "/partners/harborline/inbound/orders.csv"

Inbound transfers work the same way: the package downloads from the mounted folder before its Data Flow begins. SSIS transforms and validates data, Files.com handles the external transfer, and the partner keeps using SFTP.

The transfer logic can leave the package completely. SSIS writes files to a local folder and stops. The Files.com Agent on the SSIS server connects outbound, that folder becomes a remote server on Files.com, and a scheduled Sync moves the files to the partner. The package then contains no SFTP client, no key, and no transfer script, and a Files.com Expectation alerts the team when an expected inbound file does not arrive, which no SSIS task can do.

Which option to choose

For a single package, WinSCP is often the simplest choice. For many partners or business-critical transfers, moving SFTP out of SSIS produces a cleaner and more supportable architecture.

Frequently asked questions

Does SSIS support SFTP?

Not natively. The built-in FTP Task supports traditional FTP only. To use SFTP, a package calls a tool such as WinSCP or OpenSSH, uses a library such as SSH.NET, or installs a third-party component.

How do I use WinSCP in an SSIS package?

Run WinSCP.com from an Execute Process Task with a script that opens the SFTP connection with -hostkey and -privatekey, transfers the file, and exits. Configure the task to fail on a nonzero exit code and write a log to an explicit location.

Why does SFTP work in Visual Studio but fail in the SQL Agent?

The SQL Agent runs the package under a different Windows account with its own profile, permissions, SSH configuration, known_hosts, and WinSCP settings. Use explicit paths, grant the execution account access to the required files and folders, avoid mapped drives, and match the 32-bit or 64-bit runtime.

Can SSIS use SSH keys for SFTP?

Yes. WinSCP takes a key through -privatekey, OpenSSH sftp through -i, and SSH.NET through PrivateKeyFile. Store the key where only the package's execution account can read it.

How does an SSIS package handle partial uploads?

Upload under a temporary name, then rename after the transfer succeeds, and have the partner process only files with the final naming pattern. Design retries so they never deliver a duplicate.

Can Files.com deliver the file for the package?

Yes. The package uploads over HTTPS to a Files.com folder that mounts the partner's SFTP server, or SSIS writes to a local folder and the Files.com Agent and a scheduled Sync deliver the file with no SFTP logic in the package.