_ _ _ _ _ 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 IDTSOutput100 = 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 IDTSOutput100 = 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 IDTSOutput100 = MyBase.ComponentMetaData.OutputCollection(0) Call output.OutputColumnCollection.RemoveAll() Dim column As IDTSOutputColumn100 ' 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 IDTSVariables100 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 IDTSOutput100 = Me.ComponentMetaData.OutputCollection(0) Dim colsCount As Integer = output.OutputColumnCollection.Count For colIndex As Integer = 0 To colsCount - 1 Dim column As IDTSOutputColumn100 = 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 ]]> _ Public Class OutputBuffer Inherits ScriptBufferPlus Public Sub New(ByVal Component As ScriptComponent, ByVal ObjectID As Integer, ByVal IsInput As Boolean, ByVal Buffer As PipelineBuffer, ByVal OutputMap As OutputNameMap) MyBase.New(Component, ObjectID, IsInput, Buffer, OutputMap) End Sub Public Overrides ReadOnly Property StaticInputColumns() As String() Get Return New String() {} End Get End Property Public Overrides ReadOnly Property StaticOutputColumns() As String() Get Return New String() {} End Get End Property Public Overloads Sub AddRow() MyBase.AddRow() End Sub Public Overloads Sub SetEndOfRowset() MyBase.SetEndOfRowset() End Sub Public Overloads Function EndOfRowset() As Boolean EndOfRowset = MyBase.EndOfRowset End Function End Class ]]> {30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F} Debug AnyCPU 8.0.30703 2.0 {21E9DF50-84D7-4664-9D1E-ECC920899290} Library My Project ScriptComponent_0c4f2d277da94cd3bcb7ec6aa65b1a54 ScriptComponent_0c4f2d277da94cd3bcb7ec6aa65b1a54 v4.0 512 Binary On On true full false .\bin\Debug\ false true true prompt 4 false true .\bin\Release\ false false true prompt 4 False C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\CozyRoc.SSISPlus.2014.dll False False False False SSIS_SC120 ]]> _ Public Class UserComponent Inherits ScriptComponentPlus Public Connections As New Connections(Me) Public Variables As New Variables(Me) Public OutputBuffer As OutputBuffer Public Overridable Sub FinishOutputs() End Sub Private Sub MarkOutputsFinished() If OutputBuffer IsNot Nothing Then OutputBuffer.SetEndOfRowset OutputBuffer = Nothing End If End Sub Public Overrides Sub PrimeOutput(ByVal Outputs As Integer, ByVal OutputIDs() As Integer, ByVal Buffers() As PipelineBuffer, ByVal OutputMap As OutputNameMap) For I As Integer = 0 To Outputs - 1 If OutputIDs(I) = GetOutputID(OutputMap, "Output") Then OutputBuffer = New OutputBuffer(Me, OutputIDs(I), False, Buffers(I), OutputMap) End If Next CreateNewOutputRows() FinishOutputs() MarkOutputsFinished() End Sub Public Overridable Sub CreateNewOutputRows() End Sub End Class Public Class Connections Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub Public ReadOnly Property Http() As IDTSConnectionManager100 Get Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection("Http").ConnectionManager End Get End Property End Class Public Class Variables Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub End Class ]]> msBuild ScriptComponent_0c4f2d277da94cd3bcb7ec6aa65b1a54 ScriptComponent_0c4f2d277da94cd3bcb7ec6aa65b1a54 {5825756A-ADF8-4A1B-AF74-030E4708C3E3} ]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_0c4f2d277da94cd3bcb7ec6aa65b1a54VisualBasic