how to Dynamically set connection GUID from variable array

[Mr_Coffee]

Hey All, 

I am trying to dynamically pull the connection GUID from an array variable. 
My setup is as follows: 
I am using a lookup activity that pulls a single row of data from a SQL Table. I then use an append variable activity within a Foreach loop to create an Array variable that contains the key value pair of each column and record from the Lookup activity. 

In the copy data activity under the source tab, I'm trying to write a dynamic expression to pull the connection ID from the copy_job_connection_id key in the array below. Copilot is suggesting to use this, but it doesn't work. 

@variables('pl_metadata')[0].copy_job_connection_id

I know that I can define a single variable to represent the copy_job_connection_id and then reference that variable in the connection property of the copy data activity. However, I'd like to skip that and just pull the value from an array. Thoughts? 

My array variable looks like this: 
{
"variableName": "pl_metadata",
"value": {
"data_set_name": "Data2",
"source_host": "server1",
"source_path": "D:\\Data\\dev\\test",
"source_file_type": ".xlsx",
"destination_host": "server2",
"copy_job_connection_id": "some Guid",
"load_type": "full",
"isActive": true
}
}

[nilendraFabric]

the real issue is connection GUID field simply does not support dynamic expressions in Fabric

Try this plz

after your foreach, add a Set Variable activity and do:

@variables('pl_metadata')[0].copy_job_connection_id

store it in a simple string variable, then use that in Copy activity

ir try skip the append variable altogether and just reference lookup output directly in your set variable

[nilendraFabric]

the issue is your array is actually not an array, its a single object so [0] wont work here

try this:

@variables('pl_metadata').copy_job_connection_id

or
@variables('pl_metadata').value.copy_job_connection_id

but honestly one more thing , connection GUID field in Copy Data activity is very strict, it doesnt always accept dynamic expression there, Microsoft has this limitation in many places

[Mr_Coffee]

Thanks for the response. 

I attempted both and received the following errors:

@variables('pl_metadata').copy_job_connection_id
Error:

microsoft fabric The expression 'variables('pl_metadata').copy_job_connection_id' cannot be evaluated because property 'copy_job_connection' cannot be selected. Array elements can only be selected using an integer index.

@variables('pl_metadata').value.copy_job_connection_id
Error:
The expression 'variables('pl_metadata').value.copy_job_connection_id' cannot be evaluated because property 'value' cannot be selected. Array elements can only be selected using an integer index.

I see that my object is uing { }, which means its an object and not an array. Thus I see what you mean. So Data factory thinks it is dealing with an array, because I have stored this object as a variable array using the append variable activity. But it is in fact an object?! I'm not sure how to proceed. 

[nilendraMS · Accepted solution]

the real issue is connection GUID field simply does not support dynamic expressions in Fabric

Try this plz

after your foreach, add a Set Variable activity and do:

@variables('pl_metadata')[0].copy_job_connection_id

store it in a simple string variable, then use that in Copy activity

ir try skip the append variable altogether and just reference lookup output directly in your set variable