question

fry319-5415 avatar image
0 Votes"
fry319-5415 asked AnnuKumari-MSFT commented

Azure data factory insert script : Must declare the table variable

Hello,

I am currently working on a script able to insert one single line of data from collected variables during the pipeline execution.

The problem is i keep running into a "Must declare the table variable "@CONNECTOR_DB"." error and i cant find how to solve it.

The script i'm trying to run is a very simple Insert :

INSERT INTO @CONNECTOR_DB
VALUES(@PIPELINE_ID,@IMPORT_DATE,@NB_ROWS_READ,@NB_ROWS_WRITTEN,@NB_ERROR_ROWS,@COMMENT,@FILE_NAME)

Where @CONNECTOR_DB is @{pipeline().parameters.connectorDb}.T_ImportHistory_IH

194673-image.png




I've tried other ways to enter the connectorDb but i keep running into syntax errors or "unexpected character ." and the likes.

Please advise.

I also want to apologize, english is not my first language.

Have a nice day.

azure-data-factoryazure-sql-database
image.png (30.5 KiB)
· 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.

Hi @fry319-5415 ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

0 Votes 0 ·

Hi @fry319-5415 ,
Just following up to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

0 Votes 0 ·
PratikSomaiya avatar image
1 Vote"
PratikSomaiya answered fry319-5415 commented

Hello @fry319-5415

Can you store the metadata in a file in data lake and use a Lookup activity and pass the metadata to the foreach activity

This way you can pass the parameters and execute the pipeline

Does the connectorDB parameter having any value or is it an empty string?

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

Thank you for the fast answer.

Here is the input of the script activity. ConnectorDB seems to have value.

Input
{
"scripts": [
{
"parameters": [
{
"name": "CONNECTOR_DB",
"type": "String",
"value": "q2n200.T_ImportHistory_IH",
"direction": "Input"
},
{
"name": "PIPELINE_ID",
"type": "String",
"value": "2e185fac-5fa4-4d8c-b43b-83c642ae2e96",
"direction": "Input"
},
{
"name": "IMPORT_DATE",
"type": "Datetime",
"value": "2022-04-19T17:48:53.0496591Z",
"direction": "Input"
},
{
"name": "NB_ROWS_WRITTEN",
"type": "String",
"value": "7",
"direction": "Input"
},
{
"name": "NB_ROWS_READ",
"type": "String",
"value": null,
"direction": "Input"
},
{
"name": "NB_ERROR_ROWS",
"type": "String",
"value": null,
"direction": "Input"
},
{
"name": "COMMENT",
"type": "String",
"value": null,
"direction": "Input"
},
{
"name": "FILE_NAME",
"type": "String",
"value": [
{
"name": "SAP-Q2N_200_SALES_ORDER_SO_LIST_FTP_INCR_ALL_20210927030010.txt",
"type": "File"
}
],
"direction": "Input"
}
],
"type": "NonQuery",
"text": "INSERT INTO @CONNECTOR_DB\nVALUES(@PIPELINE_ID,@IMPORT_DATE,@NB_ROWS_READ,@NB_ROWS_WRITTEN,@NB_ERROR_ROWS,@COMMENT,@FILE_NAME)"
}
]
}

I unfortunately dont know how to pass the metadata to the script activity.

The data copy of the pipeline is in the IF statement there.

194687-image.png


0 Votes 0 ·
image.png (23.1 KiB)

hello I answered you down there

0 Votes 0 ·
AnnuKumari-MSFT avatar image
0 Votes"
AnnuKumari-MSFT answered AnnuKumari-MSFT commented

Hi @fry319-5415 ,

Welcome to Microsoft Q&A platform and thankyou for posting your query.

As I understand your query here, you are trying to make use of Script activity by using script parameters but it's failing with the error: "Must declare the table variable ".

TableName cannot be passed as a parameter.

The logic behind this is same as how you write the SQL query where you can make use of input and output parameters , however, tableName can't be parameterized.

196362-image.png

196373-image.png

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
    Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators



image.png (51.6 KiB)
image.png (46.8 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.

Hi @fry319-5415 ,
Just checking in to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer and kindly upvote the same. If you have extra questions about this answer, please do Comment.

0 Votes 0 ·

Hi @fry319-5415 ,
Just following up to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer and kindly upvote the same.

0 Votes 0 ·

hello, sorry, i somehow completely missed your answezr im so sorry.

The problem is the pipeline im using is shared between multiple database instances and i have to insert the import history depending on the instance

For example in the call i pasted ab ove CONNECTORDB was

"name": "CONNECTOR_DB",
"type": "String",
"value": "q2n200.T_ImportHistory_IH",

Where q2n200 is the instance and i got like 8 different possible instances

Im more and more thinking that the script activity is just not appropriate for my needs, iI need to insert a single line , Do you think it may be easier to use a data flow ? to insert a single line ?

0 Votes 0 ·
Show more comments