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

  • Article

I recently had a requirement to automate downloading a file from an website and then perform ETL on the data in the file.  Fortunately, this is possible via the script task in SSIS (note that this is using SQL Server 2008 Integration Services).  I found a couple of web references to do this in VB.NET but I prefer C# so modified the code and made some adjustments to suit my (debugging) needs.  I set two package variables, RemoteURI and LocalFileName, to store the URL (source) and filename (destination).

This works really well and I can change the variables at run-time using property expressions

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

           Dts.Log("entering download..", 999, null);

           try
{
myWebClient = new WebClient();

               RemoteURI = Dts.Variables["User::vPipeline"].Value.ToString();
LocalFileName = Dts.Variables["User::vLocalFileName"].Value.ToString();

               Console.WriteLine(RemoteURI);
Console.WriteLine(LocalFileName);

               MessageBox.Show(RemoteURI);
MessageBox.Show(LocalFileName);

               // 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);

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

       }