Download SFTP file using SSIS package

Question

I want to create a SSIS package which need to download a file automatically and place it in our local.

Note: using process executive task and batch script files only.

Answer 1

In a new SSIS project, create a new package. Navigate to the Parameters tab, where we’ll create a handful of runtime values that will make the DownloadSFTP package more reusable.

• pFilename: This is the file name to download from the server. Note that we can also use wildcards (assuming they are supported by the target server) – in the example above, we’ll be downloading all files ending in “.TXT”.

• pServerHostKey: This is used to satisfy a security mechanism built into the WinSCP process. By default, WinSCP will prompt the user to verify and add to local cache the host key when connecting to an SFTP server for the first time. Because this will be done in an automated, non-interactive process, getting that prompt would cause an error in our script. To prevent this, the script is built to supply the server host key to avoid the error, and also has the added benefit of ensuring we’re actually connecting to the correct SFTP server. This brief article on the WinSCP documentation site describes how to retrieve the server host key for the target server.

• pServerUserPassword: This is marked as sensitive to mask the password. As part of the script logic, this password will be decrypted before it is sent to the server.

Create a new script task in the control flow, and add all 7 of the parameters shown above to the list of ReadOnlyVariables.

Using the Main() function (which is created automatically in a new script task), create the Process object and configure a few of the runtime options, including the name of the executable and the download directory.

public void Main()
        {
            // Create a new Process object to execute WinSCP
            Process winscp = new Process();
            // Set the executable path and download directory
            winscp.StartInfo.FileName = Dts.Variables["$Package::pWinSCPLocation"].Value.ToString();
            winscp.StartInfo.WorkingDirectory = Dts.Variables["$Package::pDownloadDir"].Value.ToString();
            
            // Set static execution options (these should not need to change)
            winscp.StartInfo.UseShellExecute = false;
            winscp.StartInfo.RedirectStandardInput = true;
            winscp.StartInfo.RedirectStandardOutput = true;
            winscp.StartInfo.CreateNoWindow = true;
            // Set session options
            string sessionOptionString = "option batch abort" + System.Environment.NewLine + "option confirm off";

The next step is to create the input strings that will make the connection and download the file. At the bottom of this snippet, there are 3 variables that will capture output messages, error messages, and the return value, all of which will be used to log runtime information.

// Build the connect string (<user>:<password>@<hostname>)
            string connectString = @"open " + Dts.Variables["$Package::pServerUserName"].Value.ToString()
                                            + ":"
                                            + Dts.Variables["$Package::pServerUserPassword"].GetSensitiveValue().ToString()
                                            + "@"
                                            + Dts.Variables["$Package::pServerName"].Value.ToString();
            // Supplying the host key adds an extra level of security, and avoids getting the prompt to trust the server.
            string hostKeyString = Dts.Variables["$Package::pServerHostKey"].Value.ToString();
            // If hostkey was specified, include it
            if (hostKeyString != null && hostKeyString.Length > 0)
                connectString += " -hostkey=\"" + hostKeyString + "\"";
            // Build the get command string
            string getString = "get " + Dts.Variables["$Package::pFilename"].Value.ToString();
            // Create output variables to capture execution info
            string outStr = "", errStr = "";
            int returnVal = 1;

With all of the options configured, it’s time to invoke WinSCP.com. The try/catch block below will attempt to connect and download the specified file from the server.

// This try/catch block will capture catastrophic failures (such as specifying the wrong path to winscp).
        try
        {
            winscp.Start();
            winscp.StandardInput.WriteLine(sessionOptionString);
            winscp.StandardInput.WriteLine(connectString);
            winscp.StandardInput.WriteLine(getString);
            winscp.StandardInput.Close();
            winscp.WaitForExit();
    // Set the outStr to the output value, obfuscating the password
            outStr = winscp.StandardOutput.ReadToEnd().Replace(":" + Dts.Variables["$Package::pServerUserPassword"].GetSensitiveValue().ToString() + "@", ":*******@");
            returnVal = winscp.ExitCode;
        }
        catch (Exception ex)
        {
            errStr = "An error occurred when attempting to execute winscp.com: " + ex.Message.Replace("'", "\"").Replace("--", " - ");
        }

The package is ready to be executed. Assuming everything is configured properly, running the package on the system should download exactly two text files (remember, we used the wildcard “*.txt” to get all text files).