Azure Data Factory V2: How to pass a file name to stored procedure variable

user_8997728 0
Viewed 2508

I have a big fact Azure SQL table with the following structure:

Company     Revenue
-------------------
A              100
B              200
C              100
.               .
.               .
.               .

I am now building a stored procedure on Azure Data Factory V2 that will delete all records of a special company from the Azure SQL fact table above in a monthly basis. For this exercise this special company shall be identified by the variable @company. The structure of the stored procedure was created as: 

@company NVARCHAR(5)

DELETE FROM table 
WHERE [company] = @company

As I will have different Excel files from each company that will be inserting data into this table in a monthly basis (with Copy Activity), I want to use the stored procedure above to delete the old data from that company before I add the most updated one.

I would like then to pass to the variable "@company" the name of that Excel file (stored in a blob container) so that the stored procedure knows what is the relevant data to be deleted from the fact table. For example: If the Excel file is "A", the stored procedure shall be "delete from table where company = A".

Any ideas on how to pass the Excel file names to the variable "@company" and set this up on Azure Data Factory V2?

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

Any ideas on how to pass the Excel file names to the variable "@company" and set this up on Azure Data Factory V2?

Based on your description, I found Event-based trigger in azure data factory maybe will meet your needs. Event-based trigger runs pipelines in response to an event, such as the arrival of a file, or the deletion of a file, in Azure Blob Storage.

So, when the new excel file created in the blob storage (BTW, it only supports V2 storage account,more details please refer to this article), you could get the @triggerBody().folderPath and @triggerBody().fileName. To use the values of these properties in a pipeline, you must map the properties to pipeline parameters. After mapping the properties to parameters, you can access the values captured by the trigger through the @pipeline.parameters.parameterName expression throughout the pipeline. (doc)

You could get the filename and pass it into your stored procedure. Then do the deletion and copy activities.

user_81989460

I you use the stored procedure activity you can fill the parameters from pipeline parameters: https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure

ADF stored procedures

You can click on "Import" to automatically add the parameters after selecting a stored procedure or add them by hand. Then you can use a pipeline expression to dynamically fill them, e.g. as suggested by Jay Gong using the trigger parameters @triggerBody().folderPath and @triggerBody().fileName.

Alternatively, instead of using a stored procedure, you could add a pre-copy script to your copy activity:

Pre-copy script

This shows up only for appropriate sinks, such as a database table. You can fill this script dynamically as well. In your case, this could look like:

@{concat('DELETE FROM table 
WHERE [company] = ''',triggerBody().fileName,'''')}

It might also make sense, to add a parameter to the pipeline containing the filename, and setting it to @triggerBody().fileName or any more complex expression, in case you are using it multiple times.

user_92199700
Related