using SSIS for FTP-SSL

I have to FTP a file to an FTP/SSL site.. I can do it via WFTP.. When I try to set up a FTP connection Manager it tells me the credentials as not correct.. I know they are becuase I use WFTP and successsfully FTP files.. How can I get this to work in SSIS?
LVL 1
cheryl9063Asked:
Who is Participating?
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
Well can't help past this point, no access to SSL, also I don't know what kind of server you'll be connected to, windows vs unix, and what type of Trust.

If you'll be only uploading, you need to remove the UN-needed (public bool) (Subs)  such as delete, and get..etc...

If you can create a test account so I can help you test.


0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Well if you're sure of the remote folder, user name and password...so it could be a FTP task issue, as it doesn't support all or every flavour of FTP servers ,espcially linux.

so you've 2 options, download the FTP TASK++ , free one
http://www.easkills.com/ssis/ftptask

or use a script task
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2b7cf6b5-d16d-44cd-954d-68e9ba98ee91

that's all what I can think about, other then you DOUBLE check your inputs!
0
 
cheryl9063Author Commented:
What is SFTP? This is for SSL
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jason Yousef, MSSr. BI  DeveloperCommented:
My mistake then SFTP is SSH but FTPS is totally different, I thought it's the same! never done it before.
But thanks, now I learned something new !


there's some third part tasks HERE

Also you could search about the  .NET FtpWebRequest  and use a script task, or even Rebex.net Libraries.

0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Ok, that's a good one. I just tested it and it worked with me.

http://www.codeguru.com/csharp/.net/net_security/authentication/article.php/c15051
0
 
cheryl9063Author Commented:
What kind of code is that? Is this something I can put in an SSIS script task?
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Yes, c#  pronounced C Sharp!


0
 
cheryl9063Author Commented:
OK, I know a little VB and NO C but I will stick the code in a script task and see what happens.
0
 
cheryl9063Author Commented:
OK, I put the server, username, password, deposit deirectory in.. Where do I put the information as to where to get the file that needs to be FTP'd? ALso how do I see if I have made a succesful connection?

See my sample code below- I changed the username and password so I could show this to you
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.Security.Cryptography.X509Certificates;
using System.Net.Security;

namespace FtpFramework
{
   public class FTP
   {
       public string UserName = "usertest";
       public string Password = "test";
      public bool KeepAlive    = false;
      public bool UseSSL       = true;
      private string m_FTPSite = "ftp.teststores.com";
      public string FTPSite
      {
         get { return m_FTPSite; }
         set
         {
            m_FTPSite = value;
            if (!m_FTPSite.EndsWith("/")) m_FTPSite += "/";
         }
      }
      private string m_CurDir = "/deposit/LexingtonFurniture-4025e315";
      public string CurrentDirectory
      {
         get { return m_CurDir; }
         set
         {
            m_CurDir = value;
            if (!m_CurDir.EndsWith("/") && m_CurDir != "/deposit/LexingtonFurniture-4025e315")
               m_CurDir += "/";
               m_CurDir = m_CurDir.TrimStart("/".ToCharArray());
         }
      }

      public FTP() { }
      public FTP(string sFTPSite, string sUserName,
                 string sPassword)
      {
         UserName = sUserName;
         Password = sPassword;
         FTPSite = sFTPSite;
      }

      public static bool ValidateServerCertificate(object sender,
         X509Certificate certificate, X509Chain chain,
         SslPolicyErrors sslPolicyErrors)
      {
         if (sslPolicyErrors ==
            SslPolicyErrors.RemoteCertificateChainErrors) {
            return false;
         } else if (sslPolicyErrors ==
            SslPolicyErrors.RemoteCertificateNameMismatch) {
            System.Security.Policy.Zone z =
               System.Security.Policy.Zone.CreateFromUrl
               (((HttpWebRequest)sender).RequestUri.ToString());
            if (z.SecurityZone ==
               System.Security.SecurityZone.Intranet ||
               z.SecurityZone ==
               System.Security.SecurityZone.MyComputer) {
               return true;
            }
            return false;
         }
         return true;
      }

      public List<string> GetFileList(string CurDirectory,
         string StartsWith, string EndsWith)
      {
         CurrentDirectory = CurDirectory;
         return GetFileList(StartsWith, EndsWith);
      }
      public List<string> GetFileList(string StartsWith,
                                      string EndsWith)
      {
         FtpWebRequest oFTP =
            (FtpWebRequest)FtpWebRequest.Create(FTPSite +
            CurrentDirectory);
         //oFTP.EnableSsl = true;
         oFTP.Credentials  = new NetworkCredential(UserName,
                                                   Password);
         oFTP.KeepAlive    = KeepAlive;
         oFTP.EnableSsl    = UseSSL;
         // Validate the server certificate with
         // ServerCertificateValidationCallBack
         if (UseSSL) ServicePointManager.
            ServerCertificateValidationCallback =
            new RemoteCertificateValidationCallback
            (ValidateServerCertificate);
         //System.Security.Cryptography.X509Certificates.
         //X509Certificate oCert = new System.Security.Cryptography.
         //X509Certificates.X509Certificate();
         //oFTP.ClientCertificates.Add(oCert);

         oFTP.Method = WebRequestMethods.Ftp.ListDirectory;
         FtpWebResponse response =
            (FtpWebResponse)oFTP.GetResponse();
         StreamReader sr =
            new StreamReader(response.GetResponseStream());
         string str = sr.ReadLine();
         List<string> oList = new List<string>();
         while (str != null) {
            if (str.StartsWith(StartsWith) &&
               str.EndsWith(EndsWith)) oList.Add(str);
            str = sr.ReadLine();
         }
         sr.Close();
         response.Close();
         oFTP = null;

         return oList;
      }

