Function inside a Variable?

Question

Function inside a Variable?

Dirk Sachse 131

Is it possible to use a function inside of a variable?

I need this inside of a variable named "requestBody":

@concat('Test ', formatDateTime(utcNow(),'yyyy-MM-dd'),' URL')

And I want to use it as dynamic content like this:

@variables('requestBody')

It should resolve to this string, which I need in the Request body field of a POST method in a Copy data activity:

 Test 2022-01-05 URL

Nandan Hegde 36,886 Reputation points MVP Volunteer Moderator

2022-01-05T11:41:56.987+00:00

Please use the below mentioned expression (Note: you need to remove space in your expression)

Answer accepted by question author

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

HI @Dirk Sachse ,

Thanks for posting query in Microsoft Q&A Platform.

If I understood your ask better then you want to have expression as a string in SQL. Read that expression as string in to ADF and in ADF convert that string in to expression.

Sadly, this is not possible at this moment in pipeline expressions. But, converting string and to expression is possible using expr() function in ADF data flows.

So, if your requirement suits in dataflows, then consider using dataflows for your case.

Please check below video, where its explained using expr() function to convert string to expression.
https://www.youtube.com/watch?v=Dssph_O5cVQ&list=PLMWaZteqtEaLacN3eS3s8pw2jtwBVb1BH&index=34

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

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

Please consider hitting Accept Answer button. Accepted answers helps community as well.

Dirk Sachse 131 Reputation points

2022-01-10T11:33:37.657+00:00

Your video is super helpfull. Thank you!

1 additional answer

Your answer

Nandan Hegde 36,886 Reputation points MVP Volunteer Moderator

2022-01-05T11:41:56.987+00:00

Please use the below mentioned expression (Note: you need to remove space in your expression)
Dirk Sachse 131 Reputation points

2022-01-10T11:33:37.657+00:00

Your video is super helpfull. Thank you!

Answer 1

Nandan Hegde 36,886 MVP Volunteer Moderator

Hey,
Can you please clear your requirement :
Is the below output that you are expecting dynamically:

Then use the below expression (slight correction to the one you provided)

@concat('Test ',formatDateTime(utcNow(),'yyyy-MM-dd'),' URL')

Dirk Sachse 131 Reputation points

2022-01-05T11:42:16.07+00:00
I think I have to explain more.

I have this pipeline:

Lookup -> Gets a String out of a SQL Database. The string looks like this "Test 2021-01-05 URL"

Set Variable -> Sets the variable "requestBody" to the string that came out of Step 1.

Copy Data -> Uses the variable from Step 2 as the input in the "Request body field" like this: @variables('requestBody')

Right now the date inside the string from Step 1 is hardcoded. I want it to by dynamic.
I could use a function like this: formatDateTime(utcNow(),'yyyy-MM-dd')

In the first iteration of my pipeline, I was using this in the "Request body field": @markus.bohland@hotmail.de ('Test',formatDateTime(utcNow(),'yyyy-MM-dd'),' URL').

But I want the string "Test 2021-01-05 URL" fully dynamic, so I just build 1 pipeline and everything else is fully dynamic. I just want to save a string in a SQL Table like this: @markus.bohland@hotmail.de ('Test',formatDateTime(utcNow(),'yyyy-MM-dd'),' URL') and have it put into the "Request body" field.

To clear it even more, I have to query a REST API everyday with 10 different JSON requests and I dont want to maintain 10 pipelines. I want 1 fully dynamic pipeline in ADF and a configuration table in SQL server.
The alternative could be 10 JSON files which feed the pipeline, but I m not giving up yet.

I could also do this in the query of the Lookup step:

select replace(requestBody, 'PLACEHOLDERENDDATE', CAST(GETDATE() AS Date)) from etl.conf_lnd_GA

----------

----------

Share via

Function inside a Variable?

1 additional answer

Your answer