Architecure Question

Question

Ok, newbie user of Azure Data Factory here that has been banging my head against the wall trying to figure out the best way to proceed architecturally. :/

Here's what I am ultimately looking to do...

1. Pull in a CSV file with multiple rows
2. For each row, I need to convert the data to JSON
3. For each JSON output, I need to push the data into a stored procedure

At the surface this sounds straightforward, but after hours of trying and still not being able to get anything working, I'm turning to the experts to help!

Start with the example CSV:

"FullName", "Gender", "Address"
"John Doe", "Male", "123 Main St"
"Jane Doe", "Female", "456 Abbey Rd"

On the first loop it should produce the first JSON array which is sent to the stored proc:

{"FirstName": "John Doe", "Gender": "Male", "Address": "123 Main St"}

And on the second loop it should produce the second JSON array which is sent to the stored proc:

{"FirstName": "Jane Doe", "Gender": "Female", "Address": "456 Abbey Rd"}

I'm quite visual so big time brownie points if you have pictures illustrating how to solve this!

Thanks in advance!

Answer 1

Hi @Shaun Bailey ,

Welcome to Microsoft Q&A platform and thankyou for posting your question here.

As I understand your query correctly, it seems probably you want to fetch the data from .csv file and loop through each of the rows and pass the value to stored procedure as input parameter. Please let me know if my understanding is incorrect.

To achieve the above requirement, kindly follow these steps:

1. Upload the .csv file to the ADLS location and create a dataset pointing to that file.
2. Create an ADF pipeline : Use lookup activity to fetch the data from the newly created dataset

3. Use ForEach activity to iterate over the lookup output by having this expression in the Items: @activity('Lookup1').output.value
4. Inside ForEach, use stored procedure activity . To reproduce this scenario, I have created a table with columns : FullName, Gender, Address and created an SP to fetch the record based on the input parameter.

Import the parameters in stored procedure activity and pass the parameter values as @item().Address , @item().FullName , @item().gender

Note: Stored procedure activity doesn't return any data. If you want the data as the output of the activity, use stored procedure option inside lookup activity instead.

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

------------------------------

• Please don't forget to click on or upvote 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