Redux: Using a C# script task in SSIS to download a file over http

A few people have asked for further information about the C# script task which I blogged about (quite a while ago).  I mistakenly forgot to add the full source code, sorry everyone.  Here is the link to the original blog post:  https://blogs.msdn.com/benjones/archive/2009/03/29/using-a-c-script-task-in-ssis-to-download-a-file-over-http.aspx

I have since imported the SSIS package into Visual Studio 2010 (BIDS) and the code compiles without error.  Some of the code below is truncated on the right (just a formatting issue I need to resolve) but the core of the code is there.  

As always, let me know if there are any problems. 

 /*
   Download a file over http using Script Task in SQL Server 2008 R2 Integration Services.   
   Two key variables, vSSOReportURL, which is constructed in a prior Script Task e.g. https://www..
   vSSOLocalFileName, which is the fully qualified reference for the downloaded file e.g. c:\myfile.zip

*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.Net.Security;

namespace ST_7e897e41dd5945f3b77366d32f0a97e0.csproj
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 
        public void Main()
        {
            WebClient myWebClient;
            string RemoteURI;
            string LocalFileName;
            bool FireAgain = true;

            Variables vars = null;
            Dts.VariableDispenser.LockForRead("User::vSSOReportURL");
            Dts.VariableDispenser.LockForRead("User::vSSOLocalFileName");
            Dts.VariableDispenser.LockForWrite("User::vSSOReportURLIndicator");
            Dts.VariableDispenser.GetVariables(ref vars);
             
            try
            {
                // Ignore certificate warnings
                ServicePointManager.ServerCertificateValidationCallback = 
                     new RemoteCertificateValidationCallback(delegate { return true; });

                // Initiate webclient download, use default credentials (current login)
                myWebClient = new WebClient();
                myWebClient.Credentials = CredentialCache.DefaultCredentials; 

                RemoteURI = vars["User::vSSOReportURL"].Value.ToString();
                LocalFileName = vars["User::vSSOLocalFileName"].Value.ToString();

              
                // Log provider notification 
                Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", 
                LocalFileName, RemoteURI), String.Empty, 0, ref FireAgain);

                // Download the file 
                myWebClient.DownloadFile(RemoteURI, LocalFileName);

                // Set report URL indicator, this is used to determine the http source of the 
                // download i.e. vSSOReportURL or vSSOReportURLRetry for the message which is 
                // written to the table
                vars["User::vSSOReportURLIndicator"].Value = 0;

                // Return success
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch (Exception ex)
            {
                // Catch and handle error 
                Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

        }


    }
}

The files are extracted using an Execute Process Task (with 7-Zip) as shown below:

And the arguments are set using the expression (below).  There are probably better ways of doing this but I found this worked well.

The .zip file is then archived using a File System task and the extracted file is renamed to .xlsx.