]]> _
_
_
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 IDTSVariables90
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 IDTSVariables90
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
]]>ScriptComponent_2e3d4c56d0694f9bbd032f1e0950bec1trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea