Microsoft Flow has made Business Process Automation OR Integrations between systems very easy. Variety of connectors, lets us connect to different data sources that lets us pull the data and send it to target systems. You can really leverage your Office 365 investment to solve day to day business problems and become a power user.
Almost all organizations have a centralized Data Ware house to store different types of data like Operational, Financial, Legal etc. Organizations do use some strategic ETL\ELT tools like ODI , SSIS OR other Service bus tools like APIGee, Mulesoft etc., for Integration needs. Most of these tools do have connectors to connect all sources. Only ETL tool that comes with a native SharePoint Online connector is SSIS. For any other tools, one has to use SharePoint REST APIs to read SharePoint data and In order to pass OAuth, one must define a new App with SharePoint site to generate Client Id\Client Secret for secure communication.
But, what if you do not use SSIS as a stratigic tool but instead use Oracle tool? OR What if you do not have any such tool defined and you would like to Integrate SharePoint List data to On Premise Oracle database table periodically for automating a business process?
In this blog, we will see how we can leverage Microsoft Flow to Integrate SharePoint Online List Data to On Premise Oracle Table using #MicrosoftFlow.
One of the great tool that Microsoft came up with is OnPremise Gateway. This tool lets you connect to On Premise data sources from Online workloads.
Install On Premise Gateway from following link..
Once downloaded, configure it by logging in with your Network credentials and it should be ready to serve the needs. You should see something like below…
Cool. Now you have gateway setup. Next Step is to install latest Oracle Data Access Components from Oracle downloads. Please note that Supported Oracle Version is 9 and above. In my case I have installed 12 C ODAC. Remember that it should be 64 bit download of ODAC component.
Then create a new Oracle Table to sync data. You can call it as a staging table in Oracle Schema from where you can sync data to your actual tables.
For the Purpose of this demo I have created a table called EmployeeData with a single column EmployeeName. Currently its empty.
We are going to sync the data from SharePoint List which is below…
Now we can start building our Flow.
- Create a new Flow using Blank template.
- Since we will need data sync on periodic basis, we are going to use Scheduler as a trigger. Add a new Trigger ‘Recurrence’ and set it to run based on your schedule. For the purpose of this demo, I am setting it to run every 15 mins.
- Now Add a new Action to get data from SharePoint Site. I have a SharePoint Site with a simple list called EmployeeData where I store Employee Names. Connector looks something like below after complete configuration
- Next, We will have to loop through all rows and insert it into Oracle table. We need two actions here. 1. Apply for each 2. Insert Row [Oracle DB]. Please note that Insert Row to Oracle is a premium connector and you may need to pay. Configure Oracle Action as below…
- And Select your on premise gateway name that you created when you installed on your machine\server. As you can see, I have selected my gateway. All connections are saved in your Connections within Office 365 which you can use in any workloads like Power Apps, Power BI OR Flow OR Logic Apps.
- Connector now should look like below. If all connections setup correctly, Table Name should show you all objects from On Premise Oracle Schema. Select your table name. I have selected EmployeeData.
- All Set. Now Run the flow to test the outcome. Flow should run successfully and you should see magic. Oracle Table should be populated with data…
You can add Oracle Delete Row Action to delete all rows and then Insert. Other Option is to run a Oracle Package Procure which accepts Parameters.
Please remember to check your Flow License and number of Flow Runs included within the license. You can schedule to run the flow based on the threshold.
On Premise Gateway can also be leveraged to develop a Power App which inserts data to On Premise databases. This is really a great choice for simple apps which can be developed as a No Code Solution.
Happy Flowing.. 🙂
Prasad Athalye 