The crazy time of the year again

Its that time of the year again. This year has been really fruitful both on a personal and a professional front. My dad was diagnosed with colorectal cancer last year and this year he finally finished up with his surgery and chemo therapy and is much better. On a professional front I have moved back into a new contract role where I am helping australian farmers access premium export markets faster Ive worked on many technologies this year and love learning and experimenting with new things.I want to try to commit doing more writing couple of times a week on my blog. I think the protégé effect will help me learn more deeply and further my career goals quicker. For 2023 my goals will be to

participate in more hackathons

More opensource projects

Write more

As always learn and grow

As always take care and thanks for visiting my blog

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]