Azure Data Factory : Source data set has a stored procedure with user-defined Table Type

Question

I have a stored procedure, which accepts the user-defined Table type as a parameter. I want to use this SP in the source data set in Azure data factory's copy data activity. I have seen examples where we can use user-defined Table type in the sink data set but not with the source data set.

Is it possible to execute an SP with user-defined Table type as parameter, to get the data out of the SQL and copy it over blob storage?

Here is my SP:

CREATE PROCEDURE [export].[up_get_qnxt_enrollment_date]
      @effectiveDate DATETIME
     , @lobPlanDimIDs export.intList READONLY
AS
BEGIN
 ......
END

The export.intList is the user defined Table type:

CREATE TYPE [export].[intList] AS TABLE(
    [Id] [int] NULL,
    [Name] [varchar(256)] NULL
)

I am not able to set this parameter in the Azure DF's source data set. I tried setting this a JSON array, but with no luck:

"typeProperties": {
                    "source": {
                        "type": "AzureSqlSource",
                        "sqlReaderStoredProcedureName": "[export].[up_get_qnxt_enrollment_date]",
                        "storedProcedureParameters": {
                            "effectiveDate": {
                                "type": "DateTime",
                                "value": "2004-01-01"
                            },
                            "lobPlanDimIDs": {
                                "type": "export.intList",
                                "value": [
                                    {
                                        "Id": {   "type": "int",  "value": 1   },
                                        "Name": {  "type":  "String", "value": "ABC" }
                                    },
                                    {
                                        "Id": {   "type": "int",  "value": 2   },
                                        "Name": {  "type":  "String", "value": "DEF" }
                                    }
                                ]
                            }
                        },
                        "queryTimeout": "02:00:00"

Is there anything I am missing or this functionality is not available in Azure DF yet?

Update: As per Martin Esteban Zurita's suggestion, I have used a query instead of SP. This is a perfectly fine workaround. I have also created a feature request: Support for User-defined table in Azure DF for Source

Please upvote if you need this functionality.

Answer 1

For a more configuration based approach, you can check this blog entry from pragmatic works: https://blog.pragmaticworks.com/using-stored-procedure-in-azure-data-factory

When I faced this issue, the way I've solved it was by using query instead of using stored procedure

And then call the query like you would call it from the sql command line, for example:

DECLARE @tmp DATETIME
SET @tmp = GETDATE()
DECLARE @lob AS intList
insert into @lob (Id, Name) select 1, 'ABC'
insert into @lob (Id, Name) select 2, 'DEF'
EXEC [up_get_qnxt_enrollment_date] @tmp, @lob

Hope this helped!