0

I have tried so many ways to load data from :

  1. Azure blob to azure SQL synapse.

    My requirement is :
    Description:

     (Input)Blob storage --->  Azure sql synapse(Output)
                emp_dummy.csv----> emp_dummy table
                dept_dummy.csv -----> dept_dummy table
                sales_dummy.csv-----> sales_dummy table   and so on

...

We have files starting with different names but the format is .csv only. I have been trying this in various ways by using getmetadata activity or lookup activity.

When I tried with the below activity, facing the error: [ADF pipeline][1] [1]: https://i.sstatic.net/RynIb.png Error:

{
    "errorCode": "2200",
    "message": "ErrorCode=UserErrorMissingPropertyInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Required property 'fileName' is missing in payload.,Source=Microsoft.DataTransfer.ClientLibrary,'",
    "failureType": "UserError",
    "target": "Copy data1",
    "details": []
}

I hope, I mention all details, if need some more, let me know.

Improve this question
3
  • Hi Imran, please see my newest answer. If my answer is helpful for you, please mark it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. Commented Mar 3, 2020 at 5:50
  • Please let me know if you tested successful, thanks! Commented Mar 4, 2020 at 1:20
  • @Yue,The below steps you mentioned is not enough to do,We need to write one storeprocedure to call schema ,filename and table name.I done this task thanks for your support. Commented Mar 4, 2020 at 6:00

2 Answers 2

Reset to default
1

I figured it out.

Here's my example steps: load two csv files to ADW, and auto create table with the same name with csv filename .

Csv files in blob storage:

enter image description here

Get all the filename in the blob container 'backup':

enter image description here

enter image description here

Foreach item settings:

@activity('Get Metadata2').output.childItems

enter image description here

Copy active in Foreach:

In copy active, using another blob source, add parameter to choose the file: enter image description here enter image description here

Source settings:

enter image description here

Sink dataset(ADW): enter image description here enter image description here

Sink settings:

enter image description here

table name expression: @split(item().name, '.')[0]

Note: get metadata will get the full file name like 'test.csv', when we set the table name, we need split it and set table name as 'test'.

Execute pipeline: enter image description here

Check data in ADW: enter image description here

Hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

it worked but the problem is that it iterates all files inside a single folder. it doesn't iterate through all directories in the blob container. Can you please guide how we can do that?
@MuhammadWaheed can you post a new question?
1

I did a Google search for you. I found several really bad tutorials out there. The two links below looks pretty darn on point.

https://intellipaat.com/blog/azure-data-factory-tutorial/

https://medium.com/@adilsonbna/using-azure-data-lake-to-copy-data-from-csv-file-to-a-sql-database-712c243db658

Remember, when you're copying data from file stores by using Azure Data Factory, you can now configure wildcard file filters to let Copy Activity pick up only files that have the defined naming pattern—for example, "*.csv" or "???20180504.json".

For reference, look at the image below.

enter image description here

If you wanted to iterate through all the files, in different folders, in a Blob environment, instead of setting the File or Folder to this:

adfv2/SalesJan2009.csv

You can set the File or Folder to this:

adfv2/Sales*2009.csv

That will merge all Sales data from 2009 into a single dataframe, which you can them load to SQL Server (Data Warehouse, Synapse, etc).

Improve this answer

1 Comment

I have completed this task , Thanks to all who given inputs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.