Forum Discussion

TimoRiikonen's avatar
TimoRiikonen
Advocate V
2 years ago

How to copy large datasets with dataflows when dynamic datasources are not supported?

Hi,

 

My target is to read XML content from REST interface and store it to Bronze Datalake table on Fabric.

I failed with pipelines:

- Fabric pipelines don't support XML, only JSON, CSV, binary etc, but not XML.

- Fabric pipelines can't save content as-is without reading it. If I would manage to save XML results as files, then I would not have an issue. But REST interface doesn't store anything as a file, even binary format complained that XML is not binary.

 

Dataflows succeeded further and I managed to read the XML file with 

Xml.Tables(Web.Contents(fxGenerateQuery(DayNumber)), null, 65001)

command for development purposes.

But that doesn't work for production: for all of the data, query would take days to run and I probably it would run out of memory before completion.


So what I did next was that I parameterized the integration, so that it will transfer one day content to database.

It worked fine when I tested it, but when I saved it I got error "dynamic datasources are not supported".

I was planning to make a loop in notebook that will call the dataflow for every day that has content.

But I guess that option is out now?
Any other suggestion with pipeline or dataflow?

 

Need Help
Tutorial Requests

9 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    I would like to propose something entirely different. Store your XML in a database that understands XML  (like for example SQL Server).

     

    If you want to go with Fabric you need to convert your XML to Parquet/DeltaLake format.

  • TimoRiikonen's avatar
    TimoRiikonen
    Advocate V
    2 years ago

    Fetch XML from the system I am integrating with to my SQL database that understands XML?

    Is there a way how I can do that?

    I can convert XML to DeltaLake format if it is a file, but now it is in REST interface and I am unable to download is as a file.

      • TimoRiikonen's avatar
        TimoRiikonen
        Advocate V
        2 years ago

        I can handle XML files fine as long as I could download or import them one by one.

        I can fetch files by limiting it with the number of days.

        For one day I get on average 1,000 files.

        For 1000 days, that makes a 1,000,000 XML files.

        If I import them all into dataflow and save them all into a SQL table, I need more memory than the system has.

        I created dataflow so that it runs only one days content to database and takes as a parameter which days it will transfer. 
        But when I try to execute this dataflow and to give the day diff to todays date as a paramater I get error "dynamic datasources are not supported".

  • TimoRiikonen's avatar
    TimoRiikonen
    Advocate V
    2 years ago

    The only connection I have to the external data source is a REST API.

    How do I store XMLs from the REST API to the SQL server?

     

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Use an industry grade ETL tool, or SSIS, or PowerShell.

      • TimoRiikonen's avatar
        TimoRiikonen
        Advocate V
        2 years ago

        I disagree.

        SSIS is several generation older version of Fabric and Powershell is not any ETL tool.

        I tried to submit this as an idea https://ideas.fabric.microsoft.com/ideas/create-new-idea/:

        Hello,

         

        My problem is that I have too much data for dataflow to handle.

        And my other problem is that you don't let me program a solution that let's me to use dataflows despite of that.

         

        I want to read XML files from a REST API.

        But there is about a million of files that I need to read.

        I can divide the list of file to read with a common date parameter: I read files for a single day only, store them to (file or) database and then read again the previous day starting from today backwards.

        So I programmed the dataflow so that I have a parameter which defines which days content I fetch.

        So far everything works well.

         

        But next task is either:

        • Make a loop inside dataflow in where the memory will be reused, or
        • Make a loo from pipeline --> notebook --> dataflow

         

        I don't know how to make the first one, so I tried the second one, but found out that you are not letting me to do this.

        When I publish my dataflow, I get error: "dynamic datasources are not supported" (thanks for giving the error at this point already and not on execution)

         

        So my suggestion is one of the following:

        • Remove this security setup. There are valid reasons why we need to query dynamic data sources. The fact that dataflow works fine until publishing indicates that this is probably not an architecture limitation, but security decision, which while understandable is in reality preventing us using Fabric.
        • Create a (complex) mechanism to allow notebooks to call dataflows bypassing this rule.
        • Do both of the above. Do the first one ASAP as short term solution and second one when it fits in the development queue.

         

        More information on my case is here:

        https://community.fabric.microsoft.com/t5/Power-Query/How-to-copy-large-datasets-with-dataflows-when-dynamic/m-p/3862112#M127331

         

        But I always get an error The request is blocked. and Service unavailable.