]]> _ _ _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Dim colsIdx As Hashtable = GetColumnsIdx_() ' Setup query template. Dim queryStr As String = Me.Query If Me.IsQueryVariable Then ' Query is contained in variable. queryStr = CStr(GetVariable_(Me.QueryVariable)) End If Dim queryTemplate_1P As String = Me.Collection & "?" If Not String.IsNullOrEmpty(queryStr) Then queryTemplate_1P &= queryStr & "&" End If queryTemplate_1P &= String.Format("$top={0}&$skip={1}", Me.BatchSize, "{0}") Dim doc As XmlDocument = New XmlDocument() Dim conn As Object = Me.Connections.Http.AcquireConnection(Nothing) Dim connection As HttpClientConnection = New HttpClientConnection(conn) Dim page As Integer = 0 Dim ns As XmlNamespaceManager Try While True ' Download data. connection.ServerURL = String.Format(queryTemplate_1P, page * Me.BatchSize) Using odataStream As Stream = New MemoryStream(connection.DownloadData()) Call doc.Load(odataStream) End Using If ns Is Nothing Then ' Initialize namespace manager. ns = New XmlNamespaceManager(doc.NameTable) Call ns.AddNamespace("atom", "http://www.w3.org/2005/Atom") End If Dim entryList As XmlNodeList = doc.SelectNodes("/atom:feed/atom:entry", ns) If entryList.Count = 0 Then ' Reached end of feed. Call OutputBuffer.SetEndOfRowset() Exit While End If ' Push items. For Each entry As XmlElement In entryList With OutputBuffer Call .AddRow() ' Setup atom data. .Buffer(CInt(colsIdx("atom_id"))) = GetText_(entry("id")) .Buffer(CInt(colsIdx("atom_title"))) = GetText_(entry("title")) .Buffer(CInt(colsIdx("atom_updated"))) = DateTime.Parse(GetText_(entry("updated"))) .Buffer(CInt(colsIdx("atom_author"))) = GetText_(entry("author")) Dim linkElem As XmlElement = entry("link") If Not linkElem Is Nothing Then Dim link As String = linkElem.GetAttribute("href") If Not link.StartsWith("http://") Then ' Link is relative. link = doc.DocumentElement.GetAttribute("xml:base") & link End If .Buffer(CInt(colsIdx("atom_link"))) = link End If Dim categoryElem As XmlElement = entry("category") If Not categoryElem Is Nothing Then .Buffer(CInt(colsIdx("atom_category"))) = categoryElem.GetAttribute("term") End If ' Setup collection properties. Dim contentElem As XmlElement = entry("content") If Not contentElem Is Nothing Then Dim propsList As XmlNodeList = contentElem("m:properties").ChildNodes For Each propElem As XmlElement In propsList If colsIdx.Contains(propElem.LocalName) Then Dim value As Object Select Case propElem.GetAttribute("m:type") Case "Edm.Binary" value = Convert.FromBase64String(propElem.InnerText) Case Else value = propElem.InnerText End Select .Buffer(CInt(colsIdx(propElem.LocalName))) = value End If Next End If End With Next page += 1 End While Catch ex As Exception Call FireError_(ex.Message) Finally Call Me.Connections.Http.ReleaseConnection(conn) End Try End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList Call result.Add("Connection") Call result.Add("BatchSize") Call result.Add("Collection") Call result.Add("IsQueryVariable") Call result.Add(IIf(Me.IsQueryVariable, "QueryVariable", "Query")) Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean = True Try If String.IsNullOrEmpty(Me.Connection) Then Throw New Exception("Select connection.") End If If String.IsNullOrEmpty(Me.Collection) Then Throw New Exception("Specify collection.") End If Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0) If output.OutputColumnCollection.Count = 0 Then ' Setup collection output columns. Call SetupOutputColumns_() End If Me.ComponentMetaData.RuntimeConnectionCollection("Http").ConnectionManagerID = Me.Connection Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function ' Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of tables in the currently selected Oracle connection. Public Function GetCollections() As String() Dim result As ArrayList = New ArrayList() ' Download collections. Dim doc As XmlDocument = New XmlDocument() Dim conn As Object = Me.DesignConnections(Me.Connection).AcquireConnection(Nothing) Dim connection As HttpClientConnection = New HttpClientConnection(conn) Try Using odataStream As Stream = New MemoryStream(connection.DownloadData()) Call doc.Load(odataStream) End Using Finally Call Me.DesignConnections(Me.Connection).ReleaseConnection(conn) End Try ' Setup collections list. Dim ns As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable) Call ns.AddNamespace("app", "http://www.w3.org/2007/app") Dim collections As XmlNodeList = doc.SelectNodes("/app:service/app:workspace/app:collection", ns) For Each collection As XmlElement In collections Dim href As String = collection.GetAttribute("href") If Not href.StartsWith("http://") Then ' The collection reference is relative. href = Connection.ServerURL & "/" & href End If Call result.Add(href) Next Return CType(result.ToArray(GetType(String)), String()) End Function ' GetCollections #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property Connection() As String Get Connection = m_connection End Get Set(ByVal value As String) If m_connection <> value Then m_connection = value Me.Collection = String.Empty End If End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property BatchSize() As Integer Get BatchSize = m_batchSize End Get Set(ByVal value As Integer) m_batchSize = value End Set End Property ' BatchSize ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Collection() As String Get Collection = m_collection End Get Set(ByVal value As String) If m_collection <> value Then If Not String.IsNullOrEmpty(m_collection) Then ' Force output columns to be reloaded. If Me.ComponentMetaData.OutputCollection.Count > 0 Then Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) Call output.OutputColumnCollection.RemoveAll() End If End If m_collection = value End If End Set End Property ' Collection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property IsQueryVariable() As Boolean Get IsQueryVariable = m_isQueryVariable End Get Set(ByVal value As Boolean) If m_isQueryVariable <> value Then m_isQueryVariable = value Me.Query = String.Empty End If End Set End Property ' IsQueryVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property Query() As String Get Query = m_query End Get Set(ByVal value As String) m_query = value End Set End Property ' Table ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property QueryVariable() As String Get QueryVariable = Me.Query End Get Set(ByVal value As String) Me.Query = value End Set End Property ' QueryVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property HttpConnectionType() As String() Get HttpConnectionType = New String() {"HTTP"} End Get End Property ' HttpConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub SetupOutputColumns_() ' Load collection properties. Dim metaService As String = Me.Collection.Substring(0, Me.Collection.LastIndexOf("/") + 1) + "$metadata" Dim collectionName As String = Me.Collection.Substring(Me.Collection.LastIndexOf("/") + 1) Dim doc As XmlDocument = New XmlDocument() Dim conn As Object = Me.DesignConnections(Me.Connection).AcquireConnection(Nothing) Dim connection As HttpClientConnection = New HttpClientConnection(conn) Dim serverURL As String = connection.ServerURL Try connection.ServerURL = metaService Using odataStream As Stream = New MemoryStream(connection.DownloadData()) Call doc.Load(odataStream) End Using Finally connection.ServerURL = serverURL Call Me.DesignConnections(Me.Connection).ReleaseConnection(conn) End Try Dim ns As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable) Call ns.AddNamespace("edm", "http://schemas.microsoft.com/ado/2007/05/edm") Call ns.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2007/06/edmx") Dim props As XmlNodeList = doc.SelectNodes( _ String.Format("/edmx:Edmx/edmx:DataServices/edm:Schema/edm:EntityType['{0}']/edm:Property", collectionName), _ ns) Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0) Call output.OutputColumnCollection.RemoveAll() Dim column As IDTSOutputColumn90 ' Setup base (Atom) columns. column = output.OutputColumnCollection.New() column.Name = "atom_id" Call column.SetDataTypeProperties(DataType.DT_WSTR, 1000, 0, 0, 0) column = output.OutputColumnCollection.New() column.Name = "atom_title" Call column.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0) column = output.OutputColumnCollection.New() column.Name = "atom_updated" Call column.SetDataTypeProperties(DataType.DT_DBTIMESTAMP, 0, 0, 0, 0) column = output.OutputColumnCollection.New() column.Name = "atom_author" Call column.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0) column = output.OutputColumnCollection.New() column.Name = "atom_link" Call column.SetDataTypeProperties(DataType.DT_WSTR, 1000, 0, 0, 0) column = output.OutputColumnCollection.New() column.Name = "atom_category" Call column.SetDataTypeProperties(DataType.DT_WSTR, 1000, 0, 0, 0) For Each prop As XmlElement In props ' Get column info. Dim dtsType As Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType Dim length As Integer Dim precision As Integer Dim scale As Integer Dim codePage As Integer GetTypeFromPropertyType_( _ prop.GetAttribute("Type"), _ dtsType, _ length, _ precision, _ scale, _ codePage) ' Setup output column. column = output.OutputColumnCollection.New() column.Name = prop.GetAttribute("Name") Call column.SetDataTypeProperties(dtsType, length, precision, scale, codePage) Next End Sub ' SetupOutputColumns_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get SSIS type from specified DataRow. Private Sub GetTypeFromPropertyType_( _ ByVal propertyType As String, _ ByRef dtsType As Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType, _ ByRef length As Integer, _ ByRef precision As Integer, _ ByRef scale As Integer, _ ByRef codePage As Integer) length = 0 precision = 0 scale = 0 codePage = 0 Select Case propertyType Case "Edm.Binary" dtsType = DataType.DT_IMAGE Case "Edm.Boolean" dtsType = DataType.DT_BOOL Case "Edm.Byte" dtsType = DataType.DT_UI1 Case "Edm.DateTime", "Edm.DateTimeOffset" dtsType = DataType.DT_DBTIMESTAMP Case "Edm.Time" dtsType = DataType.DT_DBTIME Case "Edm.Decimal" dtsType = DataType.DT_DECIMAL Case "Edm.Double" dtsType = DataType.DT_R8 Case "Edm.Single" dtsType = DataType.DT_R4 Case "Edm.Guid" dtsType = DataType.DT_GUID Case "Edm.Int16" dtsType = DataType.DT_I2 Case "Edm.Int32" dtsType = DataType.DT_I4 Case "Edm.Int64" dtsType = DataType.DT_I8 Case "Edm.String" dtsType = DataType.DT_WSTR length = 250 Case Else Throw New Exception("Unhandled property type.") End Select End Sub ' GetTypeFromPropertyType_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean = False Call MyBase.ComponentMetaData.FireError( _ 0, _ "OData Source", _ message, _ String.Empty, _ 0, _ cancel) End Sub ' FireError_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetVariable_(ByVal varName As String) As Object Dim result As Object Dim vars As IDTSVariables90 Call MyBase.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Setup column name -> buffer index map. Private Function GetColumnsIdx_() As Hashtable Dim result As Hashtable = New Hashtable Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) Dim colsCount As Integer = output.OutputColumnCollection.Count For colIndex As Integer = 0 To colsCount - 1 Dim column As IDTSOutputColumn90 = output.OutputColumnCollection(colIndex) ' Setup buffer index. Call result.Add( _ column.Name, _ Me.HostComponent.BufferManager.FindColumnByLineageID( _ output.Buffer, _ column.LineageID)) Next GetColumnsIdx_ = result End Function ' GetColumnsIdx_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetText_(ByVal element As XmlElement) As String Dim result As String If Not element Is Nothing Then result = element.InnerText If result.Length > 4000 Then ' Maximum 4000 characters are supported in data flow. result = result.Substring(0, 4000) End If End If GetText_ = result End Function ' GetText_ #End Region ' Internals #Region "Attributes" Private m_connection As String Private m_collection As String Private m_batchSize As Integer Private m_isQueryVariable As Boolean Private m_query As String #End Region ' Attributes End Class ' ScriptMain ]]> ScriptComponent_0c4f2d277da94cd3bcb7ec6aa65b1a54 true CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea