Azure Data Factory Lookup Activity, Stored Procedure with Table Type parameter

0
votes

I'm setting up an Azure Data Factory to copy DB data between 2 Azure SQL Server.

I have a Stored Procedure at sink DB and SP has a user-defined table type parameter and some OUTPUT parameters.

I've succeeded with Copy Activity but found that OUTPUT parameters are not supported. (https://social.msdn.microsoft.com/Forums/azure/en-US/82e84ec4-fc40-4bd3-b6d5-b742f3cd1a33/adf-v2-how-to-check-if-stored-procedure-output-is-empty?forum=AzureDataFactory)

So I tried with two Lookup Activity, one is to select multiple rows from the table(maybe into my table type), another is to execute SP.

But when setting the second Lookup Activity, there's no Table Type parameter setting with Stored Procedure Parameter.

Pressing "Import parameter" also didn't show a table type parameter.

How can I pass a table type parameter from lookup activity 1 to the second?

1
azureazure-data-factory-2
If I understand, you want to take the result of the first activity and input to the second activity as a TVP to stored procedure? - Matt
@Matt Yes, exactly. - Winter Singha

1 Answers

1
votes

Table type is for sink stored procedure. For look up activity, the stored procedure is used to read data. So I believe table type is not required.

https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#azure-sql-database-as-the-source