locked
How to Load Multiple .csv files (each file with different schema) from blob to their respective tables in azure sql server in ADF RRS feed

  • Question

  • Hi All,

    I am trying to load multiple .csv files (each file with different schema) from blob to their respective tables in Azure Sql DB.

    where destination tables in sql db are already created (only schemas ).

    My pipelines (tried with both scenarios):

    1)Metadata -> For Each activity -> Copy Activity

    2) For Each Activity -> Data flow

    So in pipeline parameter i am providing source and their respective table name as destination 

    for ex: 

    [{"source":{"fileName":"Dept.csv"},"destination":{"table":"department"}}
    ,{"source":{"fileName":"Emp.csv"},"destination":{"table":"employee"}}]

    and trying to pass these parameters in source and sink (in copy activity which is in for each loop).

    But the result is completely different as it is inserting a merged data of all files in every sql table irrespective of schema and datatype .The resultant schema it is considering the structure of very first file in blob and is replicating same schema in all tables.

    Is this Scenario ( Copying different schema files from blob to sql db) possible using copy activity or with Data flow ?

    If this scenario is possible please help with detailed process.

    Thanks in advance.


    Tuesday, October 15, 2019 3:07 PM

Answers

  • Hi Sai,

    Apologizes for the late response. The reason might be because of your copy activity source settings. 

    Could you please make sure "Wildcard file name" section in source is empty. If your "Wildcard file name" section has *.* then this issue occurs. If it exists please try removing it since you are doing the dataset parameterization.


     Hope this helps. Let us know if you see any issues. 

     


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, October 16, 2019 1:52 AM

All replies


  • Hi Sai,

    I don't understand how you ran the pipeline with your parameters, but this is how I tried assuming the target tables are already created with the same column as corresponding file.

    First, I created parameterized dataset by adding a parameter in the dynamic file path. (see pics below)

    Here, you need to tell ADF about the file name. For ex. if the file name is 'emp.csv' -> use 'employee' table  ... @if(equals(item().name, 'Emp.csv'), 'employee', '')

    ( I hope you don't have too many files with cryptic names like above...as you will have to write nested script for each file ...or you will have to think of some clever way to resolve the file names to table names.)

    I hope this helps your use case. Let me know if you need more help on this.

    Thanks,

    Raj


    Tuesday, October 15, 2019 6:44 PM
  • Hi Sai,

    Apologizes for the late response. The reason might be because of your copy activity source settings. 

    Could you please make sure "Wildcard file name" section in source is empty. If your "Wildcard file name" section has *.* then this issue occurs. If it exists please try removing it since you are doing the dataset parameterization.


     Hope this helps. Let us know if you see any issues. 

     


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, October 16, 2019 1:52 AM
  • Hi Sai, 

    I have provided a possible approach to achieve your requirement (with some assumptions) in below MSDN thread. Please feel free to let us know if you see any issues. 

    https://social.msdn.microsoft.com/Forums/en-US/8e4bdc53-d940-4204-8230-67eb74e36f83/error-while-loading-multiple-csv-files-from-blob-to-respective-tables-in-azure-sql-db-using-adf?forum=AzureDataFactory


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, October 16, 2019 6:31 AM