question

ShaunBailey-5325 avatar image
0 Votes"
ShaunBailey-5325 asked AnnuKumari-MSFT edited

Architecure 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!

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AnnuKumari-MSFT avatar image
1 Vote"
AnnuKumari-MSFT answered AnnuKumari-MSFT edited

Hi @ShaunBailey-5325 ,

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

201408-image.png

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.

201358-image.png

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

201414-image.png

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.

201433-image.png

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





image.png (38.5 KiB)
image.png (49.0 KiB)
image.png (36.7 KiB)
image.png (39.3 KiB)
image.png (36.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @AnnuKumari-MSFT ! This worked perfectly and is very well illustrated! 5 gold stars for you!!

1 Vote 1 ·

@ShaunBailey-5325 ,
Glad to know it helped. Thanks! Although I can't see rating is reflected, hope you took the survey by clicking on the star !

0 Votes 0 ·