Table Storage Dynamic lookup query from ADF

Question

Table Storage Dynamic lookup query from ADF

Max T 21

Hello everybody.

I'm trying to setup an ADF Pipeline that 'explodes' data from an Azure Table Storage to a file system creating csv files with dynamic names based on the Partition Key value of the table.

I have on the left side a list of devices taken from a SQL Azure DB (Now they're 16 but in real-life will be thousands) and for each device I need to create every day a CSV.

I've configured a copy pipeline like this

and set a TableStorage Source inside the FOR EACH cycle

and everything seems to work well except the fact that the filter expression of the TABLESTORAGE provide a strange error:

The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'.

I've tried several ways to format the input string, all using or not the Dynamic Content to parse it but I always obtain the same error.

PartitionKey eq '{@item().deviceserial}' and Timestamp gt datetime'2021-02-01T00:00:00' and Timestamp le datetime'2021-02-02T00:00:00'
or
PartitionKey eq @item().deviceserial and Timestamp gt datetime'2021-02-01T00:00:00' and Timestamp le datetime'2021-02-02T00:00:00'

but nothing changes.

Where am I wrong ?
Thanks
Max

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-09-01T07:12:13.663+00:00

Hi @Max T ,

Welcome to Microsoft Q&A forum and thanks for posting your query.

As per the error message, it seems like the issue points to a LENGTH function input value but you have shared the Query for your Table storage which is not relevant as my understanding.

Could you please confirm if you are using a LENGTH function anywhere in your FOREACH activity? If so could you please share the complete dynamic expression where it is being used and the variable/parameter used in it and a sample input value so that we can better assist on this query.

We look forward to your response.
Max T 21 Reputation points

2021-09-01T13:34:26.08+00:00

AS I Know we're not using it in the code.
In attach you find the Complete JSON of the Pipeline.
If I have to watch in other places let me know, I'm new to ADF.
Thank you very much!
Max
128239-pipeline-json.txt
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-09-02T12:40:08.1+00:00

Hi @Max T ,

Thanks for your response and details. I checked the pipeline JSON payload and didn't notice any LENGTH function used anywhere. This is strange. If that is the case then I suspect the error message is not relevant.

Have you got a chance to try changing the query for your Table Storage ? Instead of two dates, try with one date field and see if that makes a difference.

If that didn't help, I would recommend filing a support ticket for deeper investigation as the error message is irrelevant to the issue so that a support engineer can collect more details and involve relevant product team to further troubleshoot and resolve the issue.

Do let us know how it goes.
Max T 21 Reputation points

2021-09-02T13:29:54.113+00:00

Hello. I've tryed, to cut any other problem, to put another formula with data taken from a variable that I've set

"variables": {
"DevList": {
"type": "Array"
},
"SelDateFrom": {
"type": "String",
"defaultValue": "datetime'2021-08-31T00:00:00'"
}

and then in the TableStorage query

Timestamp gt @variables('SelDateFrom')

But I get the same problem. I'll ask for assistance!
thanks
Max

Accepted answer

0 additional answers

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-09-01T07:12:13.663+00:00

Hi @Max T ,

Welcome to Microsoft Q&A forum and thanks for posting your query.

As per the error message, it seems like the issue points to a LENGTH function input value but you have shared the Query for your Table storage which is not relevant as my understanding.

Could you please confirm if you are using a LENGTH function anywhere in your FOREACH activity? If so could you please share the complete dynamic expression where it is being used and the variable/parameter used in it and a sample input value so that we can better assist on this query.

We look forward to your response.
Max T 21 Reputation points

2021-09-01T13:34:26.08+00:00

AS I Know we're not using it in the code.
In attach you find the Complete JSON of the Pipeline.
If I have to watch in other places let me know, I'm new to ADF.
Thank you very much!
Max
128239-pipeline-json.txt
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-09-02T12:40:08.1+00:00

Hi @Max T ,

Thanks for your response and details. I checked the pipeline JSON payload and didn't notice any LENGTH function used anywhere. This is strange. If that is the case then I suspect the error message is not relevant.

Have you got a chance to try changing the query for your Table Storage ? Instead of two dates, try with one date field and see if that makes a difference.

If that didn't help, I would recommend filing a support ticket for deeper investigation as the error message is irrelevant to the issue so that a support engineer can collect more details and involve relevant product team to further troubleshoot and resolve the issue.

Do let us know how it goes.
Max T 21 Reputation points

2021-09-02T13:29:54.113+00:00

Hello. I've tryed, to cut any other problem, to put another formula with data taken from a variable that I've set

"variables": {
"DevList": {
"type": "Array"
},
"SelDateFrom": {
"type": "String",
"defaultValue": "datetime'2021-08-31T00:00:00'"
}

and then in the TableStorage query

Timestamp gt @variables('SelDateFrom')

But I get the same problem. I'll ask for assistance!
thanks
Max

Answer 1

ShaikMaheer-MSFT 38,551 Microsoft Employee Moderator

Hi @Max T ,

Thank you for posting query on Microsoft Q&A and thank you for sharing your pipeline json.

I see small errors in your pipeline json.

It seems you are passing @activity('lkDeviceList').output to items field in ForEach activity. You should pass @activity('lkDeviceList').output.value there.

Could you please update your configurations accordingly and see if that helps.

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

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

Please don't forget to click on Accept Answer button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2021-09-10T16:01:19.427+00:00

Hi @Max T ,

Following up to check is above answer helps you? If yes, Please Accept Answer. Accepting answer helps community as well. Please let us know if any further queries. Thank you.
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2021-09-13T09:51:36.89+00:00

Hi @Max T ,

Just checking is above answer helps you? If yes, Please Accept Answer. Accepting answer helps community as well. Please let us know if any further queries. Thank you.
Max T 21 Reputation points

2021-09-13T12:17:16.663+00:00

Thank you! Now it works (with other errors that I'll debug soon but ... works)

Max
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2021-09-17T12:02:51.043+00:00

Hi @Max T ,

Thank you for marking as accepted answer.

Share via

Table Storage Dynamic lookup query from ADF

0 additional answers

Your answer