using sftp in SSIS

  • 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

  • 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.)

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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
    }
  • 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.

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

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