' 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 ]]> '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 InputBuffer Inherits ScriptBufferPlus Public Sub New(ByVal Component As ScriptComponent, ByVal ObjectID As Integer, ByVal IsInput As Boolean, ByVal Buffer As PipelineBuffer) MyBase.New(Component, ObjectID, IsInput, Buffer) 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 ]]> ' 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 ]]> _ 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 Buffer As PipelineBuffer) If InputID = MyBase.ComponentMetaData.InputCollection("Input").ID Then Input_ProcessInput(New InputBuffer(Me, InputID, True, Buffer)) 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 ]]> 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 ]]> _ _ _ _ _ Public Class ScriptMain Inherits UserComponent Private Class Consts Public Const ColumnSeparator As String = "," + vbLf Public Const DElement_1P As String = "D.[{0}]" Public Const SElement_1P As String = "S.[{0}]" Public Const CreateStageSql_2P As String = "SELECT * INTO {0} FROM {1} WHERE 1=0" Public Const MergeSql_7P As String = _ "MERGE {0} AS D USING {1} AS S ON ({2}) " & _ "WHEN NOT MATCHED THEN INSERT ({3}) VALUES ({4}) " & _ "WHEN MATCHED AND ({5}) THEN UPDATE SET {6};" Public Const DropTableSql_1P As String = "DROP TABLE {0}" End Class ' Consts ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) ' Nothing to do here. End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() Try Dim tblCols As DataTable = GetColumns_(False) Dim primaryKeys As ArrayList = New ArrayList(Me.PrimaryKeys.Split( _ New String() {Consts.ColumnSeparator}, _ StringSplitOptions.None)) ' Create MERGE statement elements. Dim onList As ArrayList = New ArrayList() Dim insertDList As ArrayList = New ArrayList() Dim insertSList As ArrayList = New ArrayList() Dim matchCompare As ArrayList = New ArrayList() Dim matchSet As ArrayList = New ArrayList() For Each columnRow As DataRow In tblCols.Rows Dim columnName As String = CStr(columnRow("ColumnName")) Call insertDList.Add(String.Format("[{0}]", columnName)) Call insertSList.Add(String.Format(Consts.SElement_1P, columnName)) If primaryKeys.Contains(columnName) Then ' Primary key. Call onList.Add(String.Format( _ "{0} = {1}", _ String.Format(Consts.DElement_1P, columnName), _ String.Format(Consts.SElement_1P, columnName))) Else Call matchCompare.Add(String.Format( _ "{0} <> {1}", _ String.Format(Consts.DElement_1P, columnName), _ String.Format(Consts.SElement_1P, columnName))) Call matchSet.Add(String.Format( _ "{0} = {1}", _ String.Format(Consts.DElement_1P, columnName), _ String.Format(Consts.SElement_1P, columnName))) End If Next ' Initialize command. Dim conn As DbConnection = GetConnection_(False) m_command = conn.CreateCommand() Dim table As String = GetTable_() ' Create stage table. Dim stage As String = GetTemporaryTable_() m_command.CommandText = String.Format(Consts.CreateStageSql_2P, stage, table) Call m_command.ExecuteNonQuery() If Not String.IsNullOrEmpty(Me.StageVariable) Then ' Store stage table name in variable. Call SetVariable_(Me.StageVariable, stage) End If ' Setup MERGE statement. m_mergeSql = String.Format( _ Consts.MergeSql_7P, _ table, _ stage, _ String.Join(" AND ", CType(onList.ToArray(GetType(String)), String())), _ String.Join(", ", CType(insertDList.ToArray(GetType(String)), String())), _ String.Join(", ", CType(insertSList.ToArray(GetType(String)), String())), _ String.Join(" OR ", CType(matchCompare.ToArray(GetType(String)), String())), _ String.Join(", ", CType(matchSet.ToArray(GetType(String)), String()))) Call FireInformation_(String.Format("Setup statement: {0}", m_mergeSql)) ' Setup drop stage table. m_dropStageSql = String.Format(Consts.DropTableSql_1P, stage) Catch ex As Exception Call FireError_(ex.Message) End Try End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() If Not m_command Is Nothing Then Try ' Execute merge statement. m_command.CommandText = m_mergeSql Call m_command.ExecuteNonQuery() ' Drop stage table. m_command.CommandText = m_dropStageSql Call m_command.ExecuteNonQuery() Catch ex As Exception Call FireError_(ex.Message) End Try Dim conn As DbConnection = m_command.Connection Call m_command.Dispose() m_command = Nothing Call conn.Dispose() End If 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 connection.") End If If String.IsNullOrEmpty(Me.Table) Then Throw New ApplicationException("Select destination table.") End If If String.IsNullOrEmpty(Me.PrimaryKeys) Then Throw New ApplicationException("Specify table primary keys.") 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 Function GetProperties() As String() Dim result As New ArrayList Call result.Add("Connection") Call result.Add("IsTableVariable") Call result.Add(IIf(Me.IsTableVariable, "TableVariable", "Table")) Call result.Add("PrimaryKeys") Call result.Add("StageVariable") GetProperties = CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of tables. Public Function GetTables() As Object() Dim result As ArrayList = New ArrayList() Try If String.IsNullOrEmpty(Me.Connection) Then Throw New Exception("Select connection.") End If ' Get tables. Using conn As DbConnection = GetConnection_(True) Dim tableType As String = "TABLE" If TypeOf conn Is SqlConnection Then ' SQL uses different table type identifier. tableType = "BASE TABLE" End If Dim tblTables As DataTable = conn.GetSchema( _ "Tables", _ New String() {Nothing, Nothing, Nothing, tableType}) ' Get table schema column name. Dim schemaCol As String = tblTables.Columns(1).ColumnName For Each tableRow As DataRow In tblTables.Rows Dim tableSchema As String = CStr(tableRow(schemaCol)) Dim tableName As String = CStr(tableRow("table_name")) Call result.Add(String.Format("[{0}].[{1}]", tableSchema, tableName)) Next Call result.Sort() End Using Catch ex As Exception Call MsgBox(ex.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation) End Try GetTables = result.ToArray() End Function ' GetTables ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns selected destination table columns. Public Function GetColumns() As Object() Dim result As ArrayList = New ArrayList Try Dim tblCols As DataTable = GetColumns_(True) For Each columnRow As DataRow In tblCols.Rows Call result.Add(columnRow("ColumnName")) Next Call result.Sort() Catch ex As Exception Call MsgBox(ex.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation) End Try GetColumns = result.ToArray() End Function ' GetColumns #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.Table = String.Empty End If End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property IsTableVariable() As Boolean Get IsTableVariable = m_isTableVar End Get Set(ByVal value As Boolean) If m_isTableVar <> value Then m_isTableVar = value Me.Table = String.Empty End If End Set End Property ' IsTableVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Table() As String Get Table = m_table End Get Set(ByVal value As String) If m_table <> value Then m_table = value Me.PrimaryKeys = GetPrimaryKeys_() End If End Set End Property ' Table ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property TableVariable() As String Get TableVariable = Me.Table End Get Set(ByVal value As String) Me.Table = value End Set End Property ' TableVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property PrimaryKeys() As String Get PrimaryKeys = m_primaryKeys End Get Set(ByVal value As String) m_primaryKeys = value End Set End Property ' PrimaryKeys ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property StageVariable() As String Get StageVariable = m_stageVar End Get Set(ByVal value As String) m_stageVar = value End Set End Property ' StageVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property AdoNetConnectionType() As String() Get AdoNetConnectionType = New String() {"ADO.NET"} End Get End Property ' AdoNetConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns selected destination table primary keys. Private Function GetPrimaryKeys_() As String Dim result As ArrayList = New ArrayList Try Dim tblCols As DataTable = GetColumns_(True) For Each columnRow As DataRow In tblCols.Rows If CBool(columnRow("IsKey")) Then ' Key column. Call result.Add(columnRow("ColumnName")) End If Next Catch ex As Exception ' Silently catch the error. End Try GetPrimaryKeys_ = String.Join( _ Consts.ColumnSeparator, _ CType(result.ToArray(GetType(String)), String())) End Function ' GetPrimaryKeys_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetColumns_(ByVal design As Boolean) As DataTable Dim result As DataTable Using conn As DbConnection = GetConnection_(design) Using cmd As DbCommand = conn.CreateCommand() cmd.CommandText = String.Format("SELECT * FROM {0}", GetTable_()) Dim reader As DbDataReader = cmd.ExecuteReader( _ CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo) result = reader.GetSchemaTable() End Using End Using GetColumns_ = result End Function ' GetColumns_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean Call MyBase.ComponentMetaData.FireError( _ 0, _ "Merge Process", _ message, _ String.Empty, _ 0, _ cancel) End Sub ' FireError_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireInformation_(ByVal message As String) Dim fireAgain As Boolean Call MyBase.ComponentMetaData.FireInformation( _ 0, _ "Merge Process", _ message, _ String.Empty, _ 0, _ fireAgain) End Sub ' FireInformation_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get connection object. Private Function GetConnection_(ByVal design As Boolean) As DbConnection Dim result As DbConnection If design Then result = CType( _ MyBase.DesignConnections(Me.Connection).AcquireConnection(Nothing), _ DbConnection) Else result = CType( _ MyBase.Connections.Connection.AcquireConnection(Nothing), _ DbConnection) End If GetConnection_ = result End Function ' GetConnection_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetVariable_(ByVal varName As String) As Object Dim result As Object If String.IsNullOrEmpty(varName) Then ' Variable not set. Exit Function End If 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_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub SetVariable_(ByVal varName As String, ByVal value As Object) If String.IsNullOrEmpty(varName) Then ' Variable not set. Exit Sub End If Dim vars As IDTSVariables100 Call MyBase.VariableDispenser.LockOneForWrite(varName, vars) Try vars(varName).Value = value Finally Call vars.Unlock() End Try End Sub ' SetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns temporary table name. Private Function GetTemporaryTable_() As String Const TempTable As String = "MPT" Dim table As String = GetTable_() GetTemporaryTable_ = String.Format( _ "[{0}].[{1}]", _ ExtractSchema_(table), _ TempTable & "_" & ExtractTable_(table) & New Random().Next(0, 1000)) End Function ' GetTemporaryTable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function ExtractSchema_(ByVal combo As String) As String Const EndS As String = "].[" ExtractSchema_ = combo.Substring(1, combo.IndexOf(EndS) - 1) End Function ' ExtractSchema_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function ExtractTable_(ByVal combo As String) As String Const Start As String = "].[" Dim indexStart As Integer = combo.IndexOf(Start) + Start.Length ExtractTable_ = combo.Substring(indexStart, combo.Length - indexStart - 1) End Function ' ExtractTable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetTable_() As String Dim result As String If Me.IsTableVariable Then result = GetVariable_(Me.TableVariable).ToString() Else result = Me.Table End If GetTable_ = result End Function ' GetTable_ #End Region ' Internals #Region "Attributes" Private m_connection As String Private m_isTableVar As Boolean Private m_table As String Private m_primaryKeys As String Private m_stageVar As String Private m_command As DbCommand Private m_mergeSql As String Private m_dropStageSql As String #End Region ' Attributes End Class ' ScriptMain ]]> {A860303F-1F3F-4691-B57E-529FC101A107};{F184B08F-C81C-45F6-A57F-5ABD9991F28F} Debug AnyCPU Library ScriptComponent_deb7430b070141d599c158b313c0b965.vbproj ScriptComponent_deb7430b070141d599c158b313c0b965.vbproj On Binary Off On {DCA77E73-AEEF-4FD5-9EFA-C57C15A1A4BD} true true true false bin\ false false 42016,42017,42018,42019,42032 false true false true bin\ false false 42016,42017,42018,42019,42032 Code VbMyResourcesResXFileCodeGenerator Resources.Designer.vb My.Resources True True Resources.resx Code SettingsSingleFileGenerator Settings.Designer.vb True Settings.settings Code SSIS_ScriptComponent ]]> CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2008, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea ScriptComponent_deb7430b070141d599c158b313c0b965 VisualBasic