Use SSIS with WinSCP to Upload Files Via SFTP

This tutorial will explain how to upload files using WinSCP and SSIS.  If you have not already done so, please complete tasks 1-3 in the tutorial. Once the tasks have been completed, you will have all the necessary tools to use WinSCP and SSIS.  

The assumption is that the file already exists on your network.  You may want to verify the file’s existence before attempting to transmit the file. This tutorial assumes that the file does exist.  If you would like to test for a file’s existence before acting on the file, please see the tutorial on using SSIS to test for a file’s existence.

Step 1 - Create new project in SSIS.

Step 2 – Create the package level variables to be used in the project.\

Package Level Variables

Name Scope Data Type Value

SFTP_FingerPrint         Package String See below

SFTP_LogFileName Package String See below

SFTP_Password Package String password

SFTP_PortNumber Package Int32 22

SFTP_Server Package String See below

SFTP_UploadPath Package String /     --  This is the (root directory)

SFTP_User Package String tester

vLocalFileName Package String Upload_test.txt

vLocalFullFilePath         Package String C:\Export

vLocalOutboundFilePath Package String \

1.SFTP_FileName – Name of the file we will be importing from the SFTP server.

2.SFTP_FingerPrint – You can obtain the SshHostKeyFingerprint value by using WinSCP to generate a code template providing you with the key. To learn how.

3.SFTP_FullFileImportPath – Combination of two package variables. @[User::SFTP_ImportPath]+ @[User::SFTP_FileName]

4.SFTP_ImportPath – Path to file on remote server.

5.SFTP_LogFileName – Dynamically created file name using an expression. There are many date expressoin examples for SSIS on the web. This link can provide some detail on the expression. The value will be c:\LogFiles\SFTP_Export_MMDDYY_log.txt, which is generated by using the following expression ( copy all of the text including double quotes):

