Google GData Source

Script File:

Overview

Data flow source, loading information from various Google services. The script has the following parameters:

Service – specifies Google service.
BaseUrl – specifies service base URL.
Username – specifies Google service user name.
Password – specifies Google service password.
ProxyUrl – specifies proxy server URL.
ProxyUser – specifies proxy user name.
ProxyPassword – specifies proxy password.
BatchSize – specifies number of rows to retrieve in a batch.
Query – specifies query parameters.

Requirements

Depends on CozyRoc Script Component Plus component for implementing parameters user interface.
Requires installation of CozyRoc SSIS+ 1.4 library.
Requires Google GData .NET Client library.

Deployment of Google GData .NET Client

After you download Google.GData.Client.dll you have to:

Deploy it in the Global Assembly Cache (GAC).
For SQL 2005: copy the library to C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
For SQL 2008: copy the library to C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Library references

System
System.Data
Microsoft.SqlServer.TxScript
Microsoft.SqlServer.PipelineHost
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SqlServer.DTSRuntimeWrap
Microsoft.SqlServer.ManagedDTS
CozyRoc.SSISPlus
System.XML
Google.GData.Client

ScriptMain

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2010 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. ' The following resources were used as a reference: ' http://code.google.com/apis/gdata/docs/2.0/reference.html Imports System Imports System.ComponentModel Imports System.Xml Imports System.IO Imports System.Collections Imports System.Net Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes Imports Google.GData.Client Imports Google.GData.Extensions ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() { _ "Service", "BaseUrl", "Username", "Password", _ "ProxyUrl", "ProxyUser", "ProxyPassword", _ "BatchSize", "Query"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub New() ' Setup Google services list. m_serviceList = New Hashtable() With m_serviceList Call .Add("analytics", "https://www.google.com/analytics/feeds/data") Call .Add("apps", "https://apps-apis.google.com/a/feeds/") Call .Add("blogger", "") Call .Add("cl", "") Call .Add("codesearch", "") Call .Add("cp", "http://www.google.com/m8/feeds/contacts/") Call .Add("gbase", "http://www.google.com/base/") Call .Add("health", "https://www.google.com/health/feeds/") Call .Add("lh2", "http://picasaweb.google.com/data/feed/api/user/") Call .Add("weaver", "https://www.google.com/h9/feeds/") Call .Add("wise", "http://spreadsheets.google.com/feeds/") Call .Add("writely", "http://docs.google.com/feeds/default/private/full") Call .Add("youtube", "http://gdata.youtube.com/feeds/api/") End With End Sub ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() ' Setup service. Dim service As Service = New Service(Me.Service, "CozyRoc Google Data Source") Call service.setUserCredentials(Me.Username, Me.Password) If Not String.IsNullOrEmpty(Me.ProxyUrl) Then ' Setup proxy. Dim proxy As WebProxy = New WebProxy(Me.BaseUrl) If String.IsNullOrEmpty(Me.ProxyUser) Then proxy.UseDefaultCredentials = False proxy.Credentials = New NetworkCredential(Me.ProxyUser, Me.Password) End If Dim rf As GDataRequestFactory = CType(service.RequestFactory, GDataRequestFactory) rf.Proxy = proxy End If ' Setup query object. Dim query As FeedQuery = New FeedQuery(Me.BaseUrl) query.NumberToRetrieve = Me.BatchSize query.ExtraParameters = Me.Query Dim feed As AtomFeed = service.Query(query) While True Dim itemsCount As Integer = feed.Entries.Count For itemIndex As Integer = 0 To itemsCount - 1 Dim entry As AtomEntry = feed.Entries(itemIndex) With Me.OutputBuffer Call .AddRow() .id = entry.Id.Uri.Content .title = entry.Title.Text .content = entry.Content.Content .updated = entry.Updated ' Setup links. .link = GetLinks_(entry) ' Setup authors. .author = GetAuthors_(entry) ' Setup extensions. .extensions = GetExtensions_(entry) End With Next If String.IsNullOrEmpty(feed.NextChunk) Then ' No more data to retrieve. Exit While End If ' Setup next query iteration. query.StartIndex += itemsCount feed = service.Query(query) End While End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.Service) Then Throw New ApplicationException("Select Google service.") End If If String.IsNullOrEmpty(Me.BaseUrl) Then Throw New ApplicationException("Specify base URL.") End If If String.IsNullOrEmpty(Me.Username) Then Throw New ApplicationException("Specify service user name.") End If If String.IsNullOrEmpty(Me.Password) Then Throw New ApplicationException("Specify service password.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function GetServices() As String() Dim result As String() = New String(m_serviceList.Count - 1) {} Call m_serviceList.Keys.CopyTo(result, 0) GetServices = result End Function ' GetServices #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Service")> _ <Description("Select Google service.")> _ <List("GetServices")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property Service() As String Get Service = m_service End Get Set(ByVal value As String) If m_service <> value Then m_service = value ' Setup base URL from service. Me.BaseUrl = CStr(m_serviceList(m_service)) End If End Set End Property ' Service ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Service")> _ <Description("Specify base URL.")> _ Public Property BaseUrl() As String Get BaseUrl = m_baseUrl End Get Set(ByVal value As String) m_baseUrl = value End Set End Property ' BaseUrl ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Service")> _ <Description("Specify service user name.")> _ Public Property Username() As String Get Username = m_username End Get Set(ByVal value As String) m_username = value End Set End Property ' Username ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Service")> _ <Description("Specify service password.")> _ <PasswordPropertyText(True)> _ Public Property Password() As String Get Password = m_password End Get Set(ByVal value As String) m_password = value End Set End Property ' Password ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Proxy")> _ <Description("Specify proxy server URL (optional).")> _ Public Property ProxyUrl() As String Get ProxyUrl = m_proxyUrl End Get Set(ByVal value As String) m_proxyUrl = value End Set End Property ' ProxyUrl <Category("Proxy")> _ <Description("Specify proxy user name (optional).")> _ Public Property ProxyUser() As String Get ProxyUser = m_proxyUser End Get Set(ByVal value As String) m_proxyUser = value End Set End Property ' ProxyUser ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Proxy")> _ <Description("Specify proxy password (optional).")> _ <PasswordPropertyText(True)> _ Public Property ProxyPassword() As String Get ProxyPassword = m_proxyPassword End Get Set(ByVal value As String) m_proxyPassword = value End Set End Property ' ProxyPassword ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Query")> _ <Description("Specify batch number of rows to get.")> _ <DefaultValue(100)> _ Public Property BatchSize() As Integer Get BatchSize = m_batchSize End Get Set(ByVal value As Integer) m_batchSize = value End Set End Property ' BatchSize ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Query")> _ <Description("Specify query string with parameters.")> _ Public Property Query() As String Get Query = m_query End Get Set(ByVal value As String) m_query = value End Set End Property ' Query #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetLinks_(ByVal entry As AtomEntry) As String Dim result As String For Each link As AtomLink In entry.Links If Not String.IsNullOrEmpty(result) Then result += vbCrLf End If result += link.HRef.Content Next GetLinks_ = result End Function ' GetLinks_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetAuthors_(ByVal entry As AtomEntry) As String Dim result As String For Each person As AtomPerson In entry.Authors If Not String.IsNullOrEmpty(result) Then result += vbCrLf End If result += person.Email Next GetAuthors_ = result End Function ' GetAuthors_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetExtensions_(ByVal entry As AtomEntry) As String Dim result As String For Each extension As XmlExtension In entry.ExtensionElements result += extension.Node.OuterXml Next If Not String.IsNullOrEmpty(result) Then ' Wrap result in start/end tag to create valid XML fragment. result = "<extension>" + result + "</extension>" End If GetExtensions_ = result End Function ' GetExtensions_ #End Region ' Internals #Region "Attributes" Private m_service As String Private m_baseUrl As String Private m_username As String Private m_password As String Private m_proxyUrl As String Private m_proxyUser As String Private m_proxyPassword As String Private m_batchSize As Integer Private m_query As String Private m_serviceList As Hashtable #End Region ' Attributes End Class ' ScriptMain