End-to-end scheduled SFTP with SQL Agent, PowerShell and the WinSCP .NET assembly

#What the job must do

1. Connect with a pinned host key and key authentication.
2. Download new files only, into a staging folder.
3. Verify each file (size match, not empty).
4. Move processed files to /archive on the server (or delete, if agreed).
5. Log each file to a table, so the load job knows what arrived.
6. Fail the step on any error, with the reason in the job history.

#Control table

CREATE TABLE dbo.SftpFileLog (
  id int IDENTITY PRIMARY KEY, feed sysname, file_name nvarchar(260), size_bytes bigint,
  remote_time datetime2(0), downloaded_at datetime2(0) DEFAULT SYSDATETIME(), status varchar(20));

#The script: D:\Jobs\sftp\Get-PartnerFiles.ps1

param(
  [string]$Feed = 'rates',
  [string]$RemoteDir = '/outbound/rates',
  [string]$LocalDir = 'D:\Inbound\Rates',
  [string]$SqlInstance = 'localhost'
)
$ErrorActionPreference = 'Stop'
Add-Type -Path 'D:\Tools\WinSCP\WinSCPnet.dll'

$opts = New-Object WinSCP.SessionOptions -Property @{
  Protocol              = [WinSCP.Protocol]::Sftp
  HostName              = 'sftp.partner.example'
  UserName              = 'ratesfeed'
  SshHostKeyFingerprint = 'ssh-ed25519 255 Y9f0wN3x2b...='
  SshPrivateKeyPath     = 'D:\Jobs\sftp\keys\ratesfeed.ppk'
}
$session = New-Object WinSCP.Session
$session.SessionLogPath = "D:\SQLAgentLogs\sftp_${Feed}_$(Get-Date -f yyyyMMdd_HHmmss).log"

try {
  $session.Open($opts)
  $files = $session.ListDirectory($RemoteDir).Files | Where-Object { -not $_.IsDirectory -and $_.Name -like '*.csv' }
  if (-not $files) { Write-Output "No files for $Feed."; return }

  foreach ($f in $files) {
    $local = Join-Path $LocalDir $f.Name
    $r = $session.GetFiles($session.EscapeFileMask($f.FullName), $local)
    $r.Check()                                             # throws on transfer error

    $size = (Get-Item $local).Length
    if ($size -ne $f.Length -or $size -eq 0) { throw "Size check failed for $($f.Name): local $size, remote $($f.Length)" }

    $session.MoveFile($f.FullName, "$RemoteDir/archive/$($f.Name)")

    $q = "INSERT dbo.SftpFileLog (feed, file_name, size_bytes, remote_time, status) VALUES (@feed, @name, @size, @rt, 'downloaded')"
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database DBA -Query $q -EnableException -SqlParameter @{
      feed = $Feed; name = $f.Name; size = $size; rt = $f.LastWriteTime }
    Write-Output ("{0:u} downloaded {1} ({2} bytes)" -f (Get-Date), $f.Name, $size)
  }
}
finally {
  $session.Dispose()
}

The log row is inserted with parameters (dbatools Invoke-DbaQuery -SqlParameter), so odd file names can't inject SQL. Install dbatools for the proxy account, or use System.Data.SqlClient with SqlCommand.Parameters instead.

#The job step

Use a CmdExec step that calls Windows PowerShell explicitly, so errors become a non-zero exit code:

EXEC msdb.dbo.sp_add_jobstep @job_name = N'SFTP - Partner Rates', @step_name = N'Download',
     @subsystem = N'CmdExec', @proxy_name = N'SFTP Jobs', @cmdexec_success_code = 0,
     @command = N'powershell.exe -NoProfile -NonInteractive -ExecutionPolicy Bypass -File "D:\Jobs\sftp\Get-PartnerFiles.ps1" -Feed rates',
     @output_file_name = N'D:\SQLAgentLogs\sftp_rates_step_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt',
     @retry_attempts = 2, @retry_interval = 10;

An unhandled throw in a -File script returns exit code 1, which fails the step.

#Uploads

Same pattern in reverse: PutFiles($local, "$RemoteDir/", $false, $transferOptions) to a temporary name, then MoveFile to the final name. Many partners only pick up complete files, and the rename is atomic on most SFTP servers.

#Operations

Tip

Keep transfer and load as separate jobs (or steps). The load reads SftpFileLog for status = 'downloaded' and marks rows loaded, so you can rerun either side safely.

#Checklist

• Host key pinned, key auth, protected key file
• Size check and remote archive per file
• File log table feeding the load job
• CmdExec + PowerShell with exit codes, output file, retries
• Separate alert when expected files don't arrive