SSIS Secure FTP Task

  • I am needing to transfer data using an encrypted tunnel. The only way I know of how to do this is to use Secure FTP (SFTP). SSIS has an FTP Task but doesn't seem to have any support for SFTP.

    Anyone run into this before?

    Thanks,

    Ben


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • Ben,

    You are correct that the native FTP tools in SSIS will not support SFTP.  You'll need to look at a third party utility like /n software (http://www.nsoftware.com/ssis/) for true support of SFTP.  You might look at using something like PGP or other encryption tool to encrypt your file using a script or execute process task.

    hth

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Thanks Tim,

    I've seen the /n software SSIS utilities app but the price is way outside the range for this. I have found however the open source WinSCP tool that has a command line interface that can be used to complete this. I haven't completed this yet but after reading through their documentation believe it will work.

    http://winscp.net/eng/docs/scripting

    Cheers,

    Ben


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • Use an execute process task with PSFTP - no need to shell out $$$ for /n software's SSIS task.

    Tommy

  • I am using WinSCP with a saved session and a script file and it works fine

  • Hi halexander

    Can you paste code which you have written in SSIS Script task. I am in same situation just like you. Do I have to download and Install WINSCP first or any component is there?

  • I followed the previous post suggestion and used PSFTP (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html).

    Then I created a .bat file that looks like this:

    cd "C:\Folder where you want the files to be put on your local client"

    "C:\psftp" (host name of sftp server) -l (user) -pw (password) -b c:\sftpcopy.bat

    Note - remove the brackets in the psftp command.

    In c:\sftpcopy.bat I have:

    mget *

    quit

    This assumes that your default server folder is where the files that you want to download. Otherwise you need to insert a CD command before the mget.

    Then, in integration services, create a new package and insert a process task. Simply add the name of the .bat file in the executable process name.

  • The previous Post is accurate, however, I didn't want to put the required login and password into a file (not the best practice). So I installed WINSCP (http://winscp.net/eng/index.php) and created a Stored Session with all the connection specifics including a encrypted password.

    I then created a text file into a directory on the machine and put the following text into it:

    # Force Binary Transfer

    option transfer binary

    # Download all gzip and zip

    get *.gz

    get *.zip

    # Close connection

    close

    # Exit WinSCP

    exit

    As you can see, I just wanted to download load files but you can perform any ftp operation you wish in this file.

    As in the previous post I created an Execute Process task and put the path of my executable in the Executable parameter and put the arguments "(WinSCP session name) /console /script=(Fullpath WinSCP file)" in the Arguments

    Just remember to put the download or upload directory in the WorkingDirectory parameter.

    Also remember the () should be replaced by your specific names.

    Hope that helps

  • Thank you so much guys. I will check with it.

  • Every method I've tried for executing PSFTP from within a sql server 05 package does not work. It doesn't succeed or fail, it just simply gets to that process step and hangs indefinitely. my current method is an Execute Process task that runs a batch file, the batch file changes the directory to the working directory, uses a runas to my domain account, and then runs psftp with a batch. This method works if I run the package through visual studio debugger, if I install the package and run it myself from sql server, everything except when I actually schedule it to run from sql service agent.

  • I'm just a newbie, myself, but it sounds like a problem I had where the SQLServerAgent did not have the proper permissions to read/write to a certain folder. When you run as debug or run the job yourself, it works because YOU have permission. When you schedule it, it doesn't

  • I personally use a program called Putty so SFTP. This is not a problem with your package or batch file, but possibly a permission issue.

  • Hi,

    Would you please tell me how getlisting of folder using this dll. because my file belong inside 3 sub folder and 2 folders name may anything...

    like

    Root

    1

    date1

    Folder

    File1

    file2

    date2

    Folder

    File1

    file2

    2

    date1

    Folder

    File1

    file2

    date2

    Folder

    File1

    file2

    Can i get directory listing and make a loop for each directory. If any one know please share your knowledege...

    Thanks

    Manish Jain

  • I experience the same issue with PSFTP... session runs fine when executed interactively, by if I schedule a job, the PSFTP process hangs indefinitely. It is not a permission issue, as I've set the SQL Agent to run as me just to make sure. Does anyone have a solution to correct the apparent "Hang" issue?

  • I have the same problem. At first I was sure it was the PSFTP registry entry required due to SSH. When you first execute PSFTP connection with a new site, it requires interaction. To save the server key or not. If you save the server key you will not get asked again.

    I logged into the server, executed the PSFTP process and selected the option 'y' to save to registry. However, after this step the SQL Server Agent process still hangs. Puzzled???

Viewing 15 posts - 1 through 15 (of 47 total)

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