Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Customer Insights - Journeys forum / Power automate flow - ...
Customer Insights - Journeys forum
Answered

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

(0) ShareShare
ReportReport
Posted on by SivaR 459

Hi 

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

oracleError.GIF

  • Elijah Stownton Profile Picture
    Elijah Stownton 190 on at
    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.

  • cloflyMao Profile Picture
    cloflyMao 25,194 on at
    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

  • SivaR Profile Picture
    SivaR 459 on at
    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 

  • SivaR Profile Picture
    SivaR 459 on at
    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

  • Verified answer
    cloflyMao Profile Picture
    cloflyMao 25,194 on at
    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?

    pastedimage1596184373683v1.png

    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

  • SivaR Profile Picture
    SivaR 459 on at
    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

  • Suggested answer
    protc Profile Picture
    protc on at
    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

  • cloflyMao Profile Picture
    cloflyMao 25,194 on at
    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

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Community Spotlight for September 2024

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,339 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,177 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans