using sftp in SSIS

Question

Post reply

using sftp in SSIS

Isabelle2378

SSCertifiable

Points: 6174
More actions
April 20, 2021 at 5:10 pm

#3873583

Hi,
I have SSIS packages for my SQL 2016 that currently use the FTP Connection manager. I need to switch these to use sftp instead of ftp and I'm seeing lots of articles stating that SSIS does not support this and most are using WinSCP or scripting to get around this issue. I'm wondering if there are betting solutions than WinSCP or if anyone has used PowerShell to do this? I have quite a few very sensitive SSIS packages that I need to change and I'm trying to figure out what the best solution is from anyone that has already done this.
Any help is greatly appreciated! 🙂
Thanks!
Bea Isabelle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

By 'betting', did you mean 'better'?

WinSCP is actually a rather good solution.

But if, by better, you meant easier to configure, I'd recommend this: https://www.cozyroc.com/ssis/file-transfer-task.

It's not free, but works well (and you get loads of other useful components in the bundle).

(I have used Cozyroc products, but have no affiliation with them.)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Hi Phil,

Yes, I meant better 🙂 Thanks for the feedback. I'm just trying to get a feel of what people are using out there and confirm if WinSCP is a good solution. I will look at Cozyroc as well!

Thanks!
Bea Isabelle

Ken McKelvey SSCoach Points: 18855 More actions · Answer 3

I generally us WinSCP via Powershell. When I cannot install WinSCP I use a Powershell function like:

<#
PutFileSFTP.ps1
Uploads a file to a sftp site

PutFileSFTP -LocalFile <path> -RemotePath <path> -SFTPHost <URL> -UserName <string> -KeyFile <path> -CredPrefix <path>

eg
. C:\tmp\PutFileSFTP.ps1

$params = @{
'LocalFile' = 'C:\tmp\Outgoing.csv';
'RemotePath' = '/';
'SFTPHost' = 'sftp.somewhere.com';
'UserName' = 'YourUserName';
'KeyFile' = 'C:\tmp\SomeWhere.key'; #blank if no keyfile
'CredPrefix' = 'C:\tmp\credSomeWhere' } #blank if no password file

PutFileSFTP @params
#>
Function PutFileSFTP
{
    Param($LocalFile, $RemotePath, $SFTPHost, $UserName, $KeyFile, $CredPrefix)
Try
{
$ErrorActionPreference = "Stop"
If ($CredPrefix -eq '')
{
$Password = new-object System.Security.SecureString
}
Else
{
$Content = "{0}_{1}.txt" -f $CredPrefix,$env:UserName
$Password = get-content $Content -ErrorAction Stop| ConvertTo-SecureString
}
$Credential = New-Object System.Management.Automation.PSCredential ($UserName, $Password) 

Write-Output "$((Get-Date -Format s)) Upload $LocalFile to $SFTPHost$RemotePath."
Write-Output "$((Get-Date -Format s)) Connecting to $SFTPHost..."

Try
{

If ($KeyFile -eq '')
{
$session = New-SFTPSession -ComputerName $SFTPHost -Credential $Credential -AcceptKey
}
Else
{
$session = New-SFTPSession -ComputerName $SFTPHost -Credential $Credential -KeyFile $KeyFile -AcceptKey
}

Write-Output "$((Get-Date -Format s)) Uploading file..."
Set-SFTPFile -SFTPSession $session -RemotePath $RemotePath -LocalFile $LocalFile -OverWrite
Write-Output "$((Get-Date -Format s)) Upload complete."
}
Finally
{
if ($session = Get-SFTPSession -SessionId $session.SessionId)
{
Write-Output "$((Get-Date -Format s)) Disconnecting session..."
$session.Disconnect()
}
$null = Remove-SftpSession -SftpSession $session
}
}
Catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
$emailFrom = "xxx"
$emailTo = "xxx"
$subject = "xxx"
$body = "$ErrorMessage"
$smtpServer = "xxx"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom, $emailTo, $subject, $body)

        Write-Output "$((Get-Date -Format s)) $ErrorMessage"
Throw $ErrorMessage
Break
}
}

To Generate the password file I use:

#Generate Password File
#https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-and-secure-strings/
$LocalFilePath='C:\tmp'  
if (-not (test-path $LocalFilePath\cred_$env:UserName.txt ))
{
    "password" | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File $LocalFilePath\cred_$env:Username.txt
}

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

I used CozyRoc at my last job and was satisfied with it. I think there was a similar product, for a bit less $, but don't remember the name.