Operation on target ForEach1 failed: The function 'length' expects its parameter to be an array

[CheenuSing]

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

[BhaveshPatel]

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.

[v-agajavelly]

Hi @CheenuSing 

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:

1. Go to your Lookup activity
2. 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
3. Now, go to your ForEach activity
• For the Items field, use this expression.

@activity('Lookup1').output.value

By 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.

[CheenuSing]

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

[nilendraFabric]

Hi @CheenuSing 

try Modify the ForEach `Items` property to reference the full array from the Lookup output:

@activity('Lookup1').output.value

[CheenuSing · Accepted solution]

Hi @v-agajavelly , @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 acitity 

If 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.

@v-agajavelly . May be you can check with internal documentation for above behaviour.

With this I have solved my issue. 

Cheers

CheenuSing