Sending a local file to SFTP server with script task SSIS

Question

I consulted several forums which generally explain 2 possible solutions:
1- a paid SSIS component that does the job and configure it as a task ssis
2- the passage by a third party software which makes the sending (the most used is WinSCP) and in SSIS:
Use "execute task" to trigger an execution of a script in WinSCP

But within the company they force us to look for a 100% SSIS solution with a C# script (script task) or other: how to do that?
Below is a C# code that has errors now: do you offer corrections or a C# code that works to send local files to SFTP server ?

---------- CODE C#--------------

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 Renci.SshNet;
using System.IO;

endregion

namespace ST_d27fb029ddab4957bc327e8fc4c35c18
{
/// <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
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */

    #endregion

    #region Help:  Firing Integration Services events from a script
    /* This script task can fire events for logging purposes.
     * 
     * Example of firing an error event:
     *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
     * 
     * Example of firing an information event:
     *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
     * 
     * Example of firing a warning event:
     *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
     * */
    #endregion

    #region Help:  Using Integration Services connection managers in a script
    /* Some types of connection managers can be used in this script task.  See the topic 
     * "Working with Connection Managers Programatically" for details.
     * 
     * Example of using an ADO.Net connection manager:
     *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
     *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
     *  //Use the connection in some code here, then release the connection
     *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
     *
     * Example of using a File connection manager
     *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
     *  string filePath = (string)rawConnection;
     *  //Use the connection in some code here, then release the connection
     *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
     * */
    #endregion


    /// <summary>
    /// This method is called when this script task executes in the control flow.
    /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    /// To open Help, press F1.
    /// </summary>
    public void Main()
    {
        // TODO: Add your code here
        string Server = Dts.Variables["FTPServer"].Value.ToString();
        string User = Dts.Variables["FTPUser"].Value.ToString();
        string Password = Dts.Variables["FTPPassword"].Value.ToString();
        int port = (int) Dts.Variables["FTPPort"].Value;
       // string Folder = Dts.Variables["FTPFolder"].Value.ToString();

        string localFolder = Dts.Variables["ExportEgypte_LocalFolderPath"].Value.ToString(); ;
        string fileName = Dts.Variables["fileName"].Value.ToString();
        // string absoluteFileName = Path.GetFileName(XMLFileName);
        // string FullName = localFolder + "SubFolderName." + fileName;
        string FullName = localFolder  + fileName;
        string remotePath = Dts.Variables["FTPRemoteFolder"].Value.ToString() + fileName; ;
        uploadFile(Server, port, Password, User, FullName,remotePath);

        Dts.TaskResult = (int)ScriptResults.Success;

    } 

    void uploadFile(string FTPServer,int port,string FTPPassword,string FTPUser,string localFilePath,string remotePath)
        {
            try
            {
                using (var client = new SftpClient(FTPServer, port, FTPUser, FTPPassword))
                {
                    client.Connect();
                    if (!client.IsConnected)
                    {
                        throw new Exception("Not Connected");
                    }
                    using (FileStream fs = File.OpenRead(localFilePath))
                    {
                        client.UploadFile(fs, remotePath, true, null);
                    }
                    client.Disconnect();
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception);
                throw;
            }
        }






    #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

}

}

Answer 1

Below is a C# code that has errors now:

And which error message do you get and on which line of code?

Answer 2

Hello Phil ,

Really i'm writing to say "Many thanks" for you : So the problem is sloved in Desktop , it's a DLL files that should be installed in the global cache with Commands Prompts of visual studio , after we can call the specified references Renci in the C# Code . This is a helpful link that describes the solution : https://markedcode.com/index.php/2020/04/27/how-to-access-sftp-with-ssis-using-ssh-net/

**But we still have the problem in cloud (IR SSIS) ** , my project is deplyed in Azure server (DEV) and i should install gacutil and these file in the specified folder , and i tried to modify the main.cmd in Azure storage now :

I follow this link : https://sql-stijn.com/2021/02/03/importing-dll-into-ssis-integration-runtime/
and I write the code that i inseted in main.cmd :

gacutil /i Renci.SshNet.dll /f

gacutil /i SshNet.Security.Cryptography.dll /f

--> the code works fine in my Local (with command prompt VS) , So if you have any links to help, please reply for me , thanks .

Best regards,

AHMED