Power automate flow - data gateway and oracle data import on D365 on Azure cloud

(0) Share

Report

Posted on by SivaR

i tried to use power automate flow to schedule the flow to bring data from oracle database on premise to D365 on Azure cloud.

Just for testing purpose:

i installed data gateway on my laptop which is connected to network.

then created flow with oracle database: which ask me to enter server: PORT/SID and username and password.

i entered required information and click create. i received an error. please refer the attached image.

Could you please advise me what i am missing here?

Oracle is installed on Linux server. However i connect Oracle database using SQL developer using oracle connection information from my laptop.

if this is the case, can i install data gateway on my laptop and connect to oracle from D365 on Azure cloud using the data gateway?

Please let me know how i can create a flow to import data from Oracle to D365

Is there any other way i can schedule a process to import data from Oracle to Dynamics 365?

thank you,

Siva

I have the same question (0)

All responses (8)

Answers (1)

Elijah Stownton 192 on at

Like (0)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

As an alternative, you can use a 3rd party tool to automate data import from Oracle to Dynamics 365. Try Skyvia - it is a no-code data integration product with a freemium model.

Was this reply helpful? Yes No
cloflyMao 25,208 on at

Like (0)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

Hi Siva,

It is very glad that your problem has been solved.

Regards,

Clofly

Was this reply helpful? Yes No
SivaR 63 on at

Like (1)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

Hi Clofly,

please ignore the above issue.

All your suggest helped me to successfully import the data.

i was able to create and update by adding condion action.

Also i created new field (sync date) as part of the contact entity. when new record is created, i update that sync date field with current date.

then each time i will check if the modified date is greater than sync date, then only update other wise ignore.

this helped me to create and update if that required.

Thank you so much

Was this reply helpful? Yes No
SivaR 63 on at

Like (0)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

Thank you Clofly.
I created a simple flow to retrieve data from Oracle and create records into Dynamics 365 which is working.
Steps:
1. "Get rows" action to retrieve data from oracle
2. "create a new record" action to insert data to D365

Then I created another flow to test update and create data:
Step 1: "Get rows" action to retrieve email from oracle table
Step 2: "List records" action to retrieve existing email from D365 (i passed the email coming from step 1)
Step 3: I tried to add a condition with emails received from steps 1 and 2. However, i could not access the email coming from step 2.
I am not sure how to get the email coming from step 2. i tried to use an expression "items('Apply to each')['Email']" and received the following error
"Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Apply to each' referenced by 'inputs' in action 'Condition' are not defined in the template.'.'."
Please advise me how to get the email from step 2. (refer to image 2, 3, and 4)

When the flow 2 was not working, I created a third flow slightly different from the second flow to see if i can make that works. But that is also not working. (pl refer to image 5, 6 and 7)

FlowError.docx

Was this reply helpful? Yes No
Verified answer

cloflyMao 25,208 on at

Like (1)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

Hi Siva,

1. When I run the flow it is only picked up and created 512 records in Dynamics 365:

We can allow more than the default 512 records to be retrieved in "List records" settings, and switch Pagination to on.

https://community.dynamics.com/crm/b/mrdavesdavidyackdynamicscrmblog/posts/using-flow-cds-list-records-action-with-gt-512-records

https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/CDS-List-Record-action-limitations-Power-Automate/ba-p/487478

(The maximum limit could be only set to 5000 if your license is Office 365 and free licenses, check here for limitation.)

2. Is there any common data service function available to take care of the update and create (createORupdate function instead of "create a record")

Currently there is no createORupdate function in Common Data Service connectors.

https://docs.microsoft.com/en-us/connectors/commondataservice/#actions

3. For error you are getting:

Are you querying for records whose emailaddress field is not empty?

Try to add filter in "Get rows" action directly.

https://docs.microsoft.com/en-us/connectors/oracle/#get-rows

$filter, ODATA filter query.

4. Is it possible to get delta data and insert or update those records only each time run the flow?

Is question 4 same to question 2?

Regards,

Clofly

Was this reply helpful? Yes No
SivaR 63 on at

Like (0)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

thank you Clofly,

I made this work.

I did the following steps:

I installed Oracle client www.oracle.com/.../odac-downloads.html on Windows 2016 server

then installed .Net 4.7.2 version

then installed data gateway using the link from this site: docs.microsoft.com/.../service-gateway-install

then i was able to see the data gateway as part of power automate. using the gateway i created a flow in Dynamics 365 on Azure cloud to bring data from Oracle to D365.

However, we have 3680 records available.

when i run the flow it is only picked up and created 512 records in Dynamics 365.

I am getting an error as "Create_a_new_record_error_2" failed. Please refer the attached document for more details.

I wanted to update the record. after retrieving data from oracle, i added a condition to check if each record already exists in D365. if not, it will create otherwise update. Somehow my condition for update record is not working. please give your suggestion about what i am missing in the condition. (refer to the screen on the attached document)

Is it possible to get delta data and insert or update those records only each time run the flow?

is there any common data service function available to take care of the update and create (createORupdate function instead of "create a record")

Please let me know how I can import all my data (3680) without failing.

Thank you so much

Flow_5F00_Failed-to-complete-the-process.docx

Was this reply helpful? Yes No
Suggested answer

protc on at

Like (0)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

HI Siva,

Thank you for your query.

It is also recommended that you install the Gateway directly on the Database server locally.

docs.microsoft.com/.../gateway-reference

Was this reply helpful? Yes No
cloflyMao 25,208 on at

Like (0)

Report

RE: Power automate flow - data gateway and oracle data import on D365 on Azure cloud

Hi Siva,

You could check your Oracle client version and installed .NET Framework version.

https://community.powerbi.com/t5/Desktop/NET-Framework-Data-Provider-configuration-issue-with-machine/m-p/598260#

https://community.oracle.com/thread/2571139?start=0&tstart=0

Regards,

Clofly

Was this reply helpful? Yes No