]]> _
_
_
Public Class ScriptMain
Inherits UserComponent
Private Const QueryTemplate_3P As String = "select {0} from {1} where {2}"
Private Const ListDelimiter As String = "," + vbLf
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim valueList() As String
If m_cache Is Nothing Then
valueList = GetLookupRow_(Row)
Else
' Cache is enabled.
Dim cacheKey As String = GetCacheKey_(Row)
If m_cache.Contains(cacheKey) Then
valueList = CType(m_cache(cacheKey), String())
Else
valueList = GetLookupRow_(Row)
m_cache(cacheKey) = valueList
End If
End If
If Not valueList Is Nothing Then
Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0)
' Lookup row found.
Dim colsCount As Integer = m_valueIdx.Length
For colIndex As Integer = 0 To colsCount - 1
Dim value As String = valueList(colIndex)
Dim valueIndex As Integer = m_valueIdx(colIndex)
If String.IsNullOrEmpty(value) Then
Call Row.Buffer.SetNull(valueIndex)
Continue For
End If
Try
Select Case m_valueType(colIndex)
Case DataType.DT_DBTIMESTAMP
Case DataType.DT_DATE
Case DataType.DT_FILETIME
Case DataType.DT_DBDATE
Row.Buffer(valueIndex) = Convert.ToDateTime(value)
Case DataType.DT_IMAGE
Row.Buffer(valueIndex) = Convert.FromBase64String(value)
Case Else
Row.Buffer(valueIndex) = value
End Select
Catch ex As Exception
Call FireError_(String.Format( _
"Failed to process '{0}' for column '{1}'.", _
value, _
output.OutputColumnCollection(colIndex).Name))
End Try
Next
End If
End Sub ' Input_ProcessInputRow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
' Setup connection.
m_connection = CType( _
Me.Connections.Connection.AcquireConnection(Nothing), _
ISforceConnection)
m_service = CType(m_connection.Connect(), SforceService)
If Me.UseCache Then
m_cache = New Hashtable()
End If
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
Dim colsCount As Integer = input.InputColumnCollection.Count
' Setup key index and where list.
m_keyIdx = New Integer(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim inputCol As IDTSInputColumn90 = input.InputColumnCollection(colIndex)
' Set key index.
m_keyIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
inputCol.LineageID)
' Set where list.
Dim extCol As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection.GetObjectByID( _
inputCol.ExternalMetadataColumnID)
If Not String.IsNullOrEmpty(m_whereTemplate_P) Then
m_whereTemplate_P += " AND "
End If
m_whereTemplate_P += String.Format( _
"{0} = {1}", _
extCol.Name, _
"{" + colIndex.ToString() + "}")
Next
Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0)
colsCount = output.OutputColumnCollection.Count
' Setup value index and field list.
m_valueIdx = New Integer(colsCount - 1) {}
m_valueType = New DataType(colsCount - 1) {}
Dim fieldList As String = String.Empty
For colIndex As Integer = 0 To colsCount - 1
Dim outputCol As IDTSOutputColumn90 = output.OutputColumnCollection(colIndex)
' Set value index and type.
m_valueIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
outputCol.LineageID)
m_valueType(colIndex) = outputCol.DataType
' Set field list.
If Not String.IsNullOrEmpty(fieldList) Then
fieldList += ", "
End If
fieldList += outputCol.Name
Next
' Setup query template.
m_queryTemplate_1P = String.Format( _
QueryTemplate_3P, _
fieldList, _
Me.SalesforceObject, _
"{0}")
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.SalesforceObject) Then
Throw New ApplicationException("Select object.")
End If
If String.IsNullOrEmpty(Me.KeyField) Then
Throw New ApplicationException("Specify key field(s).")
End If
If String.IsNullOrEmpty(Me.ValueField) Then
Throw New ApplicationException("Select value field.")
End If
Dim keyList() As String = Me.KeyField.Split( _
New String() {ListDelimiter}, _
StringSplitOptions.None)
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
If input.IsAttached AndAlso input.InputColumnCollection.Count < keyList.Length Then
Throw New Exception("Map input columns.")
End If
Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0)
If output.OutputColumnCollection.Count = 0 Then
' Setup collection output columns.
Call SetupOutputColumns_()
End If
' 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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub ReinitializeMetaData()
Call MyBase.ReinitializeMetaData()
If String.IsNullOrEmpty(Me.Connection) Then
Exit Sub
End If
If MyBase.ComponentMetaData.InputCollection.Count = 0 Then
' At least one input must exist.
Throw New Exception("No inputs.")
End If
If String.IsNullOrEmpty(Me.KeyField) Then
Throw New Exception("Key field(s) not selected.")
End If
' Get object fields.
Dim fieldList() As Field = GetFields_()
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
' Cleanup.
input.ExternalMetadataColumnCollection.IsUsed = True
Call input.InputColumnCollection.RemoveAll()
Call input.ExternalMetadataColumnCollection.RemoveAll()
' Setup field metadata.
Dim keyList() As String = Me.KeyField.Split( _
New String() {ListDelimiter}, _
StringSplitOptions.None)
For Each key As String In keyList
' Find field.
Dim field As Field = FindField_(fieldList, key)
Dim dtsType As DataType
Dim fieldLen, prec, scale, codePage As Integer
Call GetDtsTypeFromField_(field, dtsType, fieldLen, prec, scale, codePage)
' Setup external column.
Dim column As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection.[New]()
column.Name = field.name
column.DataType = dtsType
column.Length = fieldLen
column.Precision = prec
column.Scale = scale
column.CodePage = codePage
Next
End Sub ' ReinitializeMetaData
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of objects in the currently selected Salesforce connection.
Public Function GetObjectList() As String()
Dim result As New ArrayList
Dim connection As ISforceConnection = CType( _
Me.DesignConnections(Me.Connection).AcquireConnection(Nothing), _
ISforceConnection)
Dim service As SforceService = CType(connection.Connect(), SforceService)
Try
Dim dgr As DescribeGlobalResult = service.describeGlobal()
For Each sobject As DescribeGlobalSObjectResult In dgr.sobjects
If sobject.queryable Then
Call result.Add(sobject.name)
End If
Next
Call result.Sort()
Finally
Call connection.Close()
End Try
GetObjectList = CType(result.ToArray(GetType(String)), String())
End Function ' GetObjectList
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of fields in the currently selected object.
Public Function GetFieldList() As String()
Dim result As ArrayList = New ArrayList()
Dim fieldList() As Field = GetFields_()
For Each field As Field In fieldList
Call result.Add(field.name)
Next
Call result.Sort()
GetFieldList = CType(result.ToArray(GetType(String)), String())
End Function ' GetFieldList
#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 SalesforceObject() As String
Get
SalesforceObject = m_object
End Get
Set(ByVal value As String)
If m_object <> value Then
m_object = value
m_fields = Nothing
Me.KeyField = String.Empty
Me.ValueField = String.Empty
End If
End Set
End Property ' SalesforceObject
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property KeyField() As String
Get
KeyField = m_keyField
End Get
Set(ByVal value As String)
If m_keyField <> value Then
If Not String.IsNullOrEmpty(m_keyField) Then
' Force external columns to be reloaded.
If Me.ComponentMetaData.InputCollection.Count > 0 Then
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
Call input.ExternalMetadataColumnCollection.RemoveAll()
End If
End If
m_keyField = value
End If
End Set
End Property ' KeyField
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property ValueField() As String
Get
ValueField = m_valueField
End Get
Set(ByVal value As String)
If m_valueField <> value Then
If Not String.IsNullOrEmpty(m_valueField) 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_valueField = value
End If
End Set
End Property ' ValueField
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property UseCache() As Boolean
Get
UseCache = m_useCache
End Get
Set(ByVal value As Boolean)
m_useCache = value
End Set
End Property ' UseCache
#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 Lookup", _
message, _
String.Empty, _
0, _
cancel)
End Sub ' FireError_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function FindField_( _
ByVal fieldList() As Field, _
ByVal name As String) As Field
Dim result As Field = Nothing
For Each field As Field In fieldList
If field.name.Equals(name, StringComparison.OrdinalIgnoreCase) Then
' Found.
result = field
Exit For
End If
Next
If result Is Nothing Then
Throw New Exception(String.Format("'{0}' field not found.", name))
End If
FindField_ = result
End Function ' FindField_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Setups cache key based on specified key attributes.
Private Function GetCacheKey_(ByVal row As InputBuffer) As String
Dim result As String = String.Empty
For Each idx As Integer In m_keyIdx
Dim key As String = String.Empty
If Not row.Buffer.IsNull(idx) Then
key = row.Buffer(idx).ToString() + "_"
End If
result += key
Next
GetCacheKey_ = result
End Function ' GetCacheKey_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Uses SOQL to retrieve required lookup row.
Private Function GetLookupRow_(ByVal row As InputBuffer) As String()
Dim result() As String = Nothing
' Setup fetch conditions.
Dim colsCount As Integer = m_keyIdx.Length
Dim whereList() As Object = New Object(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim keyIndex As Integer = m_keyIdx(colIndex)
Dim key As String = String.Empty
If row.Buffer.IsNull(keyIndex) Then
key = "NULL"
Else
Dim keyObj As Object = row.Buffer(keyIndex)
If TypeOf keyObj Is String Then
key = String.Format("'{0}'", keyObj)
Else
key = keyObj.ToString()
End If
End If
whereList(colIndex) = key
Next
' Query data.
Dim record As sObject = Nothing
Dim query As String = String.Format( _
m_queryTemplate_1P, _
String.Format(m_whereTemplate_P, whereList))
Try
Dim qr As QueryResult = m_service.query(query)
If Not qr.records Is Nothing AndAlso qr.records.Length > 0 Then
record = qr.records(0)
End If
Catch ex As Exception
Call FireError_(ex.Message)
End Try
If Not record Is Nothing Then
Dim fieldsCount As Integer = m_valueIdx.Length
result = New String(fieldsCount - 1) {}
For fieldIndex As Integer = 0 To fieldsCount - 1
Dim fieldNode As XmlElement = record.Any(fieldIndex)
result(fieldIndex) = fieldNode.InnerText
Next
End If
GetLookupRow_ = result
End Function ' GetLookupRow_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function GetFields_() As Field()
Dim result() As Field = m_fields
If result Is Nothing Then
Dim connection As ISforceConnection = CType( _
Me.DesignConnections(Me.Connection).AcquireConnection(Nothing), _
ISforceConnection)
Dim service As SforceService = CType(connection.Connect(), SforceService)
Try
result = service.describeSObject(Me.SalesforceObject).fields
Finally
Call connection.Close()
End Try
m_fields = result
End If
GetFields_ = result
End Function ' GetFields_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SetupOutputColumns_()
' Get entity attributes.
Dim fieldList() As Field = GetFields_()
Dim valueList() As String = Me.ValueField.Split( _
New String() {ListDelimiter}, _
StringSplitOptions.None)
Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0)
For Each value As String In valueList
' Find Salesforce field.
Dim field As Field = FindField_(fieldList, value)
Dim dtsType As DataType
Dim fieldLen, prec, scale, codePage As Integer
Call GetDtsTypeFromField_(field, dtsType, fieldLen, prec, scale, codePage)
' Setup output column.
Dim column As IDTSOutputColumn90 = output.OutputColumnCollection.[New]()
column.Name = field.name
Call column.SetDataTypeProperties( _
dtsType, _
fieldLen, _
prec, _
scale, _
codePage)
Next
End Sub ' SetupOutputColumns_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub GetDtsTypeFromField_( _
ByVal field As Field, _
ByRef DtsType As DataType, _
ByRef fieldLen As Integer, _
ByRef Precision As Integer, _
ByRef Scale As Integer, _
ByRef CodePage As Integer)
fieldLen = field.length
Precision = field.precision
Scale = field.scale
CodePage = 0
Select Case field.soapType
Case soapType.tnsID
DtsType = DataType.DT_WSTR
Case soapType.xsdbase64Binary
DtsType = DataType.DT_IMAGE
Case soapType.xsdboolean
DtsType = DataType.DT_BOOL
Case soapType.xsddouble
DtsType = DataType.DT_NUMERIC
Case soapType.xsdint
DtsType = DataType.DT_BYREF_I4
Case soapType.xsddate
DtsType = DataType.DT_DBDATE
Case soapType.xsddateTime
DtsType = DataType.DT_DBTIMESTAMP
Case soapType.xsdtime
DtsType = DataType.DT_DBTIME
Case soapType.xsdanyType
DtsType = DataType.DT_WSTR
Case soapType.xsdstring
DtsType = DataType.DT_WSTR
If fieldLen >= 4000 Then
' DT_WSTR is limited to 4000.
DtsType = DataType.DT_NTEXT
fieldLen = 0
End If
Case Else
Throw New Exception("Unhandled soap type.")
End Select
End Sub ' GetDtsTypeFromField_
#End Region ' Internals
#Region "Attributes"
Private m_sforceConnection As String
Private m_object As String
Private m_keyField As String
Private m_valueField As String
Private m_useCache As Boolean
Private m_connection As ISforceConnection
Private m_service As SforceService
Private m_cache As Hashtable
Private m_queryTemplate_1P As String
Private m_whereTemplate_P As String
Private m_keyIdx() As Integer
Private m_valueIdx() As Integer
Private m_fields() As Field
Private m_valueType() As DataType
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_22b4f911b2584ff4bfeab0b76184dee6trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea