Azure Data Factory - Copy all tables using dynamic content in the dataset (Not ForEach)

Chris Crichton-RA 1 Reputation point
2023-01-05T23:25:01.887+00:00

Hello,

In many cases I want to copy all tables from a datasource. The dataset will show a preview of tables, but am only able to select 1 or none.

For example, In the quickbooks dataset pictured, is it possible to use dynamic content to iterate each table or does this have to be done with a ForEach activity having a list of tables.

If it has to be done through ForEach, how do I retrieve the list of tables from Quickbooks using the quickbooks LS? (or does this require a regular API call)

276637-quickbooks-adf-copy.png

Thank You

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,307 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 19,121 Reputation points Microsoft Employee
    2023-01-06T06:51:15.76+00:00

    Hi @Chris Crichton-RA ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your ask, you are trying to copy data from multiple quickbook tables and you are trying to find a way to do the same. Please let me know if that is not the case.

    Quickbook connector supports only two activities in ADF: Copy data activity and LookUp activity

    You can use Lookup activity and get the list of all tables present in quickbook by writing SQL equivalent query (if system tables exist in quickbook) like below :

    SELECT * FROM INFORMATION_SCHEMA.TABLES; GO

    I am not sure if we have an equivalent query in quickbook, I would suggest you to raise a query with Quickbooks support team to get the query probably here : Quickbook support

    According to this post, it seems REST API is not supported in quickbook to get the list of tables, you would probably have to view the API reference or you can download the API Schema to get the fields, and field types. If you query all from each entitiy you will get all the fields.

    So, this is more quickbook support query, rather than ADF query. Once you get the list of tables, you can use Foreach activity to iterate through all the tables and make your dataset dynamic and copy the data . For more details , watch out this video.

    Note: Currently, Quickbooks connector is still under preview , You can try it out and give any feedback related to it by contacting Azure support

    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    No comments