Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

March, 2009

Posts
  • Benjamin Wright-Jones

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

    • 3 Comments

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

           }

  • Benjamin Wright-Jones

    Microspotting?

    • 1 Comments

    [Non-SQL related] I’ve just discovered an interesting site called Microspotting which came to my attention courtesy of the grapevine. At first I wasn’t sure what this was about but after a little bit of digging, it would appear that the site is dedicated to sharing stories about Microsoft FTE’s. As the site proclaims, it is a bit like having an internal paparazzi but provides some good insight.  Take a look here http://www.microspotting.com/

  • Benjamin Wright-Jones

    SQL Server 2008 CU4 released

    • 1 Comments

    Just published here http://support.microsoft.com/kb/963036

Page 1 of 1 (3 items)