How Can Data be added from On Premise SQL Server or Azure SQL Server to the Common Data Model/Servic

[vsslasd]

What methods can Data be added from On Premise SQL Server or Azure SQL Server to the Common Data Model/Service?

Is TSQL an option ?

Power Query?

SSIS?

Flow?

What are the Other Methods ?

Can triggers on SQL Tables be used to add data to the CDM?

[v-xida-msft · Accepted solution]

Hi @vsslasd,

Could you please share a bit more about your scenario?

Do you want to import data from your On-Premises SQL table/Azure SQL table into your CDS Entity? Or add the corresponding data into your CDS Entity when new data is added in your SQL Table?

If you want to import data from your On-Premises SQL table/Azure SQL table into your CDS Entity, you could use Power Query functionality in CDS of your PowerApps to achieve your needs.

More details about Power Query in PowerApps CDS, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-cds-newentity-pq

If you want to add the corresponding data into your CDS Entity when new data is added in your SQL Table, I think Microsoft Flow could achieve your needs. I have made a test on my side, please take a try with the following workaround:

More details about Microsoft FLow, please check the following article:

https://docs.microsoft.com/en-us/flow/getting-started

In addition, the triggers you specified on your SQL tables could only used within your SQL Server, it could not be used to add data to your CDS Entity.

Best regards,

Kris

[vsslasd]

Thank you. That is a big help.

Just trying to understand the various methodologies for gathering and aggregating data, and just trying to understand how to leverage our current way of doing things: Stored Procedures, Views, Tables in SQL Server/Azure and how those need to change and evolve in order to best use CDS so we can best share data across systems, minimizing the need to write, or replicate the same data to many places.

In many cases, it seems that creating a view in the source system and then using the "Data Integration" functionality in PowerApps will work (although we are getting errors), as long as this can be done on a schedule (which I understand it can), and as long as the rows with all of the data can be updated, inserted or removed to exactly match the source system (which I understand it can) also on a schedule. I'm curious to know how it determines if the record/row has changed if it doesn't have a timestamp in the original source table (some of our accounting app tables don't have a modify/create date).

I'm thinking it may use a row checksum to ascertain if the record is different?

If it can do the above, then it can replace some of our SSIS packages, which could be a good thing.

Then it's really a question of how we separate and reaggregate the data for the different purposes we need, without creating duplicate silos of the same data in multiple places, which hopefully can all be shared with PowerBI. 

Thank you for your thought on the Flow/Trigger idea. that would help us for when we event processing, which we absolutely do on occasion.