F&O Export to Data Lake to Azure SQL DB

Question

Hi everyone,

I am asking this question for learning purposes. I have used the Export to Data Lake features to get some tables from F&O into ADLS Gen 2. I was also exploring how to consume the data, and I found the Power BI ADLS Gen 2 connector with the Common Data Model (CDM) option. Secondly, I found the CDM Util approach.

But I would like to know that is it possible to use ADF/Synapse Pipelines to loop through all the tables inside the Tables.manifest.cdm.json file and copy them into Azure SQL DB.

Thanks in advance.

Answer 1

Hello @SatishBoddu,

I am sorry for the late reply. The Tables.manifest.cdm.json file structure is more complex structure than the example you provided. It is something like the below example.

{
  "manifestName": "Tables",
  "subManifests": [
    {
      "manifestName": "Custom",
      "definition": "Custom/Custom.manifest.cdm.json"
    },
    {
      "manifestName": "Finance",
      "definition": "Finance/Finance.manifest.cdm.json"
    },

I have been browsing Microsoft Docs, and I came across using Dataflows to extract the tables: Create Dataflow --> Choose Inline Dataset --> Common Data Model and then navigate to the nearest "manifest.cdm.json" file to the csv files of the table I want to extract.

Answer 2

Hello @Osama Ahmed,

Does the Tables.manifest.cdm.json file structure looks like this? or is it more complex structure?
Any particular documentation have you followed?

{
    "$schema": "https://schemas.microsoft.com/ado/2018/09/metadata/tables.manifest.cdm.json",
    "name": "Tables",
    "description": "Manifest file for tables exported from Dynamics 365 Finance and Operations",
    "version": "1.0.0",
    "tables": [
        {
            "name": "Table1",
            "schema": "dbo",
            "location": "/data/Table1.csv"
        },
        {
            "name": "Table2",
            "schema": "dbo",
            "location": "/data/Table2.csv"
        },
        ...
    ]
}