Forum Discussion
Operation on target ForEach1 failed: The function 'length' expects its parameter to be an array
Hi,
I am using Fabric trial licence.
I created a pipeline with Lookup as startting point to retrieve SchemaName and TableName from on prem sql database.
It wotks fine and gives me the desired output.
Then I created a For Each activiy to copy data from each table to a landing areas in the lakehouse
settings for ForEach
Then I set up the copy activity within for each
I get the error
Operation on target ForEach1 failed: The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object' .
If I change the setting for For Each to @activity('Lookup1).output.firstRow
still get the same error.
How to fix this error ?
Cheers
CheenuSing
Hi Anonymous , nilendraFabric BhaveshPatel
Thanks for all your suggestions. All point to the same approach.
I solved the issue as follows.
In the Query area of the Lookup settings, I first used
select TABLE_SCHEMA as SchemaName , TABLE_NAME as TableName
From ecommerce INFORMATION_SCHEMA_TABLES
where TABLE_TYPE= 'BASIC TABLE'
This did give me thee schemaname and tablename. But did not have the option to select value array in the For Each Loop.
I changed the query to
select s.name as SchemaName,
t.name as TableName from sys.tables t
JOIN sys.schemas s on t.schema_Id = s.schema_Id Order by s.name ,t.name
This also gave me the SchemaName and TableName. And it allowed me to choose Lookuparray in the ForEach loop.
So by the above in the query solved my issue in the ForEach loop.
Another thing I observed is
In the destination screen of the Copy acitityIf I put the @item().TableName in the fileName above, it did generate the files but coluld not be previewed in the Lakehouse. And the
whas greyed out .txt and could not be changed to csv.
With the above settings the files got created with .csv extension and could be previewed.
However the file names included dbo. in the beginning of the file name.Anonymous . May be you can check with internal documentation for above behaviour.
With this I have solved my issue.
Cheers
CheenuSing
5 Replies
Hi Anonymous
try Modify the ForEach `Items` property to reference the full array from the Lookup output:
@activity('Lookup1').output.value
Hi Anonymous , nilendraFabric BhaveshPatel
Thanks for all your suggestions. All point to the same approach.
I solved the issue as follows.
In the Query area of the Lookup settings, I first used
select TABLE_SCHEMA as SchemaName , TABLE_NAME as TableName
From ecommerce INFORMATION_SCHEMA_TABLES
where TABLE_TYPE= 'BASIC TABLE'
This did give me thee schemaname and tablename. But did not have the option to select value array in the For Each Loop.
I changed the query to
select s.name as SchemaName,
t.name as TableName from sys.tables t
JOIN sys.schemas s on t.schema_Id = s.schema_Id Order by s.name ,t.name
This also gave me the SchemaName and TableName. And it allowed me to choose Lookuparray in the ForEach loop.
So by the above in the query solved my issue in the ForEach loop.
Another thing I observed is
In the destination screen of the Copy acitityIf I put the @item().TableName in the fileName above, it did generate the files but coluld not be previewed in the Lakehouse. And the
whas greyed out .txt and could not be changed to csv.
With the above settings the files got created with .csv extension and could be previewed.
However the file names included dbo. in the beginning of the file name.Anonymous . May be you can check with internal documentation for above behaviour.
With this I have solved my issue.
Cheers
CheenuSing
Hi Anonymous
Great question, I totally understand how this can be a bit confusing at first. The reason you're getting the error, the function 'length' expects its parameter to be an array... but got an object instead is because your Lookup activity is only returning a single row, not an array of rows and ForEach needs an array to loop over.
In your Lookup activity settings, there’s a checkbox called “First row only” this is currently enabled in your case (which is why your output shows .firstRow but not .value).
When that’s turned ON, Lookup will return a single row as an object, not an array. That’s why @activity('Lookup1').output.value doesn’t exist and throws an error.
Here’s what you can do to get it working:
- Go to your Lookup activity
- In the Settings tab, uncheck the box for “First row only”
- This will return the full list of rows as an array in the .value property
- Now, go to your ForEach activity
- For the Items field, use this expression.
@activity('Lookup1').output.valueBy adding .value i think, this will allow the ForEach loop to process each row returned by the Lookup (e.g., each SchemaName and TableName) one by one.
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Regards,
Akhil.For better output, You should try using Dataflow Gen 2. Self Service with lot more options. ( Filter the rows & each different tables)
Try step by step process to get the desired outcomes. Both Fabric Data Factory and Dataflow Gen 2 is doing the same approach of data engineering.
Hi nilendraFabric ,
WHen I tried your suggestion I get the following error
The expression 'length(activity('Lookup1').output.value)' cannot be evaluated because property 'value' doesn't exist, available properties are 'firstRow, effectiveIntegrationRuntime, billingReference, durationInQueue'.Also why Lookup1 value array option is not shown under
Why is it so ?
Any help will be greatly appreciated.Cheers
CheenuSing
