No SFTP Task Component in SSIS 2005/2008? No Problem!

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    Comments posted to this topic are about the item No SFTP Task Component in SSIS 2005/2008? No Problem!

  • Craig Lucas

    SSC Veteran

    Points: 242

    Cool article. I like CozyRoc's SFTP task too

  • albertarun

    SSC Veteran

    Points: 231

    I am using a similar SFTP 3rd party application in one of my ODS loading SSIS package.

    The problem is that the error handling is generalized and not specific.

    I suggest searching for dlls that can be called through script task which can pass error messages to package variable which can be included in the sent mail task. You can also compare already downloaded files against your audit table there by avoiding duplicate downloads.

  • mackie

    Right there with Babe

    Points: 745

    Nice article, thanks for sharing Stan. I very recently sat with the same issue, and after some investigation into different solutions to the issue I decided to use Core FTP light. I added variables to my SSIS package, configured an Execute Process task that passed the valid commands to the executable (via variables), added file archiving steps to ensure good housekeeping, and voila! Bob's your uncle. Worked like a charm. No extra coding required (other than basic variable expressions) 🙂

  • mrpolecat

    SSCertifiable

    Points: 6934

    I've been doing this with DTS since SQL7. One catch I don't see mentioned is that you have to log in manually the first time to cache the key. Otherwise the process hangs waiting for the response from the user to verify the key. This can also be a problem when the key gets changed.


  • A. Fox

    SSC Enthusiast

    Points: 168

    What concerns me about this approach is that you leave a batch file hanging around that contains your password. You have to be very careful in your cleanup and error handling routines so that you don't leave the auto-generated batch file sitting in your working directory.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    "You have to be very careful in your cleanup and error handling routines so that you don't leave the auto-generated batch file sitting in your working directory."

    That's why the last section of code deletes the batch file and the sftp script.

  • Peter-507067

    SSC Journeyman

    Points: 81

    Good article, I have been using bitvise's product sftpc.exe which has very good logging giving me the ability to not only use the SSIS embedded start process task but I can start it with a logging exe to produce transfer log files for each file which are in turn emailed to distribution lists.

    Within the Start process task I have setup two Expressions to set the Arguments and Executable.

    Arguments - @[User::SFTPC_LOG_ARGS] + " " + @[User::SFTPC_LOG_FILE_PATH] + " " + @[User::SFTPC_LOCATION] + " " + @[User::UserName] + "@" + @[User::Destination] + ":" + @[User::Port] + " -pw=" + @[User::Password] + " " + @[User::MD5_BB] + " -cmd=" + @[User::Command]

    Executable - @[User::SFTPC_LOG_LOCATION]

  • WayneS

    SSC Guru

    Points: 95342

    Good article.

    Having that username/password in a batch file is an issue. If (for any reason, like the FTP server is down) the package fails, that file could be left behind, and then anyone could see it. You might be deleting it in the last step, but if the package fails before that step then that last step won't get run.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • markmeiller

    Old Hand

    Points: 306

    Good article. I have in the past used a simalar solution with Winscp, however we recently change some of our poliacys and the SSIS packagewas moved to a server where 3rd party apps where forbiden. To get around this i found out that you could easly use the c# ftp class in a script task to do the same thing & it's more relable, and faster cause the server doesn't have to fire up another application.

    //Get Package Varables

    String MyFTPSiteName = Dts.Variables["User::FTPHost"].Value.ToString();

    String MyFTPLogin = Dts.Variables["User::FTPUser"].Value.ToString();

    String MyFTPPassWord = Dts.Variables["User::FTPPassWord"].Value.ToString();

    String MyFolder = Dts.Variables["User::Folder"].Value.ToString();

    String MyPeriod = Dts.Variables["User::Period"].Value.ToString();

    String MyFileName = Dts.Variables["User::File"].Value.ToString();

    //Accept TLS/SSL Certificate

    ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(AcceptAllCertificatePolicy);

    //Upload Data File

    {

    FtpWebRequest MyRequest = FtpWebRequest.Create(MyFTPSiteName + "/" + MyFileName ) as FtpWebRequest;

    MyRequest.EnableSsl = true;

    MyRequest.Credentials = new NetworkCredential(MyFTPLogin, MyFTPPassWord);

    MyRequest.Method = WebRequestMethods.Ftp.UploadFile;

    //Read File into byte array

    FileInfo MyFileI = new FileInfo(MyFolder + "\\"+MyFileName );

    FileStream MyFStream = new FileStream(MyFolder + "\\" + MyFileName, FileMode.Open, FileAccess.Read);

    BinaryReader MyReader = new BinaryReader(MyFStream);

    byte[] MyFile = MyReader.ReadBytes(int.Parse(MyFileI.Length.ToString()));

    MyReader.Close();

    MyFStream.Close();

    MyRequest.ContentLength = MyFile.Length;

    //Upload File

    Stream MyStream = MyRequest.GetRequestStream();

    MyStream.Write(MyFile, 0, MyFile.Length);

    MyStream.Close();

    FtpWebResponse MyResponse = MyRequest.GetResponse() as FtpWebResponse;

    MyResponse.Close();

    }

    public bool AcceptAllCertificatePolicy(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)

    {

    return true;

    }

  • albertarun

    SSC Veteran

    Points: 231

    awesome. this was something that I was looking for. Adding 3rd party apps to my ETL is a little mickey mouse for me.

    Thank You very much.

  • SQLRNNR

    SSC Guru

    Points: 281205

    Nice article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • John Barnett

    SSCommitted

    Points: 1946

    An alternative method would be to set up a "virtual" FTP server using stunnel (www.stunnel.org) pointing to the remote sftp server, which you then connect using normal ftp uploads, and sftp handles the SSL element.

  • mecurioJ

    SSC Rookie

    Points: 41

    Interesting article, but I chose a slightly different path in handling things. I am using the portable version of WinSCP, meaning there is no installation needed. They also expose Command Line arguments, meaning that all the stuff stored in the batch file is passed along as variables in the package. So I can store the variables in a database somewhere, call them when the package loads, then pass them along to the command line options. Consider the following:(written in C#...)

    //Attaching the debugger in a 64-bit instance of SSIS will not work.

    //MessageBox.Show("Attach the Debugger", "Attach the Debugger");

    // TODO: Add your code here

    ScriptResults TaskResult;

    //jf 04132010 Created objects to hold variables set at package level

    String WinSCPPath = Dts.Variables["WinSCPPath"].Value.ToString();

    String WinSCPCom = Dts.Variables["WinSCPCom"].Value.ToString(); ;

    String WinSCPini = Dts.Variables["WinSCPini"].Value.ToString();

    String WinSCPLoggingPath = Dts.Variables["WinSCPLoggingPath"].Value.ToString();

    String WinSCPLogFileName = Dts.Variables["WinSCPLogFileName"].Value.ToString();

    String LoginName = Dts.Variables["LoginName"].Value.ToString();

    String LoginPassword = Dts.Variables["LoginPassword"].Value.ToString();

    String SiteURI = Dts.Variables["SiteURI"].Value.ToString();

    String HostKey = Dts.Variables["HostKey"].Value.ToString();

    String FileToPush = Dts.Variables["FileToPush"].Value.ToString();

    String UploadDirectory = Dts.Variables["UploadDirectory"].Value.ToString();//UploadDirectory

    //jf 04132010 Created object to hold reference for FireAgain of the FireProgress Control

    bool fireAgain = false;

    try

    {

    //jf 04132010 Attach winscp to the process within the Package

    System.Diagnostics.Process winscp = new System.Diagnostics.Process();

    Dts.Events.FireProgress("WinSCP process Created", 0, 0, 100, "WinSCP", ref fireAgain);

    //jf 04132010 Pass variables into the Process object

    //Tell the Process where to find the WinSCP object for loading

    winscp.StartInfo.FileName = String.Format("{0}{1}"

    , WinSCPPath

    , WinSCPCom);

    //Pass the location of the ini file and the logging xml file to the Process

    winscp.StartInfo.Arguments = String.Format("/log={3}{1}"

    , WinSCPPath

    , WinSCPLogFileName

    , WinSCPini

    , WinSCPLoggingPath);

    Dts.Events.FireProgress("Assigned Logging File", 10, 0, 100, "WinSCP", ref fireAgain);

    //Do not launch a new shell "cmd" window

    winscp.StartInfo.UseShellExecute = false;

    //Redirect the input and output to the Logging Component

    winscp.StartInfo.RedirectStandardInput = true;

    winscp.StartInfo.RedirectStandardOutput = true;

    //Do not show a cmd window

    winscp.StartInfo.CreateNoWindow = true;

    //Start the Process Thread

    winscp.Start();

    Dts.Events.FireProgress("WinSCP process Launched", 20, 0, 100, "WinSCP", ref fireAgain);

    //Pass configuration information to WinSCP

    winscp.StandardInput.WriteLine("option batch abort");

    winscp.StandardInput.WriteLine("option confirm off");

    //Pass Login information to WinSCP for Authentication

    winscp.StandardInput.WriteLine(String.Format("open {0}:{1}@{2} -hostkey=\"{3}\""

    , LoginName

    , LoginPassword

    , SiteURI

    , HostKey));

    Dts.Events.FireProgress("Remote SFTP Connection Established", 40, 0, 100, "WinSCP", ref fireAgain);

    //Execute WinSCP Commands

    winscp.StandardInput.WriteLine("ls");

    //--change to the needed directory

    winscp.StandardInput.WriteLine(String.Format("cd {0}", UploadDirectory));

    Dts.Events.FireProgress("Remote SFTP File Action", 60, 0, 100, "WinSCP", ref fireAgain);

    //--List the directory

    winscp.StandardInput.WriteLine("ls");

    //--push the file to the server

    winscp.StandardInput.WriteLine(String.Format("put {0}", FileToPush));

    Dts.Events.FireProgress("Process Finished", 80, 0, 100, "WinSCP", ref fireAgain);

    Dts.Events.FireProgress(String.Format("put {0}", FileToPush), 0, 0, 0, "WinSCP", ref fireAgain);

    //Close input to the file

    winscp.StandardInput.Close();

    Dts.Events.FireProgress("Remote SFTP Disconnected, WinSCP Closed", 100, 0, 100, "WinSCP", ref fireAgain);

    //wait for WinSCP to Exit

    winscp.WaitForExit();

    MessageBox.Show(winscp.StandardOutput.ReadToEnd(), "WinSCP Output");

    TaskResult = ScriptResults.Success;

    }

    catch (Exception e)

    {

    //If there is problem, fail the event and output the exception Message...

    Dts.Events.FireError(999, e.Message, "WinSCP", string.Empty, 0);

    TaskResult = ScriptResults.Failure;

    //MessageBox.Show(e.Message);

    }

    Dts.TaskResult = (int)TaskResult;

    Dts.TaskResult = (int)TaskResult;

    Notice that we can also fire events that bubble up a message within the context of the package. This means no third party installation on the server, no batch file to be overwritten and most important of all, everything is saved in a script task in the package.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    Pretty cool.

    You should submit that as an article. I did what I did because it's the only thing I could figure out.

    Your way is better.

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

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