Microsoft SQL Server
Frends templates for SQL Server. SQL Server is a relational database management system, used for storing structured data.
This template process fetches new or updated rows from a table in a MicrosoftSQL database and inserts or updates the rows into a table in an Oracle database in chunks. This template process is meant to be used in conjunction with the "MicrosoftSQL to Oracle - Batch load" template, which can be used initially to load data from the MicrosoftSQL table to the Oracle table.
This template assumes that the following prerequisites are in place:
This template process queries new or updated rows from a table in a MicrosoftSQL database, and either inserts or updates the data into a table in an Oracle database. To determine which rows are new or updated, the process compares the rows' timestamp data (specified in a column, as described in the prerequisites) to either the timestamp of the previous synchronization, or a default value. If the timestamp on a row is more recent than the timestamp or the time of the previous sync, the row in question will be processed.
Once the new or updated rows have been fetched, the process builds a series of INSERT statements based on the user-specified chunk size. Afterwards, the INSERT statements are executed one at a time, inserting the data into a staging table in the Oracle database. After the insertions the staging table is merged into the destination table - this is where it is determined if each row should be inserted or updated by comparing the values of the identifier column of the staging table to the destination table. If a match is found between the staging and destination tables, the row in the destination table will be updated with the values from the staging table. If not, the row will be inserted. Finally, the staging table is cleared of data to prepare for the next synchronization and the previous synchronization timestamp is updated in the cache.
By default, this process is run once per hour, but this schedule can be changed in the schedule trigger of the process. Similarly, the default lifetime of the previous synchronization time entry in the cache (24 hours), and the default value assigned for the previous synchronization time if no value is found in the cache (current time minus 3 hours) can be changed as needed.
All handling of the timestamps is done in UTC time, using the format yyyy-MM-dd HH:mm:ss.
The process variables include:
Example source table structure in MicrosoftSQL
CREATE TABLE CONTACTS(
id NUMBER NOT NULL PRIMARY KEY,
email VARCHAR2(25),
firstname VARCHAR2(20),
lastname VARCHAR2(20),
phone VARCHAR2(15),
updated_time DATETIME
);
Example staging and destination table structure in Oracle
CREATE TABLE MY_SCHEMA.CONTACTS_STAGING(
id NUMBER NOT NULL PRIMARY KEY,
email VARCHAR2(25),
firstname VARCHAR2(20),
lastname VARCHAR2(20),
phone VARCHAR2(15),
updated_time TIMESTAMP
);
CREATE TABLE MY_SCHEMA.CONTACTS(
id NUMBER NOT NULL PRIMARY KEY,
email VARCHAR2(25),
firstname VARCHAR2(20),
lastname VARCHAR2(20),
phone VARCHAR2(15),
updated_time TIMESTAMP
);
In this example case, the process variables would be:
There is an error check after each task. If querying the MicrosoftSQL database fails, the process will throw an exception and stop. If inserting a chunk, i.e., executing an insert statement fails, the process will move on to the next chunk and append an error message into a variable that will be printed at the end of the execution. If merging the staging table into the destination table, or cleaning up the staging table fails, the process will throw an exception and stop.
This template does not handle transient errors separately. If such errors are expected, the tasks can be configured to retry execution on failure.
OracleConnectionString 🗝 | The connection string for the Oracle database. |
OracleSchema | The name of the schema under which the destination table is located in Oracle. |
OracleStagingTableName | The name of the staging table on Oracle, into which rows will be inserted before merging to the destination table. |
OracleTableName | The name of the destination table in Oracle. |
MicrosoftSqlTableName | The name of the source table in MicrosoftSQL. |
MicrosoftSqlConnectionString 🗝 | The connection string for the MicrosoftSQL database. |
TimestampColumn | The name of the column in both tables, where the value of the timestamp is stored. |
IdentifierColumn | The name of the column, in both MicrosoftSQL and Oracle, whose values should be used for identifying whether or not a row already exists in the Oracle table. This column should also be included in the ColumnNames process variable. |
ColumnNames | The names of the columns that should be handled, separated with a comma and a space. For example: firstname, lastname, title The names of the columns should be identical in both MicrosoftSQL and Oracle. |
ChunkSize | The number of rows that should be processed in one iteration. |