Azure data factory - pass multiple values from lookup into dynamic query?

Question

I have a lookup function that returns a list of valid GUID IDs in ADF. I then have a foreach process which runs a stored procedure for each GUID ID and assigns an ID column to it.

What I want to do is then have another lookup run which will run the below query to bring me the GUID and also the newly assigned ID. It is very simple to write in SQL

SELECT GUID, Identifier from DBO.GuidLOAD
             WHERE GUID in ('GUIDID','GUIDID','GUIDID')

However I am struggling to translate this in ADF.. I have got as far as the @Concat part and most of the help I find online only refers to dynamic queries with single values as input parameters.. where mine is a list of GUIDs where they may be 1, more or none at all..

Can someone advise the best way of writing this dynamic query?

first 2 run fine, I just need the third lookup to run the query based on the output of the first lookup

Can you please provide the ADF code that you have tried i.e., by using @concat? Also try to provide an image of the activity flow (where you are struck) — Saideep Arikontham
– Saideep Arikontham, Commented Nov 22, 2022 at 11:53
@concat('SELECT CI.ID, UniqueRef [GUID] FROM [DW].[dbo].[Company] CI WITH (NOLOCK) LEFT JOIN [DW].[dbo].[CompanyGUID] RP WITH (NOLOCK) ON RP.CompanyId = CI.CompanyId where uniqueref in (' — Jess8766
– Jess8766, Commented Nov 22, 2022 at 11:58
On which part are you trying to use dynamic content in get_new_id lookup? What is the dynamic content that you are trying to use (is it some activity's output or any variable)? — Saideep Arikontham
– Saideep Arikontham, Commented Nov 22, 2022 at 12:04
It is the output from the first lookup. I want it to run the below.. "where uniqueref in ('value','value,'value)" with value being each individual row brought back by the first lookup (GET_GUIDS_FOR_LOAD) — Jess8766
– Jess8766, Commented Nov 22, 2022 at 12:07
Hope this makes sense? sorry if I haven't explained it very well. — Jess8766
– Jess8766, Commented Nov 22, 2022 at 12:17

Saideep Arikontham · Accepted Answer · 2022-11-22 14:09:11Z

3

You can use string interpolation (@{...}) instead of concat(). I have a sample table with with 2 records in my demo table as shown below:

enter image description here

Now, I have sample look up which returns 3 guid records. The following is debug output of lookup activity.

enter image description here

Now, I have used a for loop to create an array of these guid's returned by lookup activity using append variable activity. The items value for each activity is @activity('get guid').output.value. The following is the configuration of append variable inside for each.

@item().guids

enter image description here

I have used join function on the above array variable to create a string which can be used in the required query.

"@{join(variables('req'),'","')}"

enter image description here

Now, the query accepts guid's wrapped inside single quotes i.e., WHERE GUID in ('GUIDID','GUIDID','GUIDID'). So, I created 2 parameters with following values. I used them in order to replace double quotes from the above final variable with single quotes.

singlequote: '
doublequote: "

enter image description here

Now in the look up where you want to use your query, you can build it using the below dynamic content:

SELECT guid, identifier from dbo.demo WHERE GUID in (@{replace(variables('final'),pipeline().parameters.doublequote,pipeline().parameters.singlequote)})

enter image description here

Now, when I debug the pipeline, the following query would be executed which can be seen in the debug input of the final lookup.

enter image description here

The output would be as below. Only one row should be returned from the sample I have taken and output is as expected:

enter image description here

answered Nov 22, 2022 at 14:09

Saideep Arikontham

6,1922 gold badges6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jess8766 Over a year ago

This works perfectly..thank you.. also makes me understand how variables work and how to use in ADF!

Collectives™ on Stack Overflow

Azure data factory - pass multiple values from lookup into dynamic query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related