SSIS – download a file from a website

Some of the companies I’ve worked for have had reporting contracts with smaller 3rd party providers. Campaign or website analysis provided by them would have to be loaded to the datawarehouse

Security policy dissallowed downloading the files over FTP, because credentials are passed in clear text, and our 3rd party report providers didn’t have a sFTP facility, so in this scenario what other options are available?

Microsoft Office 2007 SharePoint Designer Edition (PC)

One option was to have the report emailed to us each day. This presented it’s own set of problems as the report data still had to be transformed for use within the datawarehouse and for this to happen the file would have to be stored on the network. Saving an emailed file each day would have meant either human intervention (a definate no) or development of a mechanism to save it automatically – a project that there isn’t always the budget or resource for in a fairly specialised BI team.

As the title of this post suggests, another option (and the one chosen) is to download the report from the suppliers website. In our case it was an SSL encrypted connection however even a non-encrypted connection can offer superior security as at least the passwords are hashed if authentication is configured.

Although, we do have an HTTP connection manager in SSIS I don’t see a real advantage to using it other than with the web service component. For more flexibility, you can use a script task, substituting the constants I’ve used with variables.

Here’s what the code looks like…

Imports System.Net

Public Class ScriptMain

Public Sub Main()
Dim WebConnection As New WebClient()
Dim proxyConnection As New WebProxy("http://proxy.server.com")
Dim creds As New NetworkCredential("username", "Password")
Try
With WebConnection
.Proxy = proxyConnection
.BaseAddress = "https://www.mySecureDomain.com/myFolder/"
.Credentials = creds
End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem connecting to website: ", ex.Message, "", 0)
End Try

Try
With WebConnection
.DownloadFile("https://www.mySecureDomain.com/myFolder/downloads/", "myWebReport.csv")
End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem downloading file: ", ex.Message, "", 0)
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

It could potentially be used to download files in sharepoint aswell.

Cheers
Frank

2 thoughts on “SSIS – download a file from a website”

  1. Hi Frank,
    I think the credential should be set to WebProxy instead of WebConnection and you could use the .UseDefaultCredential = True to avoid hard code username and password in source code (or in configuration file)

    1. Hi James,

      Your point about hard coding is valid. It’s always best to derive or configure any types of parameters.

      If I’m understanding you correctly you are talking about authenticaion against a proxy. When I wrote this code, I was using these credentials to authenticate with a 3rd party website rather than the proxy server (which used Windows authentication anyways).

      e.g. If I’ve protected a folder on my website with an apache .htaccess file, then I can use these credentials to authenticate.

      I think that for a corporate intranet/sharepoint site, your idea of using default credentials would be perfect though.

      Cheers
      Frank

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.