' This code was generated by a tool. ' ' Changes to this file may cause incorrect behavior and will be lost if ' the code is regenerated. ' '------------------------------------------------------------------------------ Option Strict Off Option Explicit On Partial Friend NotInheritable Class MySettings Inherits System.Configuration.ApplicationSettingsBase Private Shared m_Value As MySettings Private Shared m_SyncObject As Object = New Object _ Public Shared ReadOnly Property Value() As MySettings Get If (MySettings.m_Value Is Nothing) Then System.Threading.Monitor.Enter(MySettings.m_SyncObject) If (MySettings.m_Value Is Nothing) Then Try MySettings.m_Value = New MySettings Finally System.Threading.Monitor.Exit(MySettings.m_SyncObject) End Try End If End If Return MySettings.m_Value End Get End Property End Class ]]> {30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F} Debug AnyCPU Library ScriptComponent_368ccdaf92cc423795cfa2e05a519565.vbproj ScriptComponent_368ccdaf92cc423795cfa2e05a519565.vbproj On Binary Off On {AE9D8F35-83D2-40CD-B00C-151837C9D6F5} true true true false bin\ false false 42016,42017,42018,42019,42032,42353,42354,42355 false true false true bin\ false false 42016,42017,42018,42019,42032,42353,42354,42355 False C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\CozyRoc.SSISPlus.2012.dll False C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll Code VbMyResourcesResXFileCodeGenerator Resources.Designer.vb My.Resources True True Resources.resx Code SettingsSingleFileGenerator Settings.Designer.vb True Settings.settings Code SSIS_SC110 v4.0 ]]> 'The following GUID is for the ID of the typelib if this project is exposed to COM ' Version information for an assembly consists of the following four values: ' ' Major Version ' Minor Version ' Build Number ' Revision ' ' You can specify all the values or you can default the Build and Revision Numbers ' by using the '*' as shown below: ' ]]> _ 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 CrmConnection() As IDTSConnectionManager100 Get Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection("CrmConnection").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 ]]> _ 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 Overrides ReadOnly Property StaticOutputColumns() As String() Get Return New String() {} 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 ]]> _ _ _ _ _ Public Class ScriptMain Inherits UserComponent Public Const FetchTemplate_3P As String = "{1}{2}" Public Const AttrTemplate_1P As String = "" Public Const CondTemlate_2P As String = "" Public 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 ' 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 value Is Nothing Then Call Row.Buffer.SetNull(valueIndex) Else Select Case m_valueType(colIndex) Case DataType.DT_GUID Row.Buffer(valueIndex) = New Guid(value) 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 End If Next End If End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Setup connection. m_connection = CType( _ Me.Connections.CrmConnection.AcquireConnection(Nothing), _ ICrmConnection) Call m_connection.Connect() If Me.UseCache Then m_cache = New Hashtable() End If Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0) Dim colsCount As Integer = input.InputColumnCollection.Count ' Setup key index and condition list. m_keyIdx = New Integer(colsCount - 1) {} For colIndex As Integer = 0 To colsCount - 1 Dim inputCol As IDTSInputColumn100 = input.InputColumnCollection(colIndex) ' Set key index. m_keyIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ inputCol.LineageID) ' Set condition list. Dim extCol As IDTSExternalMetadataColumn100 = input.ExternalMetadataColumnCollection.GetObjectByID( _ inputCol.ExternalMetadataColumnID) m_condTemplate_P += String.Format( _ CondTemlate_2P, _ extCol.Name, _ "{" + colIndex.ToString() + "}") Next Dim output As IDTSOutput100 = MyBase.ComponentMetaData.OutputCollection(0) colsCount = output.OutputColumnCollection.Count ' Setup value index and attribute list. m_attrToIdx = New Hashtable() m_valueIdx = New Integer(colsCount - 1) {} m_valueType = New DataType(colsCount - 1) {} Dim attrList As String = String.Empty For colIndex As Integer = 0 To colsCount - 1 Dim outputCol As IDTSOutputColumn100 = output.OutputColumnCollection(colIndex) m_attrToIdx(outputCol.Name) = colIndex ' Set value index and type. m_valueIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ outputCol.LineageID) m_valueType(colIndex) = outputCol.DataType ' Set attribute list. attrList += String.Format(AttrTemplate_1P, outputCol.Name) Next ' Setup fetch template. m_fetchTemplate_1P = String.Format( _ FetchTemplate_3P, _ Me.Entity, _ attrList, _ "{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.CrmConnection) Then Throw New ApplicationException("Select Dynamics CRM connection.") End If If String.IsNullOrEmpty(Me.Entity) Then Throw New ApplicationException("Select entity.") End If If String.IsNullOrEmpty(Me.KeyAttribute) Then Throw New ApplicationException("Specify key attribute(s).") End If If String.IsNullOrEmpty(Me.ValueAttribute) Then Throw New ApplicationException("Select value attribute.") End If Dim keyList() As String = Me.KeyAttribute.Split( _ New String() {ListDelimiter}, _ StringSplitOptions.None) Dim input As IDTSInput100 = 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 IDTSOutput100 = 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("CrmConnection").ConnectionManagerID = Me.CrmConnection 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 MyBase.ComponentMetaData.InputCollection.Count = 0 Then ' At least one input must exist. Throw New Exception("No inputs.") End If If String.IsNullOrEmpty(Me.KeyAttribute) Then Throw New Exception("Key attribute(s) not selected.") End If ' Get entity attributes. Dim attrList() As ICrmAttribute = GetAttributes_() Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0) ' Cleanup. input.ExternalMetadataColumnCollection.IsUsed = True Call input.InputColumnCollection.RemoveAll() Call input.ExternalMetadataColumnCollection.RemoveAll() ' Setup attribute metadata. Dim keyList() As String = Me.KeyAttribute.Split( _ New String() {ListDelimiter}, _ StringSplitOptions.None) For Each key As String In keyList ' Find CRM attribute. Dim attr As ICrmAttribute = FindAttribute_(attrList, key) ' Setup external column. Dim column As IDTSExternalMetadataColumn100 = input.ExternalMetadataColumnCollection.[New]() column.Name = attr.Name column.DataType = CType(attr.AttributeType, DataType) column.Length = attr.Length column.Precision = attr.Precision column.Scale = attr.Scale column.CodePage = 0 Next End Sub ' ReinitializeMetaData ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of entities in the currently selected Dynamics CRM connection. Public Function GetEntityList() As String() Dim result() As String Dim connection As ICrmConnection = CType( _ Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _ ICrmConnection) Call connection.Connect() Try result = connection.GetEntityList(2) ' 2 - Read Call Array.Sort(result) Finally Call connection.Close() End Try GetEntityList = result End Function ' GetEntityList ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of attributes in the currently selected entity. Public Function GetAttributeList() As String() Dim result As ArrayList = New ArrayList() Dim attrList() As ICrmAttribute = GetAttributes_() For Each attr As ICrmAttribute In attrList Call result.Add(attr.Name) Next Call result.Sort() GetAttributeList = CType(result.ToArray(GetType(String)), String()) End Function ' GetAttributeList #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property CrmConnection() As String Get CrmConnection = m_crmConnection End Get Set(ByVal value As String) m_crmConnection = value End Set End Property ' CrmConnection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Entity() As String Get Entity = m_entity End Get Set(ByVal value As String) If m_entity <> value Then m_entity = value m_attributes = Nothing Me.KeyAttribute = String.Empty Me.ValueAttribute = String.Empty End If End Set End Property ' Entity ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property KeyAttribute() As String Get KeyAttribute = m_keyAttribute End Get Set(ByVal value As String) If m_keyAttribute <> value Then If Not String.IsNullOrEmpty(m_keyAttribute) Then ' Force external columns to be reloaded. If Me.ComponentMetaData.InputCollection.Count > 0 Then Dim input As IDTSInput100 = Me.ComponentMetaData.InputCollection(0) Call input.ExternalMetadataColumnCollection.RemoveAll() End If End If m_keyAttribute = value End If End Set End Property ' KeyAttribute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property ValueAttribute() As String Get ValueAttribute = m_valueAttribute End Get Set(ByVal value As String) If m_valueAttribute <> value Then If Not String.IsNullOrEmpty(m_valueAttribute) 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_valueAttribute = value End If End Set End Property ' ValueAttribute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ 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 CrmConnectionType() As String() Get CrmConnectionType = New String() {"DYNAMICS-CRM"} End Get End Property ' CrmConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean = False Call MyBase.ComponentMetaData.FireError( _ 0, _ "Dynamics CRM Lookup", _ message, _ String.Empty, _ 0, _ cancel) End Sub ' FireError_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function FindAttribute_( _ ByVal attrList() As ICrmAttribute, _ ByVal name As String) As ICrmAttribute Dim result As ICrmAttribute = Nothing For Each attr As ICrmAttribute In attrList If attr.Name.Equals(name, StringComparison.OrdinalIgnoreCase) Then ' Found. result = attr Exit For End If Next If result Is Nothing Then Throw New Exception(String.Format("'{0}' attribute not found.", name)) End If FindAttribute_ = result End Function ' FindAttribute_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 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 CRM Fetch 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 condList() 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 Not row.Buffer.IsNull(keyIndex) Then key = row.Buffer(keyIndex).ToString() End If condList(colIndex) = SecurityElement.Escape(key) Next ' Fetch data. Dim fetchStmt As String = String.Format( _ m_fetchTemplate_1P, _ String.Format(m_condTemplate_P, condList)) Dim doc As XmlDocument = New XmlDocument() Try Call doc.LoadXml(m_connection.Fetch(fetchStmt)) Catch ex As SoapException Call FireError_(ex.Detail.InnerXml) Catch ex As Exception Call FireError_(ex.Message) End Try Dim recsList As XmlNodeList = doc.DocumentElement.ChildNodes If recsList.Count > 0 Then result = New String(m_valueIdx.Length - 1) {} For Each element As XmlElement In recsList(0).ChildNodes Dim colIndex As Object = m_attrToIdx(element.Name) If colIndex Is Nothing Then Continue For End If result(CInt(colIndex)) = element.InnerText Next End If GetLookupRow_ = result End Function ' GetLookupRow_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetAttributes_() As ICrmAttribute() Dim result() As ICrmAttribute = m_attributes If result Is Nothing Then Dim connection As ICrmConnection = CType( _ Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _ ICrmConnection) Call connection.Connect() Try result = connection.GetEntityAttributes(Me.Entity) Finally Call connection.Close() End Try m_attributes = result End If GetAttributes_ = result End Function ' GetAttributes_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub SetupOutputColumns_() ' Get entity attributes. Dim attrList() As ICrmAttribute = GetAttributes_() Dim valueList() As String = Me.ValueAttribute.Split( _ New String() {ListDelimiter}, _ StringSplitOptions.None) Dim output As IDTSOutput100 = Me.ComponentMetaData.OutputCollection(0) For Each value As String In valueList ' Find CRM attribute. Dim attr As ICrmAttribute = FindAttribute_(attrList, value) ' Setup output column. Dim column As IDTSOutputColumn100 = output.OutputColumnCollection.[New]() column.Name = attr.Name Call column.SetDataTypeProperties( _ CType(attr.AttributeType, DataType), _ attr.Length, _ attr.Precision, _ attr.Scale, _ 0) Next End Sub ' SetupOutputColumns_ #End Region ' Internals #Region "Attributes" Private m_crmConnection As String Private m_entity As String Private m_keyAttribute As String Private m_valueAttribute As String Private m_useCache As Boolean Private m_connection As ICrmConnection Private m_cache As Hashtable Private m_fetchTemplate_1P As String Private m_keyIdx() As Integer Private m_condTemplate_P As String Private m_valueIdx() As Integer Private m_attributes() As ICrmAttribute Private m_attrToIdx As Hashtable Private m_valueType() As DataType #End Region ' Attributes End Class ' ScriptMain ]]> msBuild Reference;Import;Folder {E1551EB3-AA16-47C5-9929-060BD632AF6D} scriptcomponent_368ccdaf92cc423795cfa2e05a519565 scriptcomponent_368ccdaf92cc423795cfa2e05a519565 ]]> ]]> text/microsoft-resx 2.0 System.Resources.ResXResourceReader, System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 System.Resources.ResXResourceWriter, System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 ]]> ' This code was generated by a tool. ' ' Changes to this file may cause incorrect behavior and will be lost if ' the code is regenerated. ' '------------------------------------------------------------------------------ Option Strict Off Option Explicit On Namespace My.Resources ''' ''' A strongly-typed resource class, for looking up localized strings, etc. ''' 'This class was auto-generated by the Strongly Typed Resource Builder 'class via a tool like ResGen or Visual Studio.NET. 'To add or remove a member, edit your .ResX file then rerun ResGen 'with the /str option, or rebuild your VS project. Class MyResources Private Shared _resMgr As System.Resources.ResourceManager Private Shared _resCulture As System.Globalization.CultureInfo Friend Sub New() MyBase.New End Sub ''' ''' Returns the cached ResourceManager instance used by this class. ''' _ Public Shared ReadOnly Property ResourceManager() As System.Resources.ResourceManager Get If (_resMgr Is Nothing) Then Dim temp As System.Resources.ResourceManager = New System.Resources.ResourceManager("My.Resources.MyResources", GetType(MyResources).Assembly) _resMgr = temp End If Return _resMgr End Get End Property ''' ''' Overrides the current thread's CurrentUICulture property for all ''' resource lookups using this strongly typed resource class. ''' _ Public Shared Property Culture() As System.Globalization.CultureInfo Get Return _resCulture End Get Set _resCulture = value End Set End Property End Class End Namespace ]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2012, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_368ccdaf92cc423795cfa2e05a519565VisualBasic