by Darren Green
2 Jul 2009 18:14
This post shows you how to download files from a web site whilst really making the most of the SSIS objects that are available. There is no task to do this, so we have to use the Script Task and some simple VB.NET or C# (if you have SQL Server 2008) code. Very often I see suggestions about how to use the .NET class System.Net.WebClient and of course this works, you can code pretty much anything you like in .NET. Here I’d just like to raise the profile of an alternative.
This approach uses the HTTP Connection Manager, one of the stock connection managers, so you can use configurations and property expressions in the same way you would for all other connections. Settings like the security details that you would want to make configurable already are, but if you take the .NET route you have to write quite a lot of code to manage those values via package variables. Using the connection manager we get all of that flexibility for free. The screenshot below illustrate some of the options we have.
Using the HttpClientConnection class makes for much simpler code as well. I have demonstrated two methods, DownloadFile which just downloads a file to disk, and DownloadData which downloads the file and retains it in memory. In each case we show a message box to note the completion of the download. You can download a sample package below, but first the code:
Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)
' Download the file #1
' Save the file from the connection manager to the local path specified
Dim filename As String = "C:\Temp\Sample.txt"
connection.DownloadFile(filename, True)
' Confirm file is there
If File.Exists(filename) Then
MessageBox.Show(String.Format("File {0} has been downloaded.", filename))
End If
' Download the file #2
' Read the text file straight into memory
Dim buffer As Byte() = connection.DownloadData()
Dim data As String = Encoding.ASCII.GetString(buffer)
' Display the file contents
MessageBox.Show(data)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Sample Package HTTPDownload.dtsx (74KB)