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


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

       }


Comments (3)

  1. Anonymous says:

    hi friend…can you plz tell me what are the namespaces did you use in your code.actually in my requirment i will be using a sharepoint site to download file.

  2. superlatch says:

    hi – I developed this in VS 2005 so I'll need to update the scripts. I'll re-post the code here.  Apologies to all for not posting the namespaces!