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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason YousefSr. 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
Jason YousefSr. 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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Jason YousefSr. BI  DeveloperCommented:
0
Jason YousefSr. 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 YousefSr. 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 YousefSr. 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jason YousefSr. BI  DeveloperCommented:
cheryl, were you able to tackle that? or what you'll be doing?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.