How to Use SSIS with WinSCP to Download Files via SFTP

Photo by: istockphoto.com

In today’s adventure, I will guide you on how to create a package that you can use to automate file transfers using SFTP in SSIS. Yes, I know API…API…. API. Not everyone has the infrastructure or skill set to handle APIs. Similarly there are still organizations using COBOL. COBOL may be outdated but is still solid and serves a purpose. There are third party tools that will help you address SFTP’ing files relatively easily. But, like with anything else, if you don’t already have the solution, just build it. It’s not as complicated as it seems. Don’t worry gentle reader, I got you. With the right setup and a few simple steps, you can get it working smoothly. Granted, I am using the word “simple” loosely, since everything is difficult when you are learning. The good news is that in this guide, I’ll walk you through how to set up an SSIS script task that uses the WinSCP.net assembly to download files over SFTP.

We’ll break this down into four main tasks:

1. Setting up the required directories for our SSIS script.
2. Installing WinSCP and grabbing the necessary files.
3. Setting up a local SFTP server for testing (if you don’t have one already).
4. Creating the SSIS project and writing the script to interact with WinSCP.

In the words of the great Mills Lane, Let’s get it on!

Task 1: Setting Up the Directories

Before we get started with the actual code, let’s first create the directories we’ll be using in this tutorial. These are where our files will be downloaded, staged, and logged.

• C:\Csharp_lib\WinSCP This directory is where we’ll store the WinSCP executable and DLL files. You can choose a different name or path if you prefer, but for this tutorial, stick with the naming convention to avoid confusion.
• C:\Import This folder will act as a staging area for the files we download via SFTP. It’s used in this tutorial, but you can modify it later if you need to.
• C:\Export This one’s for the next tutorial (where we’ll upload files via SFTP), but it’s optional for now. It will be the target directory for files you send via SFTP.
• C:\LogFiles This directory will hold all the log files generated by our SSIS script task. It’s useful for troubleshooting and monitoring file transfers.
• Rebex Tiny SFTP server creates a testfile.txt file during setup. It can be located in the folder that contains the application. I installed the application on my Desktop. Create testfile.txt file if one does not exist. The path to the root (\Data) is the following on my machine:

C:\Users\enriq\OneDrive\Desktop\Tiny SFTP Server\data

Make sure the directories you use for storing your DLL files, log files, and import directory are consistent across all environments ( i.e. your local machine, Dev, QA, and Production). The last thing you want is a mismatch in folder structures when you deploy the package. Your script task will fail if they are mismatched.

Task 2: Installing WinSCP and Getting the Files

Now, let’s get the WinSCP files you need.

1. Download and Install WinSCP Head over to WinSCP’s website, and download the installer. Once it’s installed, you’ll find the executable and DLL files in your C:\Program Files (x86)\WinSCP directory (assuming you installed the 32-bit version)

Installing the application:

• Double click on the installation file and select “Install for all users”. This will make WinSCP available for all user accounts on your PC.

• Accept the license agreement.

• Select the option for typical installation, and click on the “Next” button.

• Verify that the “Commander” option is selected, and click on the next button.

• Click on the “Install” button.

• At this point you can either click on “Finish” to complete the installation of make a donation using one of the links. A donation is optional. Click on the “Finish” button after making your decision.

1. Copy the WinSCP Files Inside the C:\Program Files (x86)\WinSCP folder, copy these two files:

• WinSCP.exe
• WinSCPnet.dll

Then, paste them into the directory we set up earlier: C:\Csharp_lib.

Remember that the paths and files in each of your environments must always match. Local, Dev, QA, and Production must always be in sync.

You’re all set with the WinSCP files! So far so good! Easy right?

Task 3: Setting Up a Local SFTP Server (Optional)

If you don’t have access to an SFTP server for testing, again, I got you, you can use a simple local server for development. I recommend using Rebex Tiny SFTP Server: (full disclosure)I am not affiliated with them and any, way, shape, or form. I simply found their (free) tool useful. If you need a step by step guide on installing Tiny SFTP Server, I wrote one.

1. Download the Rebex Tiny SFTP Server Go to Rebex Tiny SFTP Server and download the zip file. Unpack it to any directory you like. It’s free for both commercial and non-commercial use, but just keep in mind that it’s not Production tool.
2. Start the Server After unpacking, open the folder and double-click the TinySftpServer.exe to run the server. Click the “Start” button in the app window, and your local SFTP server is now up and running.

If you’re using this server, your default login credentials will be:

• Username: tester
• Password: password

Task 4: Creating the SSIS Project and Script Task

Now we’re ready to create the SSIS package that will handle the file download, using the WinSCP.net assembly.

• Step 1: Create the SSIS Project Start by creating a new SSIS project in SQL Server Data Tools (SSDT). Once your project is set up, create a new package.
• Step 2: Add a Script Task Drag a Script Task onto the Control Flow canvas. This is where the magic happens!
• Step 3: Configure the Script Task Double-click the script task to open the editor, and make sure C# is selected as the language.

