We help IT Professionals succeed at work.

how can we move the file by using SFTP in ssis

parpaa
parpaa asked
on
HI

i have a file in one server, i need to move that file to one more server. the target server folder can access by using the credentilas( this is not a shared folder)user name and password

how can we connect that folder by using ssis
Comment
Watch Question

Sr. BI  Developer
Commented:
Hi,

free option will be script an external WinSCP app.

http://www.codeproject.com/KB/database/SSIS_SFTP.aspx


paid options:

1-http://www.cozyroc.com/   $399.95
2-http://www.rebex.net/sftp.net/  $349.00
3-http://www.nsoftware.com/ssis/   $249.00
4-http://xceed.com/FTP_NET_Features.html   $899.95 USD

Commented:
Another commercially supported option would be to use Robo-FTP $150 to set up a "hot send" service.  This is basically a Windows Service that watches a folder on your computer and automatically sends any file dropped into it to a remote server using FTP, FTPS, SFTP, HTTP or HTTPS.  With this approach you'd only need to save the file-to-be-sent to a local or network folder and then the Hot Send service would do the rest.  You could customize the hot send script to have it insert a record into your database when the job is done, retry failed transfers and/or send an email notification on success or failure.
Jason YousefSr. BI  Developer

Commented:
Hi Alex,

I'm concerned, how would you control that Robo-FTP using SSIS? a script task maybe?

Commented:
I can think of 3 different way you could do it with Robo-FTP:

First, you could just make a Robo-FTP script that watches a folder and sends any files found to a remote SFTP site.  This would be installed as a Windows Service so it is "always on" and automatically restarted by the OS whenever the computer is rebooted.  Any file dropped into this folder would automagically be sent to the remote server and then either archived or deleted.  You could write the script so it only sends files of a certain type or size or just let it send everything. IYou could create multiple "source folders" if you have multiple remote servers where you send files so you might have a folder named Acme and if you put files in that folder they are sent to Aceme's SFTP server and another folder named Joe and any files in that folder are sent to Joe's FTP server and so on....  These folders could be used by any process that you want to use to generate the file, not just SSIS so all your SSIS has to do is put the file in the appropriate source folder and Robo-FTP will do the rest.

A second option is to write a Robo script to do your transfer logic and then launch it from SSIS just like a BAT file or any other external process.  The script will run and then terminate instead of being always on like the Hot Send service described above.  Basically you would launch robo-ftp.exe with the -s command line argument to specify the script file name.  If the file you want to send always has the same or similar name you can specify it inside the script using a wildcard if necessary but if the name of the file to send is completely different each time you can use one of the 9 available user defined command line variables to specify it.

The third option would be to use the COM interface to control Robo-FTP from a script task.  Using this option you could either have it run a complete script like in the second option above or you could feed it commands one at a time.  To me this would be harder to develop because you are potentially debugging in both the script code and the native task code.  With the other options you could debug your transfer logic completely within Robo-FTP and then only trigger it with SSIS logic.
Jason YousefSr. BI  Developer

Commented:
Great, thanks for the detailed information.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
You can use Third party SFTP component or write script for that as like

String Host = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["RemoteHost"]);
                String Username = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["RemoteUser"]);
                String Password = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["RemoteUserPassword"]);
                String DirectoryName = "IPA_FMA/";
                
                ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(ValidateServerCertificate);
                StringBuilder result = new StringBuilder();
                FtpWebRequest ftp;
                ftp = (FtpWebRequest)FtpWebRequest.Create(new Uri(Host + DirectoryName));

                ftp.UsePassive = true;
                ftp.KeepAlive = true;
                ftp.UseBinary = true;
                ftp.EnableSsl = true;

                ftp.Credentials = new NetworkCredential(Username, Password);
                ftp.Method = WebRequestMethods.Ftp.ListDirectory;
                ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(ValidateServerCertificate);
                WebResponse response = ftp.GetResponse();
                StreamReader reader = new StreamReader(response.GetResponseStream());
                string line = reader.ReadLine();

                 String filter = DateTime.Now.Month.ToString("00") + DateTime.Now.Day.ToString("00") + DateTime.Now.Year.ToString();
                while (line != null )
                {
                    if (line.StartsWith(filter))
                    {
                        result.Append(line);
                        result.Append("\n");
                    }
                    line = reader.ReadLine();
                }

                result.Remove(result.ToString().LastIndexOf("\n"), 1);
                reader.Close();
                response.Close();

                return result.ToString().Split('\n');

Open in new window