Download a file from the web using SSIS

There are some tasks, no matter how seemingly simple, I just would not want to have to do again.  Today’s post is about one of them…
At work we use a data cleansing tool that purchased from the USPS (Post Office) called NCOA (National Change of Address).  All of the big list brokers use the NCOA to update old addresses and keep their lists as current as possible.
One of the requirements for the NCOA is a daily delete file, that is used to make minor changes to the local NCOA database.  As a NCOA service bureau we are licensed and even required to capture the DAILYDEL.DAT file by downloading it from the USPS’s web site.
Since no one in their right mind is going to take on a task of downloading any file on a daily basis, its curious why the USPS doesn’t just issue a script to go get the file.  So I’ll share my code with anyone that needs it.
To be fair I figured this one out with much support of numerous posts from other people.  Since it’s been a while since I got this running I can not be specific as to who you are.
So here’s the objective of the task:   Go to the USPS web site and download a file every day.   The website URL and the file name do not change.  Pretty simple…
Unfortunately SSIS doesn’t have a native transform for HTTP.  As unbelievable as it is, the only way I could figure how to do it was with a EXECUTE SCRIPT task.
Here’s the code:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
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.
        Dim nativeObject As Object = Dts.Connections("HTTP Connection to USPS").AcquireConnection(Nothing)

        ' Create a new HTTP client connection
        Dim connection As New HttpClientConnection(nativeObject)

        ' Save the file from the connection manager to SBDQS01
        Dim filename As String = "\\dqs01\NCOALink\DAILYDEL.DAT"
        connection.DownloadFile(filename, True)

        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

If you need to capture the NCOA DAILYDEL.DAT file just grab this code, drop it into an EXECUTE SCRIPT, change the path where you want to store it, and voila, you’re done.
I just scheduled a SQL Server Agent job to go get the file every night just before midnight (everything starts at midnight, right?).  The download finishes in about 5 seconds.