Just want to get some suggestions on which tools or leverage OOB tools for data movement to CDS on daily basis as scheduled tasks.
The requirement is there are multiple excel sheets in one drive locations and they have to be imported to CDS , its not direct import of data its basically 50 k records of which there can be new records also there are scemarios which we need to compare the whole set of records in existing CDS entity to the excel data and see if the same records exists or not if not delete the record from CDS.
I am able to achieve this using SSIS but I am also looking for other options as well in Azure. Power Automate is not fitting into this requirement as there are many comparisons and its time consuming. Other option I have checked is using data flows but as per my knowledge its not for ETL jobs.
The newish out-of-the-box approach with low-code/no-code is with Dataflows. It allows you to do the whole ETL and schedule the runs. Here's the Microsoft documentation on Dataflows which would be a good start: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows
Hope this helps!
Not sure if this works as we need to load from multiple locations and CDS can be accessed only through web api and has a limit on 500 records. Tried Dataflow and it works better if we want to just pull data from source to destination . Transformation between multiple tables through power query in data flow didn't find it better for my scenario.
Yeah you can't have one for all locations, but can you have a Dataflow per location? Dataflow also supports APIs, and you would be able to transform multiple tables in PowerQuery. Curious what's the specific issue in PowerQuery? Otherwise the other popular cloud low-code solution would be Logic Apps which gives you a bit more flexibility than Power Automate.
Thanks for the response, My scenario requires all the data to be pulled from CDS this is not possinle with web api request as it allows only 5000 records for each request. Logic app also will not serve the purpose as we cannot load the data into a temporary location for faster processing, which will help only helps in iterating one by records etc. Which will be a time consuming job.
Thanks for clarifying. Since your pulling from CDS and Logic Apps/Power Automate wouldn’t be a viable option. Non-custom options are limited, you can look at the Data Export Service (https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database) but this is more to replicating data or SSIS (with optionally Kingswaysoft).
All the best!
Thanks for clarifying. Since your pulling from CDS and Logic Apps/Power Automate wouldn’t be a viable option. Non-custom options are limited, you can look at the Data Export Service (https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database) but this is more to replicating data or SSIS (with optionally Kingswaysoft)
All the best!
Yes thats correct, I have tried to do this with SSIS package but it becomes on premise mode. Which customers don't prefer trying to find an alternative. Checked with Azure Data factory but it doesn't have similar options as we have for SSIS.
Hi @SarathMohan ,
Based on the needs that you mentioned, I agree with @EricRegnier 's thought almost. I think the Data flow in PowerApps could achieve your needs.
Within the Data flow, you could consider merge multiple tables into a single one table using the "Merge Query" option in Power Query Editor:
More details about the usage of Power Query in Data flow, please check the following article:
My scenario is slightly different how we can extract more than 5k record from CDS through web api connector in Power Query. I need to load all the tables and do the transformation . Which means I need to load around max 50 k records from cds for processing.
basically processing is between three different sources Two excel sheets and existing CDS records and do transformations between the records one by one. Not sure how to achieve this.
Check out these cool Power Apps & vote on your favorite!
Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)
Check out whats happening in Power Apps
FIll out a quick form to claim your community user group member badge today!