Using sftp in Visual Studio for SSIS packages

  • Isabelle2378

    SSCertifiable

    Points: 6165

    Hi,

    I've inherited the maintenance of SSIS packages that were created by our former developer so I'm not really familiar with things in this area.  We are currently using ftp in some of the packages and now are going to go to sftp, but the packages cannot connect using the same credentials.  We see in the logs that it passes the credential check but does not connect.  Someone suggested maybe installing an FTP/SFTP Extension for Visual Studio.  Has anyone else run into this problem?  We are using Visual Studio Professional 2015 and the current FTP connects with no problems.  Thank you in advance! 🙂

    [2019-11-13 10:34:29]:CONNECT [  9519] - Native user 'starkfriededi' authenticated
    [2019-11-13 10:34:29]:  REPLY [  9519] - [starkfriededi] 230 Password Ok, User logged in
    [2019-11-13 10:39:09]:CONNECT [  9523] - Shell access is not permitted
    [2019-11-13 10:39:09]:CONNECT [  9523] - The client closed the connection
    [2019-11-13 10:39:09]:CONNECT [  9523] - Connection terminated

    Thanks!
    Bea Isabelle

  • Phil Parkin

    SSC Guru

    Points: 243849

    If you don't mind spending some cash on a 3rd party product, Cozyroc's SSIS add-ons will help you do the job (and many others).

    Take a look here & track down the File Transfer Task.

    Alternatively, with a bit more code (but less cash), you will probably be able to get a Winscp solution up and running.

    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.

  • Isabelle2378

    SSCertifiable

    Points: 6165

    Hi Phil,

    Thanks for your reply!  So does Visual Studio not work with sftp?  Right now the SSIS package just has the ftp setup with the connection manager and it works fine.  The only thing i did was change the server to sftp and port to 22 and it will not connect.

    Thanks!
    Bea Isabelle

  • Phil Parkin

    SSC Guru

    Points: 243849

    Isabelle2378 wrote:

    Hi Phil,

    Thanks for your reply!  So does Visual Studio not work with sftp?  Right now the SSIS package just has the ftp setup with the connection manager and it works fine.  The only thing i did was change the server to sftp and port to 22 and it will not connect.

    Unfortunately, there is no out-of-the-box support for SFTP in native SSIS.

    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.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    SSDT does not have a built-in sFTP component - the only way to utilize sFTP is to utilize a third-party product.  The basic setup would be an Execute Process Tasks - which calls the WinSCP (or other client's) command line with the appropriate parameters.

    Here is an outline from WinSCP for a basic configuration: https://winscp.net/eng/docs/guide_ssis

    With that said - WinSCP also has a .NET assembly that can be called from a Script Task.  From the above link - or here: https://winscp.net/eng/docs/library_ssis - they outline this as the preferred approach.

    I have implement both approaches - and either works.  I prefer the second approach though...

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Isabelle2378

    SSCertifiable

    Points: 6165

    Hi,

    Thank you for the information.  I will look at both the links you provided and keep digging. 🙂

    Thanks!
    Bea Isabelle

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

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