      public bool GetFile(string Name, string DestFile)
      {
         //1. Create a request: must be in ftp://hostname format,
         //   not just ftp.myhost.com
         FtpWebRequest oFTP = (FtpWebRequest)FtpWebRequest.
            Create(FTPSite + CurrentDirectory + Name);
         //oFTP.EnableSsl = true;
         //2. Set credentials
         oFTP.Credentials = new NetworkCredential(UserName,
                                                  Password);
         //Define the action required (in this case, download
         //                            a file)
         oFTP.Method = WebRequestMethods.Ftp.DownloadFile;

         //3. Settings
         oFTP.KeepAlive = KeepAlive;
         oFTP.EnableSsl = UseSSL;
         // Validate the server certificate with
         // ServerCertificateValidationCallBack
         if (UseSSL) ServicePointManager.
            ServerCertificateValidationCallback = new
            RemoteCertificateValidationCallback
            (ValidateServerCertificate);
         //we want a binary transfer, not textual data
         oFTP.UseBinary = true;

         //4. If we were using a method that uploads data;
         //   for example, UploadFile, we would open the
         //   ftp.GetRequestStream here an send the data

         //5. Get the response to the Ftp request and the
         //   associated stream
         FtpWebResponse response =
            (FtpWebResponse)oFTP.GetResponse();
         Stream responseStream = response.GetResponseStream();
         //loop to read & write to file
         FileStream fs = new FileStream(DestFile, FileMode.Create);
         Byte[] buffer = new Byte[2047];
         int read = 1;
         while (read != 0) {
            read = responseStream.Read(buffer, 0, buffer.Length);
            fs.Write(buffer, 0, read);
         }    //see Note(1)
         responseStream.Close();
         fs.Flush();
         fs.Close();
         responseStream.Close();
         response.Close();
         oFTP = null;

         return true;
      }

      public bool UploadFile(FileInfo oFile)
      {
         FtpWebRequest ftpRequest;
         FtpWebResponse ftpResponse;

         try {
            //Settings required to establish a connection with
            //the server
            ftpRequest = (FtpWebRequest)FtpWebRequest.
               Create(FTPSite + CurrentDirectory + oFile.Name);
            ftpRequest.Method = WebRequestMethods.Ftp.UploadFile;
            ftpRequest.Proxy = null;
            ftpRequest.UseBinary = true;
            ftpRequest.Credentials =
               new NetworkCredential(UserName, Password);
            ftpRequest.KeepAlive = KeepAlive;
            ftpRequest.EnableSsl = UseSSL;
            // Validate the server certificate with
            // ServerCertificateValidationCallBack
            if (UseSSL) ServicePointManager.
               ServerCertificateValidationCallback = new
                  RemoteCertificateValidationCallback
                  (ValidateServerCertificate);

            //Selection of file to be uploaded
            byte[] fileContents = new byte[oFile.Length];

            //will destroy the object immediately after being used
            using (FileStream fr = oFile.OpenRead()) {
               fr.Read(fileContents, 0,
               Convert.ToInt32(oFile.Length));
            }
            using (Stream writer = ftpRequest.GetRequestStream()) {
               writer.Write(fileContents, 0, fileContents.Length);
            }
            //Gets the FtpWebResponse of the uploading operation
            ftpResponse = (FtpWebResponse)ftpRequest.GetResponse();
            //Display response
            //Response.Write(ftpResponse.StatusDescription);

            ftpResponse.Close();
            ftpRequest = null;

            return true;
         }
         catch (WebException webex) {
            return false;
            //this.Message = webex.ToString();
         }
      }

      public bool DeleteFile(string Name)
      {
      //1. Create a request: must be in ftp://hostname format,
      //   not just ftp.myhost.com
      FtpWebRequest oFTP = (FtpWebRequest)FtpWebRequest.
         Create(FTPSite + CurrentDirectory + Name);
      //oFTP.EnableSsl = true;
      //2. Set credentials
      oFTP.Credentials = new NetworkCredential(UserName, Password);
      //Define the action required (in this case, download a file)
      oFTP.Method = WebRequestMethods.Ftp.DeleteFile;

      //3. Settings
      oFTP.KeepAlive = KeepAlive;
      oFTP.EnableSsl = UseSSL;
      // Validate the server certificate with
      // ServerCertificateValidationCallBack
      if (UseSSL) ServicePointManager.
         ServerCertificateValidationCallback = new
         RemoteCertificateValidationCallback
         (ValidateServerCertificate);
      //we want a binary transfer, not textual data
      oFTP.UseBinary = true;

      //4. If we were using a method that uploads data;
      //   for example, UploadFile, we would open the
      // ftp.GetRequestStream here an send the data

      //5. Get the response to the Ftp request and the associated
      //   stream
      FtpWebResponse response = (FtpWebResponse)oFTP.GetResponse();
      FtpStatusCode oStat = response.StatusCode;
      response.Close();
      oFTP = null;

      return true;
    } // DeleteFile()


   }
}

Open in new window

0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
cheryl, were you able to tackle that? or what you'll be doing?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.