Sunday, March 29, 2009

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

 

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

       }

Posted: Sunday, March 29, 2009 9:07 PM by benjones

Benjamin Wright-Jones : Using a C# script task in SSIS to download a file over http

4 comments:

Michael said...

This is exactly the example I've been looking for. Thank you!

However I'm not able to get it to work. I'm rather new at scripting in SSIS. I'm using SQL Server 2008 Standard.

I keep getting:
The name 'Dts' does not exist in the current context.

I have the following for my "using":

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

Any thoughts?

db-Consultants said...

Did you ever get a resolution to your question?

I'm having the same issue

Jim

Todd McDermid said...

Post this code and your problem at the MSDN SSIS Forums: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads

Post back here with a link to your question on the forums so others can follow...

I'd help you here, but I won't remember to come back after I ask you this:

When you're editing your script, where does it show you the blue squiggly underline that indicates it doesn't know what "Dts" is?

bramhaprakash a said...

hi
if the http link is having password protected then
how to connect to the password protected link and down load the file

Blog Archive