Importing Massive Amount of Data from Excel Into DataVerse

[AbuMaze]

Hi,

I have a requirement to import 100K - 1M records from Excel into DataVerse.  The Excel file will be uploaded as an attachment to a custom entity in the portal.  I built a Power Automate to process this; however, when I attempted to test this in my trial environment, I ran up against the throttling that Microsoft put up.  Even in an environment with a per user license, I will be throttled.

Is there a cleaner and faster way of doing this? 

[StalinPonnusamy]

Hi @AbuMaze 

As you mentioned, the Trail environment is not designed to upload a large volume of data since resources are shared among other environments (s).  Microsoft specifically built this policy to make sure all environment utilizes services equally. To verify the complete process, we can limit the data to upload. And then use your sandbox to load all data.

And also, in my opinion, Power Automate is designed to process large volumes of data. We use SSIS with Kingswaysoft to transfer or migrate or integrate large volumes of data.

[AbuMaze]

@StalinPonnusamy, thank you for your reply.  Did you mean Power Automate is or is not meant for large volumes of data?

How would you go about triggering an integration of an uploaded Excel file from the portal to a Notes attachment?

[StalinPonnusamy]

Hi @AbuMaze 

 Power Automate is not meant for large volumes of data. And also not possible to read attachment from Dataverse. Using power automate we need to process batch by natch since the maximum count is 5000 for a single batch. 

[AbuMaze]

@StalinPonnusamy, thank you again for your reply.

With Power Automate, I triggered my flow to when a Note is created with an Excel attachment.  I then copy that file into SharePoint and access the file to loop through the rows to insert corresponding records.

You mentioned KingswaySoft and SSIS.  I am wondering if there is a way to execute that SSIS package when a file is arrives in the SharePoint site.

[StalinPonnusamy]

Hi @AbuMaze 

Not exactly. Please review their site and ask any questions. They are really good at responding to any queries.

https://www.kingswaysoft.com/solutions/ssis-premium-data-flow-components/premium-excel-components

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-sharepoint

[AbuMaze]

@StalinPonnusamy , Thank you so much for all of your help and insight.  I really appreciate it.  I will contact them.

[StalinPonnusamy]

Hi @AbuMaze 

Please let us know if anything needs on your post. We can help with this.

Please do not forget to give kudos if you find the suggestion helpful or Accept it as a solution if works fine to help other users to find it useful.

[takolota]

There is now a template set up for doing Dataverse batch requests in Power Automate: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Dataverse-Batch-Update-Create-and-Upsert...

This could possibly handle 100s of thousands of records given it takes about 6 actions per 1000 Dataverse records created.