CODEWITHSUNDEEP
sqlazure-data-factory
user989988
user989988

Reputation: 3736

sql query using script activity in ADF pipeline

I have a copy data activity in the ADF pipeline that copies data from blob to sql table.

enter image description here

I use a script activity in order to create an index using a sql query CREATE CLUSTERED INDEX IDX_ReportsToPersonnelNbr ON TableName(ReportsToPersonnelNbr) after creating this table via ADF pipeline.

How do I pass the table name from the previous activity to script activity? This is what I tried which gives me the error:

enter image description here enter image description here

Upvotes: 1

Views: 2051

Answers (2)

lachicazul
lachicazul

Reputation: 103

First, I'd select Non Query option, instead of Query.

I don't know how you get there, but I use a for-each activity to do bulk inserts and first I drop the index, then I do the insert and then I recreate the index.

I use this:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_@{item().TargetSchemaName}_@{item().TargetTableName} ON @{item().TargetSchemaName}.@{item().TargetTableName};

item() contains the result of every loop iteration (schemaName and tableName)

Upvotes: 1

codeulike
codeulike

Reputation: 23064

You can't really get the tablename out of the Copy Data activity. But what you can do is use a variable to generate your tablename before the Copy Data activity, and then use that variable in both the Copy Data activity and the Script activity:

Like this:

  • Create a Pipeline Variable
  • Add a Set Variable activity before the Copy Data activity and use that to do the tbl@{replace(pipeline().RunId,'-','')} expression evaluation
  • Then use that variable in the Copy Activity as the table name @variables('yourVariableName')
  • then use that same variable in the Script Activity when you do the index CREATE CLUSTERED INDEX IDX_Whatever ON @{variables('yourVariableName')}

Upvotes: 1

Related Questions