Tuesday, November 28, 2017

SSIS Script Task, using C#, to Download Files w/ variable Security Protocols

I have a SSIS package that I use to download files from various websites. I began by using a C# script published by Tom Archer - MSFT on https://www.codeguru.com/columns/dotnettips/article.php/c7005/Downloading-Files-with-the-WebRequest-and-WebResponse-Classes.htm. I had to modify this script because certain security protocols were required for some sites but not others. I've included my modifications below so that Security Protocols can be controlled based on variables I pass to the Script Task. My resulting code is as follows:


public static int DownloadFile(String remoteFilename,
                                String localFilename,
                                String securityProtocolType)
{
    //Make sure to use the following namespaces:
    //using System.Net;
    //using System.IO;
    
    // Function will return the number of bytes processed
    // to the caller. Initialize to 0 here.
    int bytesProcessed = 0;

    // Assign values to these objects here so that they can
    // be referenced in the finally block
    Stream remoteStream = null;
    Stream localStream = null;
    WebResponse response = null;

        if (securityProtocolType == "Tls12") //Added if statement since some downloads will fail with Tls12 selected
        { ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072; } // 3072 = TLS 1.2 Security Protocol

    // Use a try/catch/finally block as both the WebRequest and Stream
    // classes throw exceptions upon error
    try
    {
        // Create a request for the specified remote file name
        WebRequest request = WebRequest.Create(remoteFilename);
        if (request != null)
        {
            // Send the request to the server and retrieve the
            // WebResponse object 
            response = request.GetResponse();
            if (response != null)
            {
                // Once the WebResponse object has been retrieved,
                // get the stream object associated with the response's data
                remoteStream = response.GetResponseStream();

                // Create the local file
                localStream = File.Create(localFilename);

                // Allocate a 1k buffer
                byte[] buffer = new byte[1024];
                int bytesRead;

                // Simple do/while loop to read from stream until
                // no bytes are returned
                do
                {
                    // Read data (up to 1k) from the stream
                    bytesRead = remoteStream.Read(buffer, 0, buffer.Length);

                    // Write the data to the local file
                    localStream.Write(buffer, 0, bytesRead);

                    // Increment total bytes processed
                    bytesProcessed += bytesRead;
                } while (bytesRead > 0);
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Close the response and streams objects here 
        // to make sure they're closed even if an exception
        // is thrown at some point
        if (response != null) response.Close();
        if (remoteStream != null) remoteStream.Close();
        if (localStream != null) localStream.Close();
    }

    // Return total bytes processed to caller.
    return bytesProcessed;
}

/// <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()
{
    int read110 = DownloadFile(Dts.Variables["User::rSourceFullName"].Value.ToString(), Dts.Variables["User::rDestinationFullName"].Value.ToString(), Dts.Variables["User::rSecurityProtocolType"].Value.ToString());

    //if no bytes are processed return error
    if (read110 > 0) { Dts.TaskResult = (int)ScriptResults.Success; }
    else { Dts.TaskResult = (int)ScriptResults.Failure; }
   
}