create a variable in the Data Factory

Michele Teixeira 1

Hi,

I want to create a variable in the Data Factory that returns all the "CustomerID" of a json, below:
I've tried everything and I can't solve it.
Can you help me?

"Result": [
{
"CreatedDate": "/Date(1607828445483-0300)/",
"CustomerID": 787754,
"CustomerType": "P",
"DocumentNumber": "11882470770",
"Email": "xpto@Stuff .com",
"IntegrationID": null,
"Name": "Bruna",
"WebSiteID": 162
},
{
"CreatedDate": "/Date(1607828774663-0300)/",
"CustomerID": 787755,
"CustomerType": "P",
"DocumentNumber": "41811885829",
"Email": "michele.alves@Stuff .com",
"IntegrationID": null,
"Name": "Debora",
"WebSiteID": 157
},

2 answers

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-12T10:02:07.067+00:00
Hi @Michele Teixeira ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

This can be achieved by using foreach activity which can store all these customer ids into an array variable. Set variable activity can be used in addition to this to convert array into string as below.

Foreach Config:

Append variable inside Foreach:

Set variable post Foreach to convert array into string:

Please let us know for further queries and we will be glad to assist.

--

Please accept an answer if correct. 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.
Please sign in to rate this answer.

1 person found this answer helpful.
Michele Teixeira 1 Reputation point

2021-01-12T12:45:26.86+00:00

Good Morning,

I was very happy to have the answer so quickly. Thank you!
I'm trying to redo it here, but as I'm a layman at Data Factory, I still have doubts.
I tried to redo the steps you described but I think I'm doing something wrong ... it didn't work.
Could you explain more in detail?
I'm sorry, but I have a hard time understanding ...

I am confused with the variables ... I choose array for the first "set variable" and then string for the second "set variable"?
and in which activity should I leave which variable?

Michele Teixeira 1 Reputation point

2021-01-12T12:47:52.647+00:00

Nandan Hegde 32,831 Reputation points MVP

2021-01-12T12:56:45.567+00:00

Hey @Michele Teixeira ,
You might be getting the JSON values from some activity output correct?
That would be your value in the Foreach.

Just for POC, @HarithaMaddi-MSFT has created a pipeline parameter and added your value in that parameter and used it within the Foreach loop.

So the main query remains : from where are you getting the JSON value

Michele Teixeira 1 Reputation point

2021-01-12T13:33:29.13+00:00

yes i have a json file inside folders without blob storage. But how do I point the json file to ForEach? Sorry but it is not clear yet ....

Michele Teixeira 1 Reputation point

2021-01-13T00:23:08.647+00:00

I spent the whole day trying to understand this flow and tried to redo it, but it is very confusing, I couldn't solve the problem.
Perhaps this is a community for people who know how to work with data factory.
I'm a layman and I'm trying to learn, but it got really confusing ..
I had no return.
Thanks

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-13T07:20:30.74+00:00

Hi @Michele Teixeira ,

I understand the challenge in reproducing the solution as you are new to data factory. I will try to make it as easy as possible for you. Please find below GIF showing the implementation and attached the JSON code of the pipeline which is in .txt format and can be copied to code section in your pipeline by renaming the pipeline name as per your pipeline name, post that dataset can be modified to point to the location where JSON file exists.

PipelineJSONfile

JSON File input used:

56152-jsonfile.txt

Code section in data factory where to paste this JSON (Highlighted below):

Please let us know for further queries and we will be glad to assist.

Michele Teixeira 1 Reputation point

2021-01-13T16:50:39.637+00:00

hello, I want to thank you again for your help.
Maybe I was not clear ... kkkkk

I need Data Factory to take files in json format from the blob storage and bring me only the customerID of that json.
I will try to replicate what you sent me ... watch the video.
anyway it helped me a lot, i've already learned a lot from you.

Michele Teixeira 1 Reputation point

2021-01-13T17:45:51+00:00

@HimanshuSinha-msft

I’m having a problem with ForEach

“The expression 'length(array(activity('Lookup1').output.value[0].Result) )' cannot be evaluated because property 'value' doesn't exist, available properties are 'firstRow, effectiveIntegrationRuntime, billingReference, durationInQueue'.”

@array(activity('Lookup1').output.value[0].Result)

The return generates an error and says that “value” is not valid.
When I put "firstRow" in place of "value" it brings the ID's but one per page, that is, it generates thousands of separate CustomerIDs, as I do to put all the "CustomerIDs" on a single page, or in a sequence.

Awaiting return.

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-15T08:26:15.29+00:00

Hi @Michele Teixeira ,

Thanks for sharing the details. This error is caused when we enable "First Row only" property in the Lookup activity. Please uncheck it and let us know if the error is resolved. If facing any other issues, please reach out to us at AzCommunity[at]microsoft[dot]com with subject - "Attn:Haritha - create a variable in the Data Factory" and we will interact with you in person to help in implementing the same.

Looking forward for your response!

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-18T12:42:50.613+00:00

Hi @Michele Teixeira ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.

HarithaMaddi-MSFT 10,136 Reputation points

2021-01-19T12:45:13.007+00:00

Hi @Michele Teixeira ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
dgreat juan 1 Reputation point

2021-02-11T00:32:35.063+00:00

Based on my analysis the above solution is correct and can be accepted as the correct answer.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

create a variable in the Data Factory

2 answers

Your answer