_ _ _ _ Public Class ScriptMain Inherits UserComponent Private JobUrl_3P As String = "{0}://{1}/services/async/{2}/job/" Private BulkNS As String = "http://www.force.com/2009/06/asyncapi/dataload" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Dim batchId As String = CStr(Row.Buffer(m_batchIdx)) Try If m_batchId <> batchId Then ' Load next batch. m_batchId = batchId If String.IsNullOrEmpty(m_jobId) Then ' Load job identifier. m_jobId = CStr(GetVariable_(Me.JobIdVariable)) End If If Not m_batchRes Is Nothing Then Call m_batchRes.Dispose() End If m_batchRes = GetBatchResult_(m_service, m_jobId, m_batchId) End If ' Get batch result. Dim record() As String = m_batchRes.ReadFields() If record Is Nothing Then ' There is no more data. Exit Sub End If With Row .ResultId = record(0) .Success = Convert.ToBoolean(record(1)) .Created = Convert.ToBoolean(record(2)) .Error = record(3) End With Catch ex As Exception Call FireError_(ex.ToString()) End Try End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() m_connection = CType( _ MyBase.Connections.Connection.AcquireConnection(Nothing), _ ISforceConnection) m_service = CType(m_connection.Connect(), SforceService) Dim input As IDTSInput100 = Me.ComponentMetaData.InputCollection(0) ' Get batch column index. Dim inputCol As IDTSInputColumn100 = input.InputColumnCollection(Me.BatchIdColumn) m_batchIdx = Me.HostComponent.BufferManager.FindColumnByLineageID(input.Buffer, inputCol.LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() Call m_connection.Close() End Sub ' PostExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.Connection) Then Throw New ApplicationException("Select Salesforce connection.") End If If String.IsNullOrEmpty(Me.JobIdVariable) Then Throw New ApplicationException("Select job id variable.") End If If String.IsNullOrEmpty(Me.BatchIdColumn) Then Throw New ApplicationException("Select batch id column.") End If ' Setup batch id column. Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0) Call input.InputColumnCollection.RemoveAll() Dim virtInput As IDTSVirtualInput100 = input.GetVirtualInput() Dim virtColumn As IDTSVirtualInputColumn100 = virtInput.VirtualInputColumnCollection(Me.BatchIdColumn) Call virtInput.SetUsageType(virtColumn.LineageID, DTSUsageType.UT_READONLY) ' Store connection information in the runtime connection collection, too. ' Cannot directly use RuntimeConnectionCollection in the property get/set ' because of issues with multi-threading. Me.ComponentMetaData.RuntimeConnectionCollection("Connection").ConnectionManagerID = Me.Connection result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Connection() As String Get Connection = m_sforceConnection End Get Set(ByVal value As String) m_sforceConnection = value End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property JobIdVariable() As String Get JobIdVariable = m_jobIdVar End Get Set(ByVal value As String) m_jobIdVar = value End Set End Property ' JobIdVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property BatchIdColumn() As String Get BatchIdColumn = m_batchIdCol End Get Set(ByVal value As String) m_batchIdCol = value End Set End Property ' BatchIdColumn #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property ConnectionType() As String() Get ConnectionType = New String() {"SFORCE"} End Get End Property ' ConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean = False Call MyBase.ComponentMetaData.FireError( _ 0, _ "Salesforce Bulk Result", _ 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 = Nothing Call Me.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Find the API version in use by stepping the service URL from the back. ' The first segment which can be converted to number is the version. Private Function GetServiceVersion_(ByVal url As Uri) As Double Dim result As Double = 27 Dim segments As String() = url.Segments Dim segsCount As Integer = segments.Length For segIndex As Integer = segsCount - 1 To 0 Step -1 Dim segment As String = segments(segIndex).Trim("/"c) If Double.TryParse(segment, NumberStyles.Any, CultureInfo.InvariantCulture, result) Then Exit For End If Next GetServiceVersion_ = result End Function ' GetServiceVersion_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function PrepareRequest_( _ ByVal service As SforceService, _ ByVal url As String) As HttpWebRequest ' Setup base job request url. Dim serviceUri As Uri = New Uri(service.Url) Dim jobUrl As String = String.Format( _ JobUrl_3P, _ serviceUri.Scheme, _ serviceUri.Host, _ GetServiceVersion_(serviceUri)) Dim result As HttpWebRequest = CType(WebRequest.Create(jobUrl + url), HttpWebRequest) Call result.Headers.Add("X-SFDC-Session", service.SessionHeaderValue.sessionId) PrepareRequest_ = result End Function ' PrepareRequest_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetXmlResult_(ByVal req As HttpWebRequest) As XmlDocument Dim result As New XmlDocument Try Using res As WebResponse = req.GetResponse() Call result.Load(res.GetResponseStream()) End Using Catch ex As WebException ' The regular exception information is not very useful. Extract error information. If ex.Response Is Nothing Then ' Error happened before server was able to respond. Throw End If Using errorStream As Stream = ex.Response.GetResponseStream() Throw New Exception(New StreamReader(errorStream).ReadToEnd(), ex) End Using End Try GetXmlResult_ = result End Function ' GetXmlResult_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetCsvResult_(ByVal req As HttpWebRequest) As TextFieldParser Dim result As TextFieldParser Try m_readRes = req.GetResponse() result = New TextFieldParser(m_readRes.GetResponseStream()) result.HasFieldsEnclosedInQuotes = True Call result.SetDelimiters(",") ' Skip first row which is header. Call result.ReadFields() Catch ex As WebException ' The regular exception information is not very useful. Extract error information. If ex.Response Is Nothing Then ' Error happened before server was able to respond. Throw End If Using errorStream As Stream = ex.Response.GetResponseStream() Throw New Exception(New StreamReader(errorStream).ReadToEnd(), ex) End Using End Try GetCsvResult_ = result End Function ' GetCsvResult_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetBatchResult_( _ ByVal service As SforceService, _ ByVal jobId As String, _ ByVal batchId As String) As TextFieldParser ' Loop until batch result is ready. While True Dim doc As XmlDocument = GetXmlResult_(PrepareRequest_(service, jobId + "/batch/" + batchId)) Dim ns As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable) Call ns.AddNamespace("bl", BulkNS) Dim state As String = doc.SelectSingleNode("/bl:batchInfo/bl:state", ns).InnerText Select Case state Case "Completed" ' Ready. Exit While Case "InProgress", "Queued" ' Have to wait. Case "Failed", "Not Processed" Throw New Exception(String.Format("'{0}' batch failed or aborted.", batchId)) End Select ' Wait 5 seconds and try again. Call Thread.Sleep(5 * 1000) End While GetBatchResult_ = GetCsvResult_(PrepareRequest_(service, jobId + "/batch/" + batchId + "/result")) End Function ' GetBatchResult_ #End Region ' Internals #Region "Attributes" Private m_sforceConnection As String Private m_jobIdVar As String Private m_batchIdCol As String Private m_connection As ISforceConnection Private m_service As SforceService Private m_batchIdx As Integer Private m_jobId As String Private m_batchId As String Private m_readRes As WebResponse Private m_batchRes As TextFieldParser #End Region 'Attributes End Class ' ScriptMain ]]> _ Public Class InputBuffer 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 WriteOnly Property [ResultId]() As String Set Me(0) = Value End Set End Property Public WriteOnly Property [ResultId_IsNull] As Boolean Set If (value) SetNull(0) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Success]() As Boolean Set Me(1) = Value End Set End Property Public WriteOnly Property [Success_IsNull] As Boolean Set If (value) SetNull(1) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Created]() As Boolean Set Me(2) = Value End Set End Property Public WriteOnly Property [Created_IsNull] As Boolean Set If (value) SetNull(2) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Error]() As String Set Me(3) = Value End Set End Property Public WriteOnly Property [Error_IsNull] As Boolean Set If (value) SetNull(3) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public Overrides ReadOnly Property StaticOutputColumns() As String() Get Return New String() {"ResultId", "Success", "Created", "Error"} End Get End Property Public Overloads Function NextRow() As Boolean NextRow = MyBase.NextRow() End Function 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 {A47DD1EF-D780-450A-9F4F-71911350A163} Library My Project ScriptComponent_e6007d71caeb4ae18f1e3ca85dc10c00 ScriptComponent_e6007d71caeb4ae18f1e3ca85dc10c00 v4.0 512 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\130\SDK\Assemblies\CozyRoc.SSISPlus.2016.dll SSIS_SC130 Binary On On ]]> _ Public Class UserComponent Inherits ScriptComponentPlus Public Connections As New Connections(Me) Public Variables As New Variables(Me) Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal InputName As String, ByVal Buffer As PipelineBuffer, ByVal OutputMap As OutputNameMap) If InputID = MyBase.ComponentMetaData.InputCollection("Input").ID Then Input_ProcessInput(New InputBuffer(Me, InputID, True, Buffer, OutputMap)) End If End Sub Public Overridable Sub Input_ProcessInput(ByVal Buffer As InputBuffer) While Buffer.NextRow() Input_ProcessInputRow(Buffer) End While End Sub Public Overridable Sub Input_ProcessInputRow(ByVal Row As InputBuffer) 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 Connection() As IDTSConnectionManager100 Get Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection("Connection").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_e6007d71caeb4ae18f1e3ca85dc10c00 Reference;Import;Folder {9881464E-5AF8-46FC-B3EA-6B470CE73FC6} ScriptComponent_e6007d71caeb4ae18f1e3ca85dc10c00 ]]>CozyRoc.ScriptComponentHostPlusScriptComponent_e6007d71caeb4ae18f1e3ca85dc10c00VisualBasic