Azure data factory copy with stored procedures

I have used Azure Data Factory a lot and also the copy activity. For a particular usecase recently I had to use a stored procedure as a sink. This was because we needed to add some business logic before the data was written to the Azure SQL DB To do this we will need to set up the following

• User defined table type. This is what the source is mapped to and will be a parameter for the stored procedure
• Stored procedure in SQL DB
• Sink data set to point to the database required

The stored procedure in the sink step of copy activity takes as input the user defined table type . User-defined table types are the predefined tables . The schema definition is created by the users In our case these hold temporary data that is sent from the copy activity source .




In my stored proc I insert into a new table after masking some columns.We can pretty much add any SQL Functions into this stored procedure.

To run the data pipeline we need to give execute privileges to the ADF to run Stored procedures and access user defined table types using GRANT EXEC ON TYPE::[schema].[typename] to [User]
and GRANT EXEC ON spname to [User]