"c:\\LogFiles\\SFTP_Export_"+(LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) +(LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +(DT_STR,4,1252)(DATEPART("yyyy",GETDATE())%100)+"_log.txt"

6.SFTP_Password – “password”, the password is fixed and defined by Rebex Tiny SFTP server.

7.SFTP_PortNumber– 22, standard for SFTP connections.

8.SFTP_Server – Defined by Rebex Tiny SFTP Server when the server is started. Your value will differ from mine.

9.SFTP_UploadPath –  We will be uploading to the root directory “/”, update it as needed.

10.SFTP_User  – “Tester”, this user name is fixed and defined by Rebex Tiny SFTP server.

11.vLocalFileName – Name of the file to be transmitted, i.e. Upload_test.txt.

12.vLocalFullFilePath – Combination of two variables,  @[User::vLocalOutboundFilePath]+ @[User::vLocalFileName]

13.vLocalOutboundFilePath – Path to the file  export staging directory,  C:\Export\.

SFTP credentials are displayed when Rebex Tiny SFTP Server is started.

Step 3 – Drag a Script task onto the SSIS canvas.

Step 4– Double click on the script task and ensure C# is the selected language.

Step 5 - Mark the following variables as ReadOnly Variables.

1.User::SFTP_FingerPrint

2.User::SFTP_LogFileName

3.User::SFTP_Password

4.User::SFTP_PortNumber

5.User::SFTP_Server

6.User::SFTP_UploadPath

7.User::SFTP_User

8.User::vLocalFullFilePath

Your script task editor window should resemble the following image.

Step 6 -Click on the “Edit Script” button to open the script task editor. We will need to add a reference to the WinSCP.net Assembly in the next step.  We will also add WinSCP to the list of namespaces.

Step 7 - Add a reference to the WinSCP.net assembly.

Right click on References and select the “Add Reference” menu item.

Click on the browse button and navigate to the directory the WinSCP.net assembly is stored.

Select the WinSCP dll file, and click the “Add” button.

Click on “Ok” on the next window.

Add WinSCP to the namespace. Remove the namespaces for System.Data and System.Windows.Forms.

Step 8 – Enter the script to call the WinSCP.net assembly.

Add the following script to create and entry point for  calling  the WinSCP.net assembly from a location outside of the Global Assembly Cache (GAC). Placement is just above public void Main(). The source of this code and more detailed information can be found here Assembly in SSIS outside of the GAC.

static ScriptMain()

   {

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

   }

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

       {

           if (args.Name.Contains("WinSCPnet")) //Set the “SSIS helper”

           {

               string path = @"C:\csharp_lib\WinSCP.Net\"; //Set path to assembly.

               //Set the SSIS helper DLL.

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

           }

           return null;

       }

Step 9– Populate values for SessionOptions class properties.

 public void Main()

 {

   SessionOptions sessionOptions = new SessionOptions

   {                

     Protocol = Protocol.Sftp,

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

     PortNumber = (int)Dts.Variables["User::SFTP_PortNumber"].Value,

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

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

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

   };

Step 10– Encapsulate the remaining code in a Try…Catch statement

try

   {

    using (Session session = new Session())

     {

       //Set absolute path for log file creation.

         session.SessionLogPath =Dts.Variables["User::SFTP_LogFileName"].Value.ToString();

 

       //Automatic connection and authentication of the session.

         session.Open(sessionOptions);

               

       //Default constructor.

        TransferOptions transferOptions = new TransferOptions();//Default constructor.

             

       //Use binary mode to transfer all files unless you know the file contains only

       //text, then you are able to use ASCII mode.

         transferOptions.TransferMode = TransferMode.Binary;

               

       //With SFTP protocol, WinSCP by default transfers files over 100 KB via a

       //temporary file. It will fail if you do not have permissions to create a new

       //file. Turning the option OFF avoids possible failure.

         transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;

               

       //Upload files to the server.

         TransferOperationResult transferResult = session.PutFiles(@Dts.Variables["User::vLocalFullFilePath"].Value.ToString(), Dts.Variables["User::SFTP_UploadPath"].Value.ToString(), false, transferOptions);

                 

 

       //Checks if there is any failure

         transferResult.Check();

 

       // Print results, displayed on Execution Results tab.

            bool fireAgain = false;

                foreach (TransferEventArgs transfer in transferResult.Transfers)

                {

                    Dts.Events.FireInformation(0, null,

                    string.Format("Upload 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 upload files: {0}", e),

            null, 0);

 

            Dts.TaskResult = (int)DTSExecResult.Failure;

        }

Step 11 – Testing

Create a text file name Upload_test.txt in your C:\Export directory.

Start Tiny SFTP server by double clicking on the Rebex Tiny SFTP Server executable file in the directory containing the application contents.

Click on the start button in the application window.

You should see a window resembling the following. Your server IP will differ.

Once the SFTP server is started, execute the package. Navigate to the “data” directory in the folder that contains the Rebex Tiny SFTP Server application files.  You should see the Upload_test.txt file there.

Navigate to C:\Logs, you should see a log file for your WinSCP session.

Code in its Entirety

#region Namespaces

using System;

using Microsoft.SqlServer.Dts.Runtime;

using WinSCP;

#endregion

namespace ST_baea964635034bbf9735e63b0f979109

{

[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:\csharp_lib\WinSCP.Net\";

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

           }

           return null;

       }

       public void Main()

{

           // Set up session options

           SessionOptions sessionOptions = new SessionOptions

           {

               Protocol = Protocol.Sftp,

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

               PortNumber = (int)Dts.Variables["User::SFTP_PortNumber"].Value,

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

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

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

           };

           try

           {

               using (Session session = new Session())

               {

                   session.SessionLogPath = Dts.Variables["User::SFTP_LogFileName"].Value.ToString();

                   session.Open(sessionOptions);

       

                   TransferOptions transferOptions = new TransferOptions();

                   transferOptions.TransferMode = TransferMode.Binary;

                   transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;

                   TransferOperationResult transferResult = session.PutFiles(@Dts.Variables["User::vLocalFullFilePath"].Value.ToString(), Dts.Variables["User::SFTP_UploadPath"].Value.ToString(), false, transferOptions);

                 

                 

                   transferResult.Check();

                 

                   bool fireAgain = false;

                   foreach (TransferEventArgs transfer in transferResult.Transfers)

                   {

                       Dts.Events.FireInformation(0, null,

                           string.Format("Upload 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 upload files: {0}", e),

                   null, 0);

               Dts.TaskResult = (int)DTSExecResult.Failure;

           }

       }

 

       enum ScriptResults

       {

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

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

       };

   

   }

}

I hope you found this tutorial helpful.