question

JohnConnor-1052 avatar image
0 Votes"
JohnConnor-1052 asked JohnConnor-1052 answered

Use stored procedure at sink with multiple input tables using metadata driven copy task

I have successfully followed the below article to run a stored procedure before my data is stored in the destination database (Azure SQL).
https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory#invoke-a-stored-procedure-from-a-sql-sink

However I did my test only with one table, while the metadata driven copy task is built for many tables (that is why I use it) and in the sink properties of the copy task I can only specify one stored procedure and related to it the table type and the table type parameter name.

So when I have 2 tables to be processed how can I make the stored procedure dynamic so that it can handle different input tables? Since I have to specify a table type I cannot think of a way on how to make this dynamic so that it works for 2 different table types and in the end also to run the stored procedure depending on the kind of input table!

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @johnconnor-1052,

Thanks for using Microsoft Q&A !!
I do not think it is possible but I am checking it internally and get back to you in case any solutions.

Thanks
Saurabh

0 Votes 0 ·

Hi, thanks for checking. I found a SO thread for the same problem, but it does not provide a solution https://stackoverflow.com/questions/51711240/how-to-upsert-insert-records-in-all-tables-in-an-azure-sql-database-with-azure-d

I did not mention that what I want to achieve is the UPSERT behaviour in the destination database, but I think it is not really relevant for the question. It is a pity that although the metadata driven copy task provides the delta option to load only new data from the source table, that it just appends this delta data to the destination table. It is not useful if rows in the original table changed, because in the end you receive 2 entries in the destination table, although there was only a change in a single entry in the source table.

0 Votes 0 ·

1 Answer

JohnConnor-1052 avatar image
0 Votes"
JohnConnor-1052 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.