Passing in a string as dynamic content?

Question

Passing in a string as dynamic content?

Mike Wong 46

Hello,

I am currently using this expression for an Odata query in an API call:

?filter=occurred__gt: @{variables('start_date')};occurred__lt:@{variables('end_date')}&offset={offset}&pageSize=100

This works great when I pass it into a Copy Activity directly:

enter image description here

The issue is I'm building a dynamic pipeline where certain endpoints will need different Odata queries and I want to loop over a control table which is in SQL:

enter image description here

Although every time I read the Odata query from the SQL table it reads it as a string, not as dynamic content:

User's image

I have tried setting it as an expression:

enter image description here #

However, it still comes out as a string:

enter image description here

Is there a way I can have the Copy Activity read the odata query above as dynamic content?

Thank you!

Charls Koruthu Varghese 5 Reputation points Microsoft Employee

2023-03-15T11:53:07.48+00:00

Hi Mike,

Are you trying to construct the OData query using variable values in the pipeline?

Instead of directly fetching the Odata query from the SQL Table, have you tried constructing it using a stored procedure that can accept dynamic values from the pipeline as parameters and return the Odata query with substituted values?

Thanks,
Charls
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-03-27T06:58:00.2933333+00:00

@Mike Wong ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Mike Wong 46 Reputation points

2023-03-27T10:56:09.52+00:00

Hello, unfortunately none of these answers solved the issue at hand. Instead, I've resorted to not making this dynamic and hard coding it which is a bit of a shame.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-04-04T05:19:57.88+00:00

Hi Mike Wong, Apologies for the inconvenience here. But, Yes we need to hard code only at this moment. I forwarded this feedback to internal team to consider it for future implementations. I would encourage you also have a feed item created at below link. Internal teams have close look on feedbacks and consider them for future implementations. https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c?query=hubspot

1 answer

Your answer

Charls Koruthu Varghese 5 Reputation points Microsoft Employee

2023-03-15T11:53:07.48+00:00

Hi Mike,

Are you trying to construct the OData query using variable values in the pipeline?

Instead of directly fetching the Odata query from the SQL Table, have you tried constructing it using a stored procedure that can accept dynamic values from the pipeline as parameters and return the Odata query with substituted values?

Thanks,
Charls
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-03-27T06:58:00.2933333+00:00

@Mike Wong ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Mike Wong 46 Reputation points

2023-03-27T10:56:09.52+00:00

Hello, unfortunately none of these answers solved the issue at hand. Instead, I've resorted to not making this dynamic and hard coding it which is a bit of a shame.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-04-04T05:19:57.88+00:00

Hi Mike Wong, Apologies for the inconvenience here. But, Yes we need to hard code only at this moment. I forwarded this feedback to internal team to consider it for future implementations. I would encourage you also have a feed item created at below link. Internal teams have close look on feedbacks and consider them for future implementations. https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c?query=hubspot

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi Mike Wong,

Thank you for posting query in Microsoft Q&A Platform.

Above, is expected behavior. In Pipeline at this movement there is no function available which can convert your string into expressions. So ADF always treats it as string not as expression.

You can consider below workarounds if they suits to your needs.

In Mapping data flows, there is a function called expr() which can convert strings to expressions. Click here to know more. You can consider using mapping dataflows if that works for you.

OR

Try to build your string completely with values before using in API, so that you can directly use them in URL in API call.

Hope this helps. Please let me know if any further queries.

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

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-03-20T09:51:14.7166667+00:00

Hi Mike Wong, Just checking if above answer helps? If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries.

Share via

Passing in a string as dynamic content?

1 answer

Your answer