Next, we need to specify the SSIS variables that the script task will use. Here are the variables you’ll need to create at the package level:

Your variables will look something like this after setting them up.

Some notes on the variables:

• SFTP_FingerPrint: You can get the SshHostKeyFingerprint value from WinSCP, which will help ensure you’re connecting securely to the server.
• SFTP_LogFileName: This is a dynamic log file name based on the current date. Here’s an SSIS expression to generate it:

"c:\\LogFiles\\SFTP_Import_" + 
(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"    

This is what it will look like once you use it in the variable expression. The expression task allows you to customize your filename at runtime.

Step 4: Edit the Script Double click on the script task. There is an ellipses (…) to the right of “ReadOnlyVariable”, click on it. Define the Read/Only variables so that your script task can see them. Select each of the variables that are relevant. The selections should resemble the image below.

Next, do the same thing for ReadWriteVariables. Your selection should resemble the image below.

Click the Edit Script button to open the script editor. We need to add a reference to WinSCPnet.dll.

• Add the Reference Right-click on References in the Solution Explorer, then select Add Reference. Browse to the directory where WinSCPnet.dll is located (e.g., C:\Csharp_lib) and add it.

• Click on the “OK” button once you have located and selected the file.

• Add the WinSCP Namespace In the using statements at the top, add WinSCP. The end result should resemble the following:

// Import necessary namespaces
using System; // Provides basic functionalities
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime; // Provides access to SSIS runtime objects
using WinSCP; // Used for interacting with SFTP via the WinSCP .NET library

Resolve the Assembly To ensure SSIS can find WinSCPnet.dll, you need to add a custom assembly resolver. Place the following code just above the public void Main() method:

 static ScriptMain()
        {
            // Event handler to resolve missing assemblies dynamically
            AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
        }

        // Method to load the WinSCP .NET assembly 
        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            // Check if the assembly being requested is "WinSCPnet"
            if (args.Name.Contains("WinSCPnet"))
            {
                // Define the path to the assembly
                string path = @"C:\Csharp_lib\";
                // Load and return the assembly
                return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
            }
            return null; // Return null if not found
        }

Step 5: Write the Main Script Logic

Here’s the code to set up the SFTP session and download the file. The entire script is provided near the end of the article, so that you can see how it all fits together.

        // Main method executed when the script runs
        public void Main()
        {
            // Define session options for SFTP connection
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp, // Protocol to use: SFTP
                HostName = Dts.Variables["User::SFTP_Server"].Value.ToString(), // SFTP server address
                PortNumber = (int)Dts.Variables["User::SFTP_PortNumber"].Value, // SFTP port
                UserName = Dts.Variables["User::SFTP_User"].Value.ToString(), // SFTP username
                Password = Dts.Variables["User::SFTP_Password"].Value.ToString(), // SFTP password
                SshHostKeyFingerprint = Dts.Variables["User::SFTP_FingerPrint"].Value.ToString(), // SSH host key fingerprint for security
            };

            try
            {
                // Establish a connection with the SFTP server
                using (Session session = new Session())
                {
                    // Set the session log file path
                    session.SessionLogPath = Dts.Variables["User::SFTP_LogFileName"].Value.ToString();

                    // Open the session 
                    session.Open(sessionOptions);

                    // Retrieve the remote file path from an SSIS variable
                    string curFile = Dts.Variables["User::SFTP_FullFileImportPath"].Value.ToString();

                    // Check if the file exists on the remote server
                    if (session.FileExists(curFile))
                    {
                        // Configure transfer options for downloading the file
                        TransferOptions transferOptions = new TransferOptions
                        {
                            TransferMode = TransferMode.Binary, // Use binary mode for file transfer
                            ResumeSupport = { State = TransferResumeSupportState.Off } // Disable resume support
                        };

                        // Download the file to the local path, found in the SSIS variable
                        TransferOperationResult transferResult = session.GetFiles(
                            curFile,
                            Dts.Variables["User::vLocalFilePath"].Value.ToString(),
                            false,
                            transferOptions
                        );

                        // Check the transfer result for errors
                        transferResult.Check();

                        // Delete the file from the remote server after a successful download
                        session.RemoveFiles(curFile);

                        // Set the flag variable to 1, showing the file was found 
                        Dts.Variables["User::vFileExistsFlag"].Value = 1;

                        // Log success messages for the download
                        bool fireAgain = false;
                        foreach (TransferEventArgs transfer in transferResult.Transfers)
                        {
                            Dts.Events.FireInformation(0, null,
                                string.Format("Download and deletion of {0} succeeded", transfer.FileName),
                                null, 0, ref fireAgain);
                        }
                    }
                    else
                    {
                        // File does not exist; set the flag variable to 0
                        Dts.Variables["User::vFileExistsFlag"].Value = 0;
                    }
                }

                // Indicate task success
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                // Log the error details
                Dts.Events.FireError(0, null,
                    string.Format("Error when using WinSCP to download and delete files: {0}", e),
                    null, 0);

                // Indicate task failure
                Dts.TaskResult = (int)DTSExecResult.Failure;
            }
        }

