Retrieve Web Page Results Using SSIS

Scenario

A client is wanting to use the web to retrieve coordinate information from Google Maps to store the information for a Marketing campaign.

Solution

The SSIS Script task can connect to the web page and return the results. The following script provides sample code on how to get the Script Task to perform the action. The script task was developed in SQL SSIS 2008 using VB, the 2005 version is similar but the guts of the HTTP code remains the same (thanks to my colleague Andrew Crowder for putting together the main syntax):

First, a package is created with two variables of type String. The variables are passed into the script task in the following manner:

• ReadOnly: User::URL
• ReadWrite: User::HTML

Now, the script itself:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Net

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Try

            'setup the variables, URL is set in the package as a var and read
            Dim URL As String = Dts.Variables("URL").Value
            Dim request As HttpWebRequest = WebRequest.Create(URL)
            Dim response As HttpWebResponse = request.GetResponse()
            Dim reader As StreamReader = New StreamReader(response.GetResponseStream())

            'read the http data into a string to be used later in the package
            Dim str As String = reader.ReadToEnd()

            Dts.Variables("HTML").Value = str.ToString()

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            'fire the info event and return as error from the task
            Dts.Events.FireInformation(0, "Web Script Task", ex.Message.ToString(), "", 0, 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub

Execution

The following URL value is set and returns the coordinate information into the HTML variable:

URL: http://maps.google.com/maps/geo?q=1600+Amphitheatre+Parkway,+Mountain+View,+CA&output=csv&oe=utf8&sensor=false&key=ABQIAAAAP6yK9BJ44wmvT1Dd7V5cKRQz9eeYvRAhuPGO19pGZUf7Pj2DYxRZ0Zbtz0o1UAkk17kmD6xk5qDnzg

HTML: 200,8,37.4219720,-122.0841430

The script task can also return the HTML from a page, for example http://www.rdacorp.com, and allow for other SSIS tasks to manipulate the HTML variable as per the requirements and design of the package as it would like any other variable within the package.