_
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 Oracle() As IDTSConnectionManager100
Get
Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection("Oracle").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
Private Const OracleTypeProperty As String = "OracleType"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim colsCount As Integer = m_inputIndexes.Length
For colIndex As Integer = 0 To colsCount - 1
Dim bufIndex As Integer = m_inputIndexes(colIndex)
Dim param As DbParameter = m_command.Parameters.Item(colIndex)
Dim value As Object = Nothing
If Not Row.Buffer.IsNull(bufIndex) Then
value = Row.Buffer(bufIndex)
Select Case param.DbType
Case DbType.Object
' Blob columns need special handling.
value = GetOracleBlob_(CType(value, BlobColumn))
End Select
End If
Dim buffer() As Object = CType(param.Value, Object())
buffer(m_currentRow) = value
Next
m_currentRow += 1
If m_currentRow = Me.BatchSize Then
' Time to send current batch of data.
Try
Call m_command.ExecuteNonQuery()
Catch ex As Exception
Call FireError_(ex.Message)
End Try
m_currentRow = 0
End If
End Sub ' Input_ProcessInputRow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0)
Dim colsCount As Integer = input.InputColumnCollection.Count
' Setup dynamic input columns indexes.
m_inputIndexes = New Integer(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
m_inputIndexes(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(colIndex).LineageID)
Next
' Setup Oracle command.
Call m_command.GetType().InvokeMember( _
"BindByName", _
BindingFlags.SetProperty Or BindingFlags.Public Or BindingFlags.Instance, _
Nothing, _
m_command, _
New Object() {True})
Call m_command.GetType().InvokeMember( _
"ArrayBindCount", _
BindingFlags.SetProperty Or BindingFlags.Public Or BindingFlags.Instance, _
Nothing, _
m_command, _
New Object() {Me.BatchSize})
m_command.CommandTimeout = Me.Timeout
m_command.CommandType = CommandType.Text
' Setup command parameters.
Dim colsList As String = String.Empty
Dim paramsList As String = String.Empty
For colIndex As Integer = 0 To colsCount - 1
Dim extCol As IDTSExternalMetadataColumn100 = input.ExternalMetadataColumnCollection.GetObjectByID( _
input.InputColumnCollection(colIndex).ExternalMetadataColumnID)
If Not String.IsNullOrEmpty(colsList) Then
colsList += ", "
paramsList += ", "
End If
colsList += String.Format("""{0}""", extCol.Name)
paramsList += String.Format(":{0}", extCol.Name)
Dim parameter As DbParameter = m_command.CreateParameter()
parameter.ParameterName = extCol.Name
Dim paramType As Type = parameter.GetType()
Call paramType.InvokeMember( _
"OracleDbType", _
BindingFlags.SetProperty Or BindingFlags.Public Or BindingFlags.Instance, _
Nothing, _
parameter, _
New Object() {System.Enum.Parse( _
paramType.GetProperty("OracleDbType").PropertyType, _
extCol.CustomPropertyCollection(OracleTypeProperty).Value.ToString())})
parameter.Value = New Object(Me.BatchSize) {}
parameter.Direction = ParameterDirection.Input
Call m_command.Parameters.Add(parameter)
Next
Dim destTable As String = Me.Table
If Me.IsTableVariable Then
' Destination table is contained in variable.
destTable = CStr(GetVariable_(Me.TableVariable))
End If
If String.IsNullOrEmpty(destTable) Then
Throw New Exception("Destination table not specified. Check package protection level.")
End If
' Setup command statement.
m_command.CommandText = String.Format( _
"INSERT INTO {0} ( {1} ) VALUES ( {2} )", _
destTable, _
colsList, _
paramsList)
m_currentRow = 0
' Setup OracleClob constructor.
Dim clobType As Type = Type.GetType( _
"Oracle.DataAccess.Types.OracleClob, " + _
m_command.Connection.GetType().Assembly.FullName)
m_clobCI = clobType.GetConstructor(New Type() {m_command.Connection.GetType()})
' Setup OracleBlob constructor.
Dim blobType As Type = Type.GetType( _
"Oracle.DataAccess.Types.OracleBlob, " + _
m_command.Connection.GetType().Assembly.FullName)
m_blobCI = blobType.GetConstructor(New Type() {Connection.GetType()})
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PostExecute()
Call MyBase.PostExecute()
If m_currentRow > 0 Then
Call m_command.GetType().InvokeMember( _
"ArrayBindCount", _
BindingFlags.SetProperty Or BindingFlags.Public Or BindingFlags.Instance, _
Nothing, _
m_command, _
New Object() {m_currentRow})
Call m_command.ExecuteNonQuery()
m_currentRow = 0
End If
End Sub ' PostExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Call MyBase.AcquireConnections(Transaction)
' Setup command.
Dim conn As DbConnection = CType( _
Me.Connections.Oracle.AcquireConnection(Transaction), _
DbConnection)
m_command = conn.CreateCommand()
End Sub ' AcquireConnections
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub ReleaseConnections()
Call MyBase.ReleaseConnections()
If Not m_command Is Nothing Then
' Dispose command and connection.
Dim conn As DbConnection = m_command.Connection
Call m_command.Dispose()
Call conn.Dispose()
m_command = Nothing
End If
End Sub ' ReleaseConnections
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of current properties based on current state.
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("BatchSize")
Call result.Add("Timeout")
Return CType(result.ToArray(GetType(String)), String())
End Function ' GetProperties
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Validate(ByRef errMessage As String) As Boolean
Dim result As Boolean = True
Try
If String.IsNullOrEmpty(Me.Connection) Then
Throw New Exception("Select connection.")
End If
If String.IsNullOrEmpty(Me.Table) Then
Throw New Exception("Select destination table.")
End If
Dim input As IDTSInput100 = Me.ComponentMetaData.InputCollection(0)
If input.IsAttached AndAlso input.InputColumnCollection.Count = 0 Then
Throw New Exception("Map input columns.")
End If
Me.ComponentMetaData.RuntimeConnectionCollection("Oracle").ConnectionManagerID = Me.Connection
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.Connection) Then
Throw New Exception("Connection not selected.")
End If
If String.IsNullOrEmpty(Me.Table) Then
Throw New Exception("Destination table not selected.")
End If
Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0)
' Get table schema.
Dim tableSchema As DataTable
Using connection As DbConnection = CType( _
Me.DesignConnections(Me.Connection).AcquireConnection(Nothing), _
DbConnection), _
command As DbCommand = connection.CreateCommand()
command.CommandType = CommandType.TableDirect
' Setup destination table.
If Me.IsTableVariable Then
command.CommandText = CStr(GetVariable_(Me.TableVariable))
Else
command.CommandText = Me.Table
End If
tableSchema = command.ExecuteReader(CommandBehavior.SchemaOnly).GetSchemaTable()
End Using
' Cleanup.
input.ExternalMetadataColumnCollection.IsUsed = True
Call input.InputColumnCollection.RemoveAll()
Call input.ExternalMetadataColumnCollection.RemoveAll()
' Setup column metadata.
Dim colsCount As Integer = tableSchema.Rows.Count
For colIndex As Integer = 0 To colsCount - 1
Dim columnRow As DataRow = tableSchema.Rows(colIndex)
Dim column As IDTSExternalMetadataColumn100 = input.ExternalMetadataColumnCollection.NewAt( _
colIndex)
column.Name = CStr(columnRow("ColumnName"))
' Get type.
Dim rawType As Type = CType(columnRow("DataType"), Type)
Dim columnType As DataType = DataRecordTypeToBufferType_(rawType)
If CBool(columnRow("IsLong")) Then
' This is extended type. Have to modify the column type a bit.
Select Case columnType
Case DataType.DT_WSTR
columnType = DataType.DT_NTEXT
Case DataType.DT_STR
columnType = DataType.DT_TEXT
Case DataType.DT_BYTES
columnType = DataType.DT_IMAGE
End Select
End If
' Get length.
Dim length As Integer = 0
Select Case columnType
Case DataType.DT_WSTR, DataType.DT_STR, DataType.DT_BYTES, DataType.DT_IMAGE, DataType.DT_NTEXT, DataType.DT_TEXT
length = CInt(columnRow("ColumnSize"))
End Select
' Get code page.
Dim codepage As Integer = 0
If columnType = DataType.DT_STR Then
codepage = -1
End If
' Get precision and scale.
Dim precision As Integer = 0
Dim scale As Integer = 0
If columnType = DataType.DT_NUMERIC OrElse columnType = DataType.DT_BYREF_DECIMAL Then
If columnRow.IsNull("NumericPrecision") Then
precision = 29
Else
precision = CInt(columnRow("NumericPrecision"))
End If
If columnRow.IsNull("NumericScale") Then
scale = 0
Else
scale = CInt(columnRow("NumericScale"))
End If
If scale = 255 Then
scale = 0
End If
End If
column.DataType = columnType
column.Length = length
column.Precision = precision
column.Scale = scale
column.CodePage = codepage
' Include Oracle type as property.
Dim propType As IDTSCustomProperty100 = column.CustomPropertyCollection.[New]()
propType.Name = OracleTypeProperty
propType.Value = columnRow("ProviderType")
Next
End Sub ' ReinitializeMetaData
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of tables in the currently selected Oracle connection.
Public Function GetTables() As String()
Dim result As ArrayList = New ArrayList()
Dim connection As DbConnection = CType( _
Me.DesignConnections(Me.Connection).AcquireConnection(Nothing), _
DbConnection)
Try
Dim tables As DataTable = connection.GetSchema("Tables")
For Each table As DataRow In tables.Rows
Dim tableType As String = table(2).ToString().ToUpper()
If tableType <> "USER" Then
' Include only user tables.
End If
Dim tableName As String = String.Format( _
"""{0}"".""{1}""", _
table(0), _
table(1))
Call result.Add(tableName)
Next
Finally
Call connection.Dispose()
End Try
Return CType(result.ToArray(GetType(String)), String())
End Function ' GetTables
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Connection() As String
Get
Connection = m_connection
End Get
Set(ByVal value As String)
m_connection = value
End Set
End Property ' Connection
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property BatchSize() As Integer
Get
BatchSize = m_batchSize
End Get
Set(ByVal value As Integer)
m_batchSize = value
End Set
End Property ' BatchSize
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Timeout() As Integer
Get
Timeout = m_timeout
End Get
Set(ByVal value As Integer)
m_timeout = value
End Set
End Property ' Timeout
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
_
Public Property IsTableVariable() As Boolean
Get
IsTableVariable = m_isTableVariable
End Get
Set(ByVal value As Boolean)
If m_isTableVariable <> value Then
m_isTableVariable = 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
If Not String.IsNullOrEmpty(m_table) 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_table = value
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
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private ReadOnly Property AdoNetConnectionType() As String()
Get
AdoNetConnectionType = New String() {"ADO.NET"}
End Get
End Property ' AdoNetConnectionType
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns SSIS data type from .NET type. Uses private PipelineComponent method.
Private Function DataRecordTypeToBufferType_(ByVal type As Type) As DataType
DataRecordTypeToBufferType_ = CType( _
GetType(PipelineComponent).InvokeMember( _
"DataRecordTypeToBufferType", _
BindingFlags.InvokeMethod Or BindingFlags.NonPublic Or BindingFlags.Static, _
Nothing, _
Nothing, _
New Object() {type}), _
DataType)
End Function ' DataRecordTypeToBufferType_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function GetVariable_(ByVal varName As String) As Object
Dim result As Object
Dim vars As IDTSVariables100 = Nothing
Call MyBase.VariableDispenser.LockOneForRead(varName, vars)
Try
result = vars(varName).Value
Finally
Call vars.Unlock()
End Try
GetVariable_ = result
End Function ' GetVariable_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Creates Oracle specific BLOB out of SSIS BLOB.
Private Function GetOracleBlob_(ByVal blob As BlobColumn) As Stream
Dim result As Stream
If blob.ColumnInfo.DataType = DataType.DT_STR OrElse _
blob.ColumnInfo.DataType = DataType.DT_WSTR Then
result = CType(m_clobCI.Invoke(New Object() {m_command.Connection}), Stream)
Else
result = CType(m_blobCI.Invoke(New Object() {m_command.Connection}), Stream)
End If
Dim data() As Byte = blob.GetBlobData(0, CInt(blob.Length))
Call result.Write(data, 0, data.Length)
Call result.Flush()
GetOracleBlob_ = result
End Function ' GetOracleBlob_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub FireError_(ByVal message As String)
Dim cancel As Boolean = False
Call MyBase.ComponentMetaData.FireError( _
0, _
"Oracle Destination", _
message, _
String.Empty, _
0, _
cancel)
End Sub ' FireError_
#End Region ' Internals
#Region "Attributes"
Private m_connection As String
Private m_batchSize As Integer
Private m_timeout As Integer
Private m_isTableVariable As Boolean
Private m_table As String
Private m_command As DbCommand
Private m_inputIndexes() As Integer
Private m_currentRow As Integer
Private m_clobCI As ConstructorInfo
Private m_blobCI As ConstructorInfo
#End Region ' Attributes
End Class ' ScriptMain
]]>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}DebugAnyCPU8.0.307032.0{180CF706-38AC-4A21-BB78-9C54E410C0B2}LibraryMy ProjectScriptComponent_e8c6f2cbccf645ae862b6dbf4ea8a3d9ScriptComponent_e8c6f2cbccf645ae862b6dbf4ea8a3d9v4.0512BinaryOnOntruefullfalse.\bin\Debug\falsetruetrueprompt4falsetrue.\bin\Release\falsefalsetrueprompt4FalseC:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\CozyRoc.SSISPlus.2014.dllFalseFalseFalseFalseSSIS_SC120]]>msBuildScriptComponent_e8c6f2cbccf645ae862b6dbf4ea8a3d9ScriptComponent_e8c6f2cbccf645ae862b6dbf4ea8a3d9{39C90C2C-C8FD-44B4-A5C0-348DBD4C23B3}]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_e8c6f2cbccf645ae862b6dbf4ea8a3d9VisualBasic