Downloading Files from the SFTP Server

This post is part of a larger project. You will need to create the following project level variables. vDate, vSFTPInboundFileName, and vSftpLogFilename will use expressions to populate the variables.

Name Data Type   Value

1.vDate String Current date in YYYYYMMDD format.

(DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())

+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)

+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

2.vSftpServerUserName String User name for logging into SFTP server.

3.vSftpServerUserPassword         String Password for logging into SFTP server.

4.vSftpInboundFileName String This value searches for all files beginning with "Test_", followed by the                                                                                 current date, ending with "-.zip" within the root directory."/Test_"+                                                                                 @[User::vDate] +"_.zip"

5.vSftpInboundFilePath         String This value is local. But can be anywhere on your network. C:\Staging

6.vSftpLogFilename String Path and name for the log file that will be generated during the WinSCP                                                                                 session. Date including the hour, minute, and second will be embedded                                                                                 in the file name.                                                                                 "C:\Logs\Document_Import_"+ @[User::vDate]                                                                                +Right("0" + (DT_STR,2,1252) DATEPART("hh",GETDATE()),2)                                                                                + Right("0" + (DT_STR,2,1252) DATEPART("mi",GETDATE()),2)
                                                                               + Right("0" + (DT_STR,4,1252) DATEPART("ss",GETDATE()),2)+"_log.txt"

7.vSftpServerUserName String The name or IP address of the server.

8.vSftpServerHostKey         String The host key for the server.

You will need to obtain the WinSCP .exe and .dll files before implementing WinSCP functionality.

1. Drag the script task onto the SSIS canvas.

2.Configure the script task to use C# as the selected language.

3.Identify the ReadOnlyVariables and click on “OK”.

4.Your script task editor should resemble the following image.

5.Click on “Edit Script” and import the necessary namespaces.

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using WinSCP;

using System.Collections.Generic;

using System.Net.Mail;

using System.IO;

using System.Text;

using System.Text.RegularExpressions;

#endregion

6.Enter the following code to call the WinSCP.net assembly. The path variable should reference your location for the assembly.

static ScriptMain()

       {

           AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);

       }

       static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)

       {

           if (args.Name.Contains("WinSCPnet"))

           {

               string path = @"C:\lib_csharp\WinSCP.Net";

               return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));

           }

           return null;

       }

       //end

7. Create the script variables.

     public void Main()

       {

           // Set up session options

           SessionOptions sessionOptions = new SessionOptions

           {

               Protocol = Protocol.Sftp,

               HostName = Dts.Variables["User::vSftpServerName"].Value.ToString(),

               //PortNumber = 22, //Port is 22 by default.

               UserName = Dts.Variables["User::vSftpUserName"].Value.ToString(),

               Password = Dts.Variables["User::vSftpServerUserPassword"].Value.ToString(),

               SshHostKeyFingerprint = Dts.Variables["User::vSftpServerHostKey"].Value.ToString(),      

           };

8. Encapsulate WinSCP code in try...catch statement.

Note the following script "Dts.Variables["User::vSftpInboundFilePath"].Value.ToString(), true, transferOptions);" The value of "true" will delete the file after the download is complete. Changing the value to "false" will leave the server file intact.

try

           {

               using (Session session = new Session())

               {

                   session.Open(sessionOptions);

                   session.DebugLogLevel = 0;

                   

                   //Create session log file.

                   session.DebugLogPath = Dts.Variables["User::vSftpLogFilename"].Value.ToString();

               

                       TransferOptions transferOptions = new TransferOptions();

                       transferOptions.TransferMode = TransferMode.Binary;

                       transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;

                        //Download the files and delete the files on the server once download is complete.  

                       TransferOperationResult vSftpTransferResult = session.GetFiles(Dts.Variables["User::vSftpInboundFileName"].Value.ToString(), Dts.Variables["User::vSftpInboundFilePath"].Value.ToString(), true, transferOptions);

                       // Throw on any error

                       vSftpTransferResult.Check();

                       // Print results

                       bool fireAgain = false;

                       foreach (TransferEventArgs transfer in vSftpTransferResult.Transfers)

                       {

                           Dts.Events.FireInformation(0, null,

                               string.Format("Download of {0} succeeded", transfer.FileName),

                               null, 0, ref fireAgain);

                       }

                   

         

               }

               Dts.TaskResult = (int)ScriptResults.Success;

           }

           catch (Exception e)

           {

               Dts.Events.FireError(0, null,

                   string.Format("Error when using WinSCP to download files: {0}", e),

                   null, 0);

               Dts.TaskResult = (int)DTSExecResult.Failure;

           }

         

The code in its entirety.

#region Help:  Introduction to the script task

/* The Script Task allows you to perform virtually any operation that can be accomplished in

* a .Net application within the context of an Integration Services control flow.

*

* Expand the other regions which have "Help" prefixes for examples of specific ways to use

* Integration Services features within this script task. */

#endregion

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using WinSCP;

using System.Collections.Generic;

using System.Net.Mail;

using System.IO;

using System.Text;

using System.Text.RegularExpressions;

#endregion

namespace ST_7196ea3db49a4422838421d1e71fd54a

{

   /// <summary>

   /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,

   /// or parent of this class.

   /// </summary>

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

       public object Lists { get; private set; }

       static ScriptMain()

       {

           AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);

       }

       static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)

       {

           if (args.Name.Contains("WinSCPnet"))

           {

               string path = @"C:\lib_csharp\WinSCP.Net";

               return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));

           }

           return null;

       }

       //end

       public void Main()

       {

           // Set up session options

           SessionOptions sessionOptions = new SessionOptions

           {

               Protocol = Protocol.Sftp,

               HostName = Dts.Variables["User::vSftpServerName"].Value.ToString(),

               //PortNumber = 22, //Update only if the port number needed for connection differs from port 22

               UserName = Dts.Variables["User::vSftpUserName"].Value.ToString(),

               Password = Dts.Variables["User::vSftpServerUserPassword"].Value.ToString(),

               SshHostKeyFingerprint = Dts.Variables["User::vSftpServerHostKey"].Value.ToString(),      

           };

           try

           {

               using (Session session = new Session())

               {

                   session.Open(sessionOptions);

                   session.DebugLogLevel = 0;

                   session.DebugLogPath = Dts.Variables["User::vSftpLogFilename"].Value.ToString();

               

                       TransferOptions transferOptions = new TransferOptions();

                       transferOptions.TransferMode = TransferMode.Binary;

                       transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;

                       TransferOperationResult vSftpTransferResult = session.GetFiles(Dts.Variables["User::vSftpInboundFileName"].Value.ToString(), Dts.Variables["User::vSftpInboundFilePath"].Value.ToString(), true, transferOptions);

                       // Throw on any error

                       vSftpTransferResult.Check();

                       // Print results

                       bool fireAgain = false;

                       foreach (TransferEventArgs transfer in vSftpTransferResult.Transfers)

                       {

                           Dts.Events.FireInformation(0, null,

                               string.Format("Download of {0} succeeded", transfer.FileName),

                               null, 0, ref fireAgain);

                       }

                   

         

               }

               Dts.TaskResult = (int)ScriptResults.Success;

           }

           catch (Exception e)

           {

               Dts.Events.FireError(0, null,

                   string.Format("Error when using WinSCP to download files: {0}", e),

                   null, 0);

               Dts.TaskResult = (int)DTSExecResult.Failure;

           }

         

       }

   

       #region ScriptResults declaration

       /// <summary>

       /// This enum provides a convenient shorthand within the scope of this class for setting the

       /// result of the script.

       ///

       /// This code was generated automatically.

       /// </summary>

       enum ScriptResults

       {

           Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

           Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

       };

       #endregion

   }

   

}