sql query using script activity in ADF pipeline

user_3693060 0
Viewed 508

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

azure-data-factory-2 azure-data-factory sql
2 Answers

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)

user_55251820

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')}
user_221940
Related