question

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 asked KranthiPakala-MSFT commented

Parameter Null

Hello team,

The pipeline has Comapnies and Tablewith company as a parameter if we pass for whole companies and Tablewithcompany it is taking more than 10 hrs to excute the pipeling so we planned to pass only prmutation and combination like

  1. 1 companies and 1 Tablewithcompany : It is working perfectly

  2. 1 companies and Tablewithcompany is null : it is not working properly .

I am passing the parameter value :

80610-image.png

80609-image.png

I am passing these two parameter
companies :["Group"]
TablesWithCompany :[]

It is not working properly because i have parameterized query where it will fetch like

select * from dbo.Group$[]

I am expecting like to fetch the query :
select from dbo.Group$Vendor
select
from dbo.Group$GL_Account (It must take all the tablewithcompany data)
The query:

if ISNULL( ('@{ if(lessOrEquals(length(pipeline().parameters.TablesWithCompany),1),
join(pipeline().parameters.TablesWithCompany,''),
concat(join(pipeline().parameters.TablesWithCompany,''','''))
)}'),'')<> ' '
Begin
select DISTINCT RIGHT(t.TABLE_NAME,LEN(t.TABLE_NAME) -CHARINDEX('$',t.TABLE_NAME)) AS TableName
from INFORMATION_SCHEMA.TABLES t
where t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME like '%$%'
AND t.TABLE_NAME not like '%$%$%'
AND t.TABLE_NAME not like '%Bak'
AND t.TABLE_NAME not like '%
%1%'
AND t.TABLE_NAME not like '%%2%'
AND RIGHT(t.TABLE_NAME,LEN(t.TABLE_NAME) -CHARINDEX('$',t.TABLE_NAME))
IN ('@{ if(lessOrEquals(length(pipeline().parameters.TablesWithCompany),1),
join(pipeline().parameters.TablesWithCompany,''),
concat(join(pipeline().parameters.TablesWithCompany,''','''))
)}')
End
ELSE
Begin
select DISTINCT RIGHT(t.TABLE_NAME,LEN(t.TABLE_NAME) -CHARINDEX('$',t.TABLE_NAME)) AS TableName
from INFORMATION_SCHEMA.TABLES t
where t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME like '%$%'
AND t.TABLE_NAME not like '%$%$%'
AND t.TABLE_NAME not like '%Bak'
AND t.TABLE_NAME not like '%
%1%'
AND t.TABLE_NAME not like '%
%2%'
AND LEFT(t.TABLE_NAME,CHARINDEX('$',t.TABLE_NAME) - 1) <> ''
END
--AND RIGHT(t.TABLE_NAME,LEN(t.TABLE_NAME) -CHARINDEX('$',t.TABLE_NAME)) IN ('Value Entry')



azure-data-factory
image.png (28.1 KiB)
image.png (32.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered KranthiPakala-MSFT commented

Hey you can try this:
@not(equals(coalesce(<<parameter>>, ' '), ' ')) to validate whether the parameter is null

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey @RohitKulkarni-3496 ,
where you able to resolve the issue?

0 Votes 0 ·

Still working on this

0 Votes 0 ·

In Get meta data there are 3 parameters

Container :@pipeline().parameters.RawContainer
Folder :@{pipeline().parameters.RawFolder}/@{pipeline().parameters.Companies[0]}/Tables/@{pipeline().parameters.TablesWithCompany[0]}

FileName :@{pipeline().parameters.TablesWithCompany[0]}_@{pipeline().parameters.Companies[0]}.@{pipeline().parameters.RawFileFormat}

Values for Parameter
@{pipeline().parameters.RawFolder} : GOP/Tables/ (There are multiple Table name folders are present)
@{pipeline().parameters.Companies[0]} : ABC
@{pipeline().parameters.TablesWithCompany[0]}: I am passing Null(So it has to fetch all the Table Name folder for specific companies )


80968-image.png


Meta data Input :

81044-image.png


Normally it as to fetch all the tables Name folder under this path "Folder": "GPO Corp 2017/Tables/",

0 Votes 0 ·
image.png (25.0 KiB)
image.png (21.6 KiB)

Hello Team,

I have lookup and For each activity.
81398-image.png


Under each activity

81399-image.png


In "Get Metdata of file" activity I need to pass the values of "LookupTables stagingZone " activity

Get Metdata activty I am passing the parameter :

81366-image.png

FolderName :@{pipeline().parameters.RawFolder}/@{pipeline().parameters.Companies[0]}/Tables/@{item().value}

Please let me know the correct syntax.



0 Votes 0 ·
image.png (24.7 KiB)
image.png (23.4 KiB)
image.png (66.1 KiB)

Hi @RohitKulkarni-3496 ,

Do you still need assistance on this ask? I see a similar query was asked in this thread but later it was closed - https://docs.microsoft.com/en-us/answers/questions/331110/index.html.

Thanks

0 Votes 0 ·
Show more comments