I know what you are thinking. It is not as difficult as it looks. You got this. Worst case scenario, if it does not work the first time, is that you find an opportunity to trouble shoot the script and discover where the disconnect is. We have all been there.

Once the package is complete, run it to test the SFTP file download. If you set everything up correctly, you should see the testfile.txt file in the C:\Import folder, and a log will be created in C:\LogFiles. The script will also delete the file, from the server, after a successful download.

C:\Import directory.

C:\LogFiles directory

Script

#region Namespaces
// Import necessary namespaces
using System; // Basic functionality
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime; // Access to SSIS runtime objects
using WinSCP; // Used for SFTP process via the WinSCP .NET library
#endregion

namespace ST_baea964635034bbf9735e63b0f979109
{
    // Entry point for the SSIS Script Task
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        // Assembly resolution
        static ScriptMain()
        {
            // Event handler to resolve missing assemblies dynamically
            AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
        }

        // Method to load the WinSCP .NET assembly 
        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            // Check if the assembly being requested is "WinSCPnet"
            if (args.Name.Contains("WinSCPnet"))
            {
                // Define the path to the assembly
                string path = @"C:\Csharp_lib\";
                // Load and return the assembly
                return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
            }
            return null; // Return null if not found
        }

        // Main method executed when the script runs
        public void Main()
        {
            // Define session options for SFTP connection
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp, // Protocol to use: SFTP
                HostName = Dts.Variables["User::SFTP_Server"].Value.ToString(), // SFTP server address
                PortNumber = (int)Dts.Variables["User::SFTP_PortNumber"].Value, // SFTP port
                UserName = Dts.Variables["User::SFTP_User"].Value.ToString(), // SFTP username
                Password = Dts.Variables["User::SFTP_Password"].Value.ToString(), // SFTP password
                SshHostKeyFingerprint = Dts.Variables["User::SFTP_FingerPrint"].Value.ToString(), // SSH host key fingerprint for security
            };

            try
            {
                // Establish a connection with the SFTP server
                using (Session session = new Session())
                {
                    // Set the session log file path
                    session.SessionLogPath = Dts.Variables["User::SFTP_LogFileName"].Value.ToString();

                    // Open the session 
                    session.Open(sessionOptions);

                    // Retrieve the remote file path from an SSIS variable
                    string curFile = Dts.Variables["User::SFTP_FullFileImportPath"].Value.ToString();

                    // Check if the file exists on the remote server
                    if (session.FileExists(curFile))
                    {
                        // Configure transfer options for downloading the file
                        TransferOptions transferOptions = new TransferOptions
                        {
                            TransferMode = TransferMode.Binary, // Use binary mode for file transfer
                            ResumeSupport = { State = TransferResumeSupportState.Off } // Disable resume support
                        };

                        // Download the file to the local path, found in the SSIS variable
                        TransferOperationResult transferResult = session.GetFiles(
                            curFile,
                            Dts.Variables["User::vLocalFilePath"].Value.ToString(),
                            false,
                            transferOptions
                        );

                        // Check the transfer result for errors
                        transferResult.Check();

                        // Delete the file from the remote server after a successful download
                        session.RemoveFiles(curFile);

                        // Set the flag variable to 1, showing the file was found 
                        Dts.Variables["User::vFileExistsFlag"].Value = 1;

                        // Log success messages for the download
                        bool fireAgain = false;
                        foreach (TransferEventArgs transfer in transferResult.Transfers)
                        {
                            Dts.Events.FireInformation(0, null,
                                string.Format("Download and deletion of {0} succeeded", transfer.FileName),
                                null, 0, ref fireAgain);
                        }
                    }
                    else
                    {
                        // File does not exist; set the flag variable to 0
                        Dts.Variables["User::vFileExistsFlag"].Value = 0;
                    }
                }

                // Indicate task success
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                // Log the error details
                Dts.Events.FireError(0, null,
                    string.Format("Error when using WinSCP to download and delete files: {0}", e),
                    null, 0);

                // Indicate task failure
                Dts.TaskResult = (int)DTSExecResult.Failure;
            }
        }

        // Enum to define the possible script results (success or failure)
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Takeaway

This guide provides a step-by-step approach to automating SFTP file transfers using SSIS and the WinSCP.net assembly, catering to those who need alternatives to APIs. It covers preparing necessary directories, installing WinSCP, setting up a local SFTP server for testing, and configuring an SSIS Script Task with variables for secure file transfers. The tutorial includes clear instructions for writing C# code to establish an SFTP session, download files, and log the process. With robust error handling and practical testing tips, this guide makes SFTP integration in SSIS accessible and straightforward.