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
.jpg)
4 comments:
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?
Did you ever get a resolution to your question?
I'm having the same issue
Jim
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?
hi
if the http link is having password protected then
how to connect to the password protected link and down load the file
Post a Comment