question

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

Copy data Activity

Hello Team,

I am running the script in query :


79247-image.png

Query :

@{pipeline().parameters.SelectClause}
from @{pipeline().parameters.Schema}.[@{pipeline().parameters.Companies[0]}$@{pipeline().parameters.TablesWithCompany[0]}]

@{pipeline().parameters.Companies[0] is in array
@{pipeline().parameters.TablesWithCompany[0]} is in array

I am getting error. Whether i am passing the parameter values wrong. Please advise


Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid object name 'pool.VendorsV2'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'pool.VendorsV2'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=208,State=1,Message=Invalid object name 'pool.VendorsV2'.,},],'


azure-data-factory
image.png (58.8 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.

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered RohitKulkarni-3496 commented

Hello @RohitKulkarni-3496 ,
Thanks for the ask and using the Microsoft Q&A platform .

As I understand the intention here is to get a query like

Select a,b,c from dbo.company1TablesWithCompany1, if yes please try and use the expression

 @{pipeline().parameters.SelectClause}
 from @{pipeline().parameters.Schema}.@{pipeline().parameters.Companies[0]}@{pipeline().parameters.TablesWithCompany[0]}


I also suggest that you use the set variable activity to test this kind of query , I have found that to be very helpful . The animation below will make my implementation clear and also how I am using the set variable activity .

79710-333.gif


Please do let me know how it goes .
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members



333.gif (860.4 KiB)
· 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.

sorry the query i used it gave me error :



A database operation failed with the following error: 'Incorrect syntax near the keyword 'from'.'
Incorrect syntax near the keyword 'from'., SqlErrorNumber=156,Class=15,State=1,
.

0 Votes 0 ·

If i use this query

@{pipeline().parameters.SelectClause}
from @{pipeline().parameters.Schema}.[@{pipeline().parameters.Companies}]$[@{pipeline().parameters.TablesWithCompany}]
@{pipeline().parameters.Companies}=Group
@{pipeline().parameters.TablesWithCompany=Vendor

Please advise

I am getting this error

A database operation failed with the following error: 'Incorrect syntax near the keyword 'from'.
Unclosed quotation mark after the character string '["Group "]$[["Vendor"]'.'
Incorrect syntax near the keyword 'from'.
Unclosed quotation mark after the character string '["Group O"]$[["Vendor"]'.,

0 Votes 0 ·

the query has to be

select a,b,c from Group$Vendor

0 Votes 0 ·

output :

 "errors": [
     {
         "Code": 22301,
         "Message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near the keyword 'from'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near the keyword 'from'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=156,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=156,State=1,Message=Incorrect syntax near the keyword 'from'.,},],'",
0 Votes 0 ·

Inout :

"source": {
"type": "SqlServerSource",
"additionalColumns": [
{
"name": "Companies",
"value": "[\"Group O\"]"
}
],
"sqlReaderQuery": "\nfrom dbo.[\"Group O\"]$[\"Vendor\"]",
"queryTimeout": "20:00:00",
"isolationLevel": "ReadUncommitted",
"partitionOption": "None"
},

0 Votes 0 ·

After lot of modification in the query i came to near but still have some issue :
I am looking in this format :dbo.Group O$Vendor.But it is appaearing in different format dbo.Group O$["Vendor"]
query :

@{pipeline().parameters.SelectClause}
from [@{pipeline().parameters.Schema}.@{pipeline().parameters.Companies[0]}$@{pipeline().parameters.TablesWithCompany}

Companies and Tablewith company are in array

Please advise
79767-image.png


0 Votes 0 ·
image.png (35.7 KiB)
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered MartinJaffer-MSFT commented

Hello @RohitKulkarni-3496 ,

Since you shared the query , I think it gave a little bit more idea of the issue . If you see there is space in between Group and O .

try the below expressiona nd it should work .


@{pipeline().parameters.SelectClause}
from @{pipeline().parameters.Schema}.[@{pipeline().parameters.Companies[0]}$@{pipeline().parameters.TablesWithCompany[0]}]

Output :

{
"name": "queryOutput",
"value": "Select a,b,c\nfrom [dbo.Group O$Vendor]"
}


Please do let me know how it goes .
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members



· 2
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.

There is a space between Group and O. And I found where it was wrong. In 2 pipeline ,In one pipeline it was passing string and in another one it was passing array. I have converted the one pipeline from string to array. It got worked

Thanks for advise

0 Votes 0 ·

Wow this was a tough one. I'm happy you managed to solve the problem. Could you please mark as accepted answer? Thank you, @RohitKulkarni-3496 .

0 Votes 0 ·