Using OIC3 To Integrate SQL Server Data

By -

 

🧭 Overview

We have multiple SQL Server database running on premises and in Microsoft Azure. We also have applications and services using files and REST APIs to expose application data. We have an exiting on-premises message integration solution that is primarily XML based.

We now have a Oracle cloud based solution which uses Oracle Integration Cloud version 3 (OIC3) to import and export data. We would like to leverage our investment in Oracle to integrate our data but this needs to include our existing on-premises and Azure based SQL Server databases.

📌 What This Post Covers

  • ✅ Configuring an on-premises SQL Server connection from OIC3
  • ✅ Configuring an Azure SQL Server connection from OIC3
  • ✅ Utilizing those connections to read and write data
  • ✅ Managing data using the XML datatype in SQL Server

⚠️ Configuring an on-premises Oracle agent is out of scope for this post. The process for setting up an agent will be covered in its own post

🧱 High-Level Approach

In this solution we will get data from SQL Server on-premise using a T-SQL query which return columns including text, numbers, dates and XML datatypes. We will then write that data to an Azure SQL Server database. We will use both T-SQL queries and stored procedures to manipulate the SQL Server data.

This solution will be suitable for a message based pattern, getting single records from the database, iterating through unprocessed records to one-by-one.

⚠️ To keep this post simple we will use a simple schedule to trigger the integration. Triggering based on data in SQL Server will be covered in its own blog post.

🛠️ Technical Solution

Step 1 of 7: Create A Connection To An On-Premises SQL Server

Create a connection in OIC and choose the adapter called Microsoft SQL Server from the list.


Give the connection a name and identifier and set the role to Trigger and invoke and click the Create button


Enter the host name for your SQL Server


Expand the Optional properties section and fill in the Database Name and any other details that apply to your environment


Enter a valid username and password which can be delegated by the on-premises OIC agent (for example, a SQL Server based login)


Choose the only Access type available which will be Connectivity agent and then use the Associate agent group button to choose an on-premises agent


Select an agent for your environment and then click the Use button


Test and Save your connection

Step 2 of 7: Create A Connection To An Azure SQL Server

Create a connection in OIC and choose the adapter called Microsoft SQL Server from the list.



Give the connection a name and identifier and set the role to Trigger and invoke and click the Create button


In the Host field enter your Azure SQL Server host name, usually suffixed with .database.windows.net

In the Optional properties section enter the database name suffixed with ;CryptoProtocolVersion=TLSv1.2; and then ensure Encrypt Connection is set to Yes and Validate Server Certificate is set to No


Enter a valid username and password which can be delegated by the on-premises OIC agent (for example, a SQL Server based login)


This seems counter-intuitive but you must choose the only Access type available which will be Connectivity agent and then use the Associate agent group button to choose an on-premises agent


Select an agent for your environment and then click the Use button


Test and Save your connection

⚠️ Yes, you have read that correctly, OIC3 uses the on-premises agent to access Azure SQL databases using exactly the same connectivity as an on-premises database!

Step 3 of 7: Execute A Query To Find Out How Many Records We Need To Process

Create a new OIC integration based on a schedule and then add a connection action based on your source SQL Server connection


Give your SQL Server invoke a name and description, set the Operation to Run a SQL Statement and then click contine


Enter a T-SQL statement and click Validate before clicking Continue


Step 4 of 7: Create An Iteration Loop

By Assigning two variables, one called RecordCount and one called RecordIterator we can use a While block to iterate over our records executing the next steps for each for each unprocessed record in our source table.

You can access the RecordCount value returned by the quest using the following syntax:

$Get_Record_Count/db:Get_Record_CountOutput/db:RecordCount

🧠 You could also achieve a similar outcome using by assigning a State variable based on the record count with the While iterating until the state reaches No Rows. This would allow you to repeatedly execute the count query to make the looping more dynamic.

Step 5 of 7: Execute A Query To Get A Single Record

To get a single record from the source table follow the same process as Step 3 but use a query such as:

SELECT TOP 1
  [MessageID],
  [MessageDateTime],
  [MessageType],
  [Sender],
  [Receiver],
  [MessageContent]
FROM
  [dbo].[OutboundRecords]
WHERE
  [Processed] = 0

Using the TOP 1 clause ensures we only receive one record to process at a time.

When mapping the data sent from SQL Server, column based nodes can be mapped directly like this:


However the XML based column [MessageContent] must be mapped using a function called parseEscapedXML() as follows:

oraext:parseEscapedXML ( /nssrcmpr:execute/ns21:request-wrapper/ns21:MessageContent )/*[local-name()='ApplicationArea']/*[local-name()='CorrelationID']

In this code the ns21: is map specific so should be changed to match your own maps prefix.

The vitally important part of this syntax is the XPath attached immediately after the function which must be character perfect.

⚠️ In this example our column [MessageContent] is an XML datatype but will be converted to the VARCHAR datatype by the connector

Step 6 of 7: Execute A Stored Procedure To Write A Single Record

Invoking a stored procedure is very similar to executing a T-SQL statement with the exception that the Operation must be Invoke a Stored Procedure


You then need to select your Schema (in SQL Server this is the database), Package (SQL Server schema / role) and Stored Procedure to execute.

Use the preceding map to set the stored procedure parameter mapping and the subsequent map to receive the response.

⚠️ The OIC3 user interface for selecting Schema, Package and Procedure seems to be broken in some browsers which means the drop down lists stay dropped down once a value has been selected. While this is a bit "clunky" it doesn't cause any problems completing the form.

Step 7 of 7: Execute A Query To Update The Record

Executing a T-SQL query to update a record is the same process as step 3. However our solution needs to update a specific record.

We use this syntax to parameterize our query:

UPDATE
  [dbo].[OutboundRecords]
SET
  [Processed] = 0
WHERE
  [MessageID] = #MessageID

This syntax exposes the parameter #MessageID to the preceding map, allowing you to map it to the value of MessageID from our get in step 5.

❌ Dead Ends & Gotchas

❌ Selecting The XML DataType

  • When dealing with XML based SQL Server columns it would seem reasonable to think the OIC3 mapper would render the XML structure of the data

  • Unfortunately the OIC3 SQL Connector returns the XML datatype as VARCHAR so if you require a structured view of the XML data you will need to manipulate the data in the T-SQL query to return a recordset

❌ Working With parseXML And parseEscapedXML Functions

  • The XPath after the function must start with the /* characters with no whitespace after the function ) close-backet. Any deviation from this will result in no data being mapped. It's also worth noting the use of the local-name syntax to handle XML namespaces within the data sent from SQL Server to avoid clashes with OICs own namespaces.

🔍 Next Steps

There are improvements we can make to this solution which will be covered in later blog posts:

  • Decoupling the reader and writer OIC3 integration components using Events
  • Triggering the process by polling for data changes in SQL Server

✅ Conclusion

  • In this solution we created a simple, scheduled based integration which iterates records in an on-premises SQL Server database table and writes the data to an Azure SQL Server table
  • The solution uses both T-SQL queries and stored procedures to achieve its objective
  • We can improve this solution by splitting the integration in two with one integration responsible for getting the data and another responsible for writing, linking them together using an event mechanism

📚 References & Resources



Comments

Post a Comment

Popular posts from this blog

MSDTC in Windows Core

By BG -
Simple one this... How can we enable inbound transactions in MSDTC on a core machine? Well we use PowerShell of course! :-) PS C:\>Set-DtcNetworkSetting -InboundTransactionsEnabled $True Nice one.
Read more

The Dreaded 403 PowerShell Remoting Error

By BG -
Image
If you have worked with PowerShell remoting then you will have seen this error before: : Connecting to remote server BLAH failed with the following error message : The WinRM client received an HTTP status code of 403 from the remote WS-Management service. For more information... It is not a happy message, especially when you have been using PowerShell to remotely manage the particular server for ages! So then you try remoting from another client and it works! You go back to your original client and try remoting to anything else and it fails...dohh! "But this worked just yesturday!" you scream. Ahh, little things can make a big difference and in my case the issue was related to a VSTS Agent update that I did the day before. In order for the new version of the agent to communicate with VSTS in the cloud I needed to set a WinHTTP proxy. Once the agent was configured I could use a .proxy file in the agent directory instead...but I forgot to remove the WinHTTP proxy and in s...
Read more

Biztalk vs OIC

By BG -
Image
 So we are developing integration solutions using Biztalk 2016 and recently we started working with Oracle's Integration Cloud iPaaS service. We have found it quite painful and limiting and until this week have been singing the praises of Biztalk, saying that it's just far easier to work with and more functionally rich. Well... This week I had what seemed a simple task. Take an OAGIS BODFailureMessage (OAGIS is awesome) and send the details to Service Desk Plus, then put the resultant Request ID back into the original message and send it onwards. Seemed pretty straightforward. Problem #1 Service Desk Plus has an API that enables us to POST a Request BUT the API does not accept a message-body; it wants you to send the details of the request (which is a JSON payload) as a parameter called input_data.  Now from Postman, this is quite easy. Simply add a key-value pair in the Body using the x-www-form-urlencoded option like so: However, when it comes to doing the same thing in Bi...
Read more