Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS call web API with script task in VB.net

Posted on 2013-11-29
3
Medium Priority
?
3,146 Views
Last Modified: 2016-02-11
Hello:

I need to callthe following API, retrieve the XML results so they can be written to a SQL server table. The API is http://data.fcc.gov/api/block/2010/find?latitude=40.0&longitude=-85
0
Comment
Question by:Louis Capece
  • 2
3 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39686516
Hi,
which SQL Server version?
Which coding language (for newer SSIS versions you have the choice between VB.NET and C#)?

Thanks.
Rainer
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 39686925
Hi,

I built a sample for SQL 2008R2 (BIDS with VS2008).

CSV input file with multiple lines and different longitudes/latitudes, enrichment from the response for each line and output to a raw file.

This is the script component code:
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Xml
Imports System.Net


<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Private baseUrl As String = "http://data.fcc.gov/api/block/2010/find?"
    Private webClient As New WebClient()

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        '
        ' Add your code here for preprocessing or remove if not needed
        '
    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        '
        ' Add your code here for postprocessing or remove if not needed
        ' You can set read/write variables here, for example:
        ' Me.Variables.MyIntVar = 100
        '
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim xmlDoc As New Xml.XmlDocument()
        Dim rowUrl As String

        rowUrl = baseUrl & "latitude=" & Row.Latitude.ToString() & "&longitude=" & Row.Longitude.ToString()

        Try
            xmlDoc.LoadXml(webClient.DownloadString(rowUrl))

            Dim xnsm As New XmlNamespaceManager(xmlDoc.NameTable)
            xnsm.AddNamespace("dnsp", "http://data.fcc.gov/api")

            ' Just for debugging - store the whole XML response into a field
            Row.TempXML = xmlDoc.OuterXml

            Row.StateFIPS = xmlDoc.SelectSingleNode("/dnsp:Response/dnsp:State/@FIPS", xnsm).InnerText
            Row.StateName = xmlDoc.SelectSingleNode("/dnsp:Response/dnsp:State/@name", xnsm).InnerText
            Row.StateCode = xmlDoc.SelectSingleNode("dnsp:Response/dnsp:State/@code", xnsm).InnerText
            Row.CountyFIPS = xmlDoc.SelectSingleNode("dnsp:Response/dnsp:County/@FIPS", xnsm).InnerText
            Row.CountyName = xmlDoc.SelectSingleNode("dnsp:Response/dnsp:County/@name", xnsm).InnerText
            Row.BlockFIPS = xmlDoc.SelectSingleNode("dnsp:Response/dnsp:Block/@FIPS", xnsm).InnerText

        Catch ex As Exception

        End Try
        

    End Sub

End Class

Open in new window


Attached the package as well as the input (please rename the package from .dtsx.txt to .dtsx). The package assumes that the sample input file is stored at C:\Temp

To Dos:
- Remove the debug column
- Error handling (e.g. check the status attribute of the response node)

HTH
Rainer
EEGovAPI.dtsx.txt
Input.csv
0
 

Author Closing Comment

by:Louis Capece
ID: 39695176
Thanks for this! Hopefully, this will help alot of others. Great examples and code
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question