Transfer the output of 'Set Variable' activity into a json file [Azure Data Factory]

Question

In Data Factory, can we have the output from 'Set Variable' activity being logged as a json file?

Answer 1

I generally use the Copy activity for writing files but it is possible to write content to Azure Data Lake (ADLS) Gen 2 using the Blob REST API and PUT command. The settings in the Web activity are crucial to this working:

Setting	Value	Notes
URL	some blob	NB this is using the .blob address not the .dfs one. The path must end in ?resource=file
Method	PUT
Headers
x-ms-version	2019-07-07
x-ms-blob-type	BlockBlob
Content-Type	application/json	This value is for writing json but can be customised eg application/csv
Body	@variables('varResult')	I'm using a pre-prepared variable with json content but this can be anything
Authentication	Managed Identity
Resource	https://storage.azure.com

Note you must set the URL to the file name and folder you want and use the .blob address. The URL must end with ?resource=file:

Example URL / Blob address https://yourstorage.blob.core.windows.net/yourFilesystem/yourFolder/someFile.json?resource=file

Note also I'm writing json here but you can amend as required, eg application/csv. I am using a variable in the Body but this can be anything you like. The documentation states this only supports files up to 2GB so this is only for small activities.

Screenprint:

I wasn't able to get this to work with the .dfs address and/or Data Lake methods but it's fine as long as it works on blob.

Answer 2

If you want to write the content of a variable of type Array, there is a workaround which works fine. Goal: write content of your array as 1 line per value of the array into a file

variable : [ a, b, c]
to
file content:
a
b
c

Steps:

1. Create an 'empty' file with 1 row , can be a json file or something else with just 1 row
2. Use the additional column mechanism
3. Expand-join-with-carriagereturn the array variable with use of @join and @decodeUriComponent -> @join(variable,decodeUriComponent('%0A'))

Yes, it is horrible that Microsoft doesn't have an @char(int) function to create a special character.(or I am an idiot and doesn't know the right way to concat an '\n' , which I tried but didn't work.)

Answer 3

Another simplest way to achieve this requirement is by utilizing "Add additional columns during copy" feature as below.

Have a set variable activity and set the value of the variable, followed by a copy activity. In copy activity, Source settings, you have Additional columns property where you can give a name to source variable column. Using Dynamic expression @variables('varInput') you assign the variable value. Then in Mapping section, you can remove unwanted columns and only have the required columns including variable column that you created in Additional columns of Source. Then on the destination side give your desired column name and test it.

NOTE: This feature works with the latest dataset model. If you don't see this option from the UI, try creating a new dataset.

Hope this helps.

Answer 4

No built-in easy way for your need as i know.

2 ways as workarounds:

1.Use enable Azure Monitor diagnostic log in ADF to log data into Azure Blob Storage as JSON files.And every activity's execution details(contains output) could be logged in the file.However,you need to get know the structure of json schema and grab what you want.

2.Use Azure Function or Web Activity after Set Variable Activity to call API(@activity('Set Variable1').output). Save the output into residence as json file in the function method with SDK code.