Passing Array Parameter in Look Activity

Question

Passing Array Parameter in Look Activity

Rohit Kulkarni 441

Hello Team,

I want to Pass the array parameter in lookup activity.

I am Passing the parameter :

AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) IN [@{pipeline().parameters.Companies}]

After running the query i am getting error

Please advise .

Thanks
RK

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T03:47:33.767+00:00

Hey, Can you please provide the overall query in your lookup activity and some sample values of the parameter and also the database schema as it might help us to debug on our end
Rohit Kulkarni 441 Reputation points

2021-03-12T03:51:15.797+00:00

Hello Nandan

select * from from INFORMATION_SCHEMA.TABLES t

AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) IN [@{pipeline().parameters.Companies}]

I am passing the paramter :

Answer accepted by question author

8 additional answers

Your answer

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T03:47:33.767+00:00

Hey, Can you please provide the overall query in your lookup activity and some sample values of the parameter and also the database schema as it might help us to debug on our end
Rohit Kulkarni 441 Reputation points

2021-03-12T03:51:15.797+00:00

Hello Nandan

select * from from INFORMATION_SCHEMA.TABLES t

AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) IN [@{pipeline().parameters.Companies}]

I am passing the paramter :

Answer 1

Nandan Hegde 36,716 MVP Volunteer Moderator

Hey Rohit,
Based on above things I guess your requirement is you want to get the result from Information_schema:
So please make the below changes :
Update the Parameter type to String and its value as :
'Group','ABC'
without any parenthesis

And also update lookup activity as below:
select * from from INFORMATION_SCHEMA.TABLES t

where LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) IN (@{pipeline().parameters.Array})

Hope that should solve

Rohit Kulkarni 441 Reputation points

2021-03-12T04:21:58.02+00:00

Hi Nandan,

The example you have provide is working fine. But i need in array. I need to satisfy the below condition in below query:

Companies (arrays) --> iF length > 0 Then apply parameter --> Default to empty --> Example for testing: Group

select * from from INFORMATION_SCHEMA.TABLES t

where LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) IN (@{pipeline().parameters.Companies })
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T04:36:11.77+00:00

Hey,
can you please be more clear on what does this mean : Then apply parameter --> Default to empty --> Example for testing: Group
you can also check the string parameter whether it is blank which is equivalent to empty array

like Parameter=''
Rohit Kulkarni 441 Reputation points

2021-03-12T04:38:35.753+00:00

What i mean to say is If The companies Value like (Group ,ABC) then apply parameter other wise leave it blank
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T05:03:25.407+00:00

Then you can use a combination of concat to create the SQL query and string,Replace functions to convert the array to string and replace unwanted characters
Rohit Kulkarni 441 Reputation points

2021-03-12T05:10:48.697+00:00

Which function you are saying i am not getting with above example can you let me know.
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T05:26:39.927+00:00

@markus.bohland@hotmail.de ('select * from INFORMATION_SCHEMA.TABLES t

where LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) IN (', replace(replace(replace(replace(string(pipeline().parameters.Array),'[',''),']',''),'\',''),'"','''') ,')')

use this in your lookup activity
Rohit Kulkarni 441 Reputation points

2021-03-12T06:44:47.857+00:00
Thanks got it

I have 2 parameter

[@{pipeline().parameters.Tablewithcompanies}]

[@{pipeline().parameters.Companies}]

I need to pass above parameter in the below

select * from from INFORMATION_SCHEMA.TABLES t

where LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1)

The output i would be passing ["Table1","Group"] in the pipeline

Please advise

Regards
RK
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T07:05:33.89+00:00

Sorry, I didnt get.
Can you clarify again:
You have 2 parameters:
what are the values for those?

And where do you want to incorporate those 2 parameters in the lookup query?
It would be better if you provide a sample lookup query w.r.t parameters though not as per proper ADF format:
Rohit Kulkarni 441 Reputation points

2021-03-12T12:02:49.493+00:00

This is not working for me

where LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) IN
(', replace(replace(replace(replace(string(@{pipeline().parameters.Companies }),'[',''),']',''),'\',''),'"','''') ,')')

I am getting invalid string error :']',

Answer 2

Rohit Kulkarni 441

Sorry for repeating Please don't remove the where clause statment.I am sending you the whole script once again :

@markus.bohland@hotmail.de ('select distinct
LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) AS CompanyName
from INFORMATION_SCHEMA.TABLES t
where t.TABLE_TYPE ="BASE TABLE"
AND t.TABLE_NAME like "%$%"
AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) <> ''
AND LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) IN (', replace(replace(replace(replace(string(pipeline().parameters.Companies),'[',''),']',''),'\',''),'"','''') )

I am getting error

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T13:32:22.7+00:00

Please use this :

@markus.bohland@hotmail.de ('select distinct
LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) AS CompanyName
from INFORMATION_SCHEMA.TABLES t
where LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) IN (', replace(replace(replace(replace(string(pipeline().parameters.Companies),'[',''),']',''),'\',''),'"','''') ,') and t.Table_name like ','''%$%''',' and t.table_type=','''Base Table''',' AND LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) <>', '''''')

Hope this would work for you :)

Answer 3

Rohit Kulkarni 441

The query is working fine Now the out put is blank.Please refer the screenshot below:

Passing paramter in Pipeline :

I have Passed the value in parameter :

The output is blank

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T13:41:05.36+00:00

Hey,
For debugging purpose, create a pipeline variable and assign that query to the variable.
and execute the ADF.
Then can you paste the variable output value from the ADF logs here.
That would help us in debugging further.

Answer 4

Rohit Kulkarni 441

I can't run this pipeline because it starts loading data from 1 environment to other environment. I just wanted to add one parameter in offline mode and test it.

Is there any other way i can find.

Thanks

RK

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-12T14:03:33.11+00:00
Can you tell me what you are trying to achieve?
It would be better if you provide the actual SQL query which you want to execute on SQL server .
I would need the below things:

SQL query

image of the output of that SQL query on database via SSMS

Answer 5

Rohit Kulkarni 441

Already existing pipeline is there which will transfer the data from raw zone to staging zone.

When i run the query it is showing blank.If i comment this "AND LEFT(t.TABLE_NAME,CHARINDEX(','''$''',',t.TABLE_NAME) - 1) IN (', replace(replace(replace(replace(string(pipeline().parameters.Companies),'[',''),']',''),'\',''),'"','''') )"

If i run the previous query

select DISTINCT LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) AS CompanyName
from INFORMATION_SCHEMA.TABLES t
where t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME like '%$%'
AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) <> ''

Then the output will be

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-15T04:41:43.973+00:00
Hey Rohit,
The output is returning blank as either the parameter values are not matching the table names in info_schema.

you can verify from the lookup activity logs:

Can you please provide the below details :

sqlReaderQuery that is getting genereted in Lookup input after ADF execution

Array parameter value which you are passing

Sample value of the below query execution in database:
Select table_name
from INFORMATION_SCHEMA.TABLES t
where t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME like '%$%'
AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) <> ''

Share via

Passing Array Parameter in Look Activity

8 additional answers

Your answer