none
Copy multiple files with different structure/schema from Data Lake into Azure SQL Database

    Question

  • Hi Guys,

    I'm new onto the Microsoft Azure platform. Currently trying to copy multiple files with different files structure/schema from a Data Lake into Azure SQL tables using one CopyPipeline in Data Factory and i'm not able to select or get all the files i intend to copy into Azure SQL tables. It only allows me to select one file, even when there are multiple files in the main folder  and it only gets the first file when doing the mappings to the destination table.

    Is there a way to get multiple files with different structure/schema from Data Lake into Azure SQL Tables using one CopyPipeline?  Please note that i need to be able to move files with different files structure/schema in one folder from data lake to Azure SQL tables using one copyPipeline. Also note that i am able to move data with different files structure/schema from Azure SQL tables to Azure Data Lake in one copyPipeline but not vice versa.


    Your feedback will be highly appreciated.

    Regards,

    Thabo

    Friday, July 13, 2018 8:06 AM

All replies

  • Hi Thabo,

    # Is there a way to get multiple files with different structure/schema from Data Lake into Azure SQL Tables using one CopyPipeline?

    Yes, the copy data tool should be what you want, which supports copying multiple files/tables in one pipeline, please reference post: https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-tool.

    # i need to be able to move files with different files structure/schema in one folder from data lake to Azure SQL tables using one copyPipeline.

    In copy data tool, you need to set schema mapping rules between source and sink datastore, not sure what your source and sink schema are like, would you please clarify your scenario? Anyway, please try the copy data tool first and feel free to post new questions.

    Thanks.

    Friday, July 13, 2018 9:15 AM
  • Hi Thabo and Wang,

    Copy Data Tool won't help in this case. Copy Data Tool only create pipeline for a single file or files in a folder with the same schema. Also I think this is what a single copy activity can do.

    In your case, I think:
    1. You could create multiple copy activity in a pipeline.

    2. You could use foreach + copy activity in a pipeline. Make your format, structure and column mappings as parameters, and then use foreach iterate the array of different structure/schema. 

    If you have already generated pipelines with Copy Data Tool for copying data from azure sql tables to azure data lake, you could reference the json payload generated by copy data tool.

    Friday, July 13, 2018 9:57 AM