Execute Visual Basic code on source table in memory

The step of data transfer of a configuration always begins with reading data from the source table. At this time a number - specified as Maximum number of records in memory on page Performance, or less - of records are loaded into a DataTable object called sourceData in the memory. We are allowed to perform actions on this datatable using Visual Basic code before its contents are added to the target table(s).

code_on_source_data

An example

The source table includes the column Index we want to copy to another table while replacing negative values with their respective absolute values.

This can be accomplished by checking Execute VB code on source table in memory on page Enabled actions and type the following into the input field of page VB code on source data (4):

Dim i As Integer

For i = 0 To sourceData.Rows.Count - 1

If sourceData.Rows(i).Item("Index") < 0

sourceData.Rows(i).Item("Index") *= -1

End If

Obviously no changes are made to the source table, this code only effects data in the memory and, as a result, data inserted into the target database.

Use of methods defined on page Own VB functions is permitted here. Click the button below the input field to verify your code syntactically.

Some predefined constants are available from this code containing values from text boxes on page Configuration groups of the simple view (which in turn are parameters of the source and target connection strings and the otherwise unused Additional parameters), these are:

ReadOnly sourceServerName As String

' Source -> Server

ReadOnly sourceDatabase As String

' Source -> Database

ReadOnly sourceUsername As String

' Source -> Username

ReadOnly sourcePassword As String

' Source -> Password

ReadOnly sourceAdditionalParameter As String

' Source -> Additional parameter

ReadOnly targetServerName As String

' Target -> Server

ReadOnly targetDatabase As String

' Target -> Database

ReadOnly targetUsername As String

' Target -> Username

ReadOnly targetPassword As String

' Target -> Password

ReadOnly targetAdditionalParameter As String

' Target -> Additional parameter

Three other constants are also available and four predefined objects with specific roles:

ReadOnly groupName As String

' Name of the group containing this configuration

ReadOnly configurationName As String

' Name of this configuration

ReadOnly groupsDirectory As String

' Directory in which configuration groups of the plugin (.group files) are located

sourceData As DataTable

' Table containing a number of rows of loaded source data. Its structure is identical to

' that of the source table

schemaTable As DataTable

' Table describing the structure of the source table returned by .NET call

' ODBCConnection.GetSchema()

auxiliaryTables As Dictionary(Of String, DataTable)

' Object containing auxiliary tables' names and the tables themselves. This includes

' virtual tables (virtualTable1 - virtualTable8)

log As List(Of String)

' The list log allows you to create your own log entries: lines in it will be added to the

' plugin's event log once this code is executed

Warning: configurations are executed by the scheduler of the plugin, thus code provided here is treated as if it belonged to a Windows service and is usually not capable of interactive actions such as displaying dialog boxes.