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:

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. http://www..
   vSSOLocalFileName, which is the fully qualified reference for the downloaded file e.g. c:\


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
    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

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

            Variables vars = null;
            Dts.VariableDispenser.GetVariables(ref vars);
                // 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.