How to copy folder from FTP to local Drive using SSIS?

Question

WE have a call recordings everyday that are posted in FTP. As a backup i wanted to copy those recordings from FTP in to local drive on daily basis.Is it possible to copy entire folder as a whole and paste the folder in my local and rename it ?

thanks in advance

Answer 1

Yes - you would use a File System Task and set the Operation to Copy Directory (before Microsoft changed it with Win 95, folders were called directories). You can change the name in the destination connection where you choose the destination folder. You can use an expression to add a date if you want.

https://msdn.microsoft.com/en-us/ms140185.aspx

Here's some good step-by-step instructions if you need them since Microsoft gives vague descriptions with no examples:

http://oops-solution.blogspot.com/2011/11/file-operation-copy-move-rename-and.htmlhttp://oops-solution.blogspot.com/2011/11/file-operation-copy-move-rename-and.html

Answer 2

BIDS in 2008 has an FTP Task, but I never use it. There are also several third party extensions for SFTP and the like, but I don't use those.

[Edit: Since the above links are to CodePlex, which is shutting down, I'll provide more detail. The first link is to a project titled "SSIS SFTP Task Control Flow Component" and the second is to a project titled "SSIS Extensions - SFTP Task, PGP Task, Zip Task". As of June 2017 the projects do not appear to have an obvious rehosting.]

I tend to use WinSCP with a script file. It's certainly the least Microsoft-y approach, but, IMX, it's more foolproof. This has the advantage of working for FTP, FTPS, and SFTP. It has the disadvantage that you're storing the password in plain text. You can store it in the registry with WinSCP's session manager, but I believe that's a per-user registry which makes for lots of possible fun when you have the SQL Agent using an SSIS Proxy account.

Create your script file with the commands you want to use. Be sure to specify option batch abort and option confirm off. You also might want to specify option failonnomatch, but I haven't tested that one yet myself since I'm still on a slightly older version that doesn't support that option.

Mine tend to look like this:

# Set batch settings
option batch abort
option confirm off

# Connect
open sftp://user:[email protected] -hostkey="ssh-rsa 2048 00:11:22:33:44:55:66:77:88:99:aa:bb:cc:dd:ee:ff"

# Change remote and local directories
lcd "M:\SSIS\eSchoolPlus to Clever\Output"

# Transfer files
put -nopreservetime -nopermissions -transfer=ascii file1.csv
put -nopreservetime -nopermissions -transfer=ascii file2.csv
put -nopreservetime -nopermissions -transfer=ascii file3.csv
put -nopreservetime -nopermissions -transfer=ascii file4.csv
put -nopreservetime -nopermissions -transfer=ascii file5.csv

close
exit

The options on put are due to how the remote server works for the script I happened to grab. get works about the same.

Then you use an Execute Process Task. The executable is C:\Program Files (x86)\WinSCP\WinSCP.exe and the Arguments I use are:

-console -script="X:\Path\To\Script\WinSCPScript.txt" -xmllog="X:\Path\To\Script\WinSCP-!S-!Y-!M-!D-!T.log" -xmlgroups`

That creates a daily log of just the WinSCP information. The XML logs are more readable and useful than the older log format, IMO, which contains a lot of mostly debugging information.

You can also go the route of using the WinSCP .NET library with an Execute Script Task, but that's obviously a lot more effort. They do have a HOWTO for that, however.