locked
How to use SqlWriterStoredProcedureName and SqlWriterTableType in ADF as SQL sink? RRS feed

  • Question

  • Hi,

    I have done a POC to copy data from on premises sql tables to azure sql database table using copy activity (by using sqlReaderQuery as sql source). I need to call a stored procedure for inserting the data to destination azure sql db table. There is a property 'SqlWriterStoredProcedureName' for calling sp. Can you please help me with the output of the sqlReaderQuery as input to the stored procedure? There is no sample available which explains how the stored procedure should be and what is the purpose of 'SqlWriterTableType'.

    Friday, December 5, 2014 10:38 AM

Answers

  • Hi Anna,

    Thanks for your question. To call a stored procedure for inserting data, both SqlWriterStoredProcedureName and SqlWriterTableType are needed. Here is a sample to illustrate how to use it.

    1. The Json of the Activity is like this:

       "sink":
       {
        "type": "SqlSink",
        "SqlWriterTableType": "MarketingType",
        "SqlWriterStoredProcedureName": "spOverwriteMarketing"
       } 

    2. The Json of the target Table is like this:

            "location":
            {
                "type": "AzureSqlTableLocation",
                "tableName": "Marketing",
                "linkedServiceName": "AzureSqlLinkedService"
            },

    3. The stored procedure is defined like this. It handles input data from your provided sqlReaderQuery, and insert into the target table. Notice that the parameter name of the sp should be same as the tableName defined in Table Json file.

        CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY
        AS
        BEGIN
           INSERT [dbo].[Marketing](ProfileID, State)
           SELECT * FROM @Marketing
        END

    4. The table type is defined like this. Notice that the schema of the table type should be same as the schema returned by your provided sqlReaderQuery.

        CREATE TYPE [dbo].[MarketingType] AS TABLE(
            [ProfileID] [varchar](256) NOT NULL,
            [State] [varchar](256) NOT NULL,
        )

    The stored procedure feature takes advantage of Table-Valued Parameters. You can learn more information about Table-Valued Parameters from this link http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

    Wednesday, December 10, 2014 9:39 AM

All replies

  • Hi Anna,

    Thanks for your question. To call a stored procedure for inserting data, both SqlWriterStoredProcedureName and SqlWriterTableType are needed. Here is a sample to illustrate how to use it.

    1. The Json of the Activity is like this:

       "sink":
       {
        "type": "SqlSink",
        "SqlWriterTableType": "MarketingType",
        "SqlWriterStoredProcedureName": "spOverwriteMarketing"
       } 

    2. The Json of the target Table is like this:

            "location":
            {
                "type": "AzureSqlTableLocation",
                "tableName": "Marketing",
                "linkedServiceName": "AzureSqlLinkedService"
            },

    3. The stored procedure is defined like this. It handles input data from your provided sqlReaderQuery, and insert into the target table. Notice that the parameter name of the sp should be same as the tableName defined in Table Json file.

        CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY
        AS
        BEGIN
           INSERT [dbo].[Marketing](ProfileID, State)
           SELECT * FROM @Marketing
        END

    4. The table type is defined like this. Notice that the schema of the table type should be same as the schema returned by your provided sqlReaderQuery.

        CREATE TYPE [dbo].[MarketingType] AS TABLE(
            [ProfileID] [varchar](256) NOT NULL,
            [State] [varchar](256) NOT NULL,
        )

    The stored procedure feature takes advantage of Table-Valued Parameters. You can learn more information about Table-Valued Parameters from this link http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

    Wednesday, December 10, 2014 9:39 AM
  • Hi there,

    This example is copying data to a azure sql database. I'm trying to use a sql server on premises, but it's not working.. is this feature only available for azure databases??

    Thanks!!


    Wednesday, July 15, 2015 2:26 PM
  • Hi Nelson,

    This feature works for both Azure DB and SQL Server. Could you share more about the issue you met? And it would be better if you can provide the runId and timestamp for your failed run.

    Friday, July 17, 2015 5:36 AM
  • Hi there,

    I am attempting to populate from my on prem SQL to Azure SQL using Data Factory and this works fine if I use a standard SELECT statement.  However what I want to do is use a stored proc to prevent entering duplicates into my target table, i.e. to be able to do incremental updates.

    So I have defined:

    • A SQL table type in SQL Azure
    • A SP that uses that type in SQL Azure

    Then using the following JSON to call it:

    {

        "name": "LoadDatePipeline",

        "properties": {

            "description": "This pipeline has one Copy activity that copies data from an on-prem SQL to SQL Azure table",

            "activities": [

                {

                    "type": "SqlServerStoredProcedure",

                    "typeProperties": {

                        "storedProcedureName": "[load].[PopulateDate]",

                        "storedProcedureParameters": {

                            "source": "LoadDateOnPremSQLTable"

                        }

                    },

                    "inputs": [

                        {

                            "name": "LoadDateOnPremSQLTable"

                        }

                    ],

                    "outputs": [

                        {

                            "name": "LoadDateAzureSQLTable"

                        }

                    ],

                    "policy": {

                        "timeout": "01:00:00",

                        "concurrency": 1,

                        "retry": 3

                    },

                    "scheduler": {

                        "frequency": "Hour",

                        "interval": 1

                    },

                    "name": "LoadDatePipeline"

                }

            ],

            "start": "2015-08-02T00:00:00Z",

            "end": "2015-08-05T00:00:00Z",

            "isPaused": false,

            "hubName": "dflottoresults_hub"

        }

    }

    But it keeps erroring out telling me:

    Database operation failed. Error message from database execution : The procedure "PopulateDate" has no parameter named "@load.Date"..

    This is the definition of the SP and the table type reference:

    CREATE TYPE [reference].[DateKeys] AS TABLE(

           [DateKey] [int] NULL,

           [ActualDate] [datetime] NULL,

           [ShortDate] [datetime] NULL,

           [DayOfWeek] [int] NULL,

           [CalendarDay] [int] NULL,

           [DayNumberOverall] [int] NULL,

           [DayName] [nvarchar](255) NULL,

           [DayAbbreviatedName] [nvarchar](255) NULL,

           [MaoriDayName] [nvarchar](255) NULL,

           [IsWeekDay] [nvarchar](255) NULL,

           [WeekNumberInYear] [int] NULL,

           [WeekNumberOverall] [int] NULL,

           [LongDisplayDate] [datetime] NULL,

           [WeekBeginDateKey] [int] NULL,

           [MonthNumber] [int] NULL,

           [MonthNumberOverall] [int] NULL,

           [MonthName] [nvarchar](255) NULL,

           [MonthAbbreviatedName] [nvarchar](255) NULL,

           [MaoriMonthName] [nvarchar](255) NULL,

           [CalendarQuarterNumber] [int] NULL,

           [CalendarQuarterName] [nvarchar](255) NULL,

           [CalendarQuarterAbbreviatedName] [nvarchar](255) NULL,

           [CalendarYear] [int] NULL,

           [YearMonth] [int] NULL,

           [MonthNameYear] [nvarchar](255) NULL,

           [FinancialMonth] [int] NULL,

           [FinancialQuarter] [int] NULL,

           [FinancialYear] [int] NULL,

           [FinancialQuarterAbbreviatedName] [nvarchar](255) NULL,

           [FinancialQuarterName] [nvarchar](255) NULL,

           [LastDayInMonth] [nvarchar](255) NULL,

           [DaylightSavingsTime] [nvarchar](255) NULL,

           [NationalHolidayFlag] [nvarchar](255) NULL,

           [NationalHolidaysDescription] [nvarchar](255) NULL,

           [RegionalHolidaysFlag] [nvarchar](255) NULL,

           [RegionalHolidaysDescription] [nvarchar](255) NULL,

           [WeekBeginDate] [datetime] NULL,

           [SameDayYearAgo] [datetime] NULL

    )

    GO

    CREATE PROCEDURE [load].[PopulateDate] (@date [reference].[DateKeys] READONLY)

    AS

    BEGIN

        INSERT INTO [load].[Date]

        SELECT

                  [DateKey],

                  [ActualDate],

                  [ShortDate],

                  [DayOfWeek],

                  [CalendarDay],

                  [DayNumberOverall],

                  [DayName],

                  [DayAbbreviatedName],

                  [MaoriDayName],

                  [IsWeekDay],

                  [WeekNumberInYear],

                  [WeekNumberOverall],

                  [LongDisplayDate],

                  [WeekBeginDateKey],

                  [MonthNumber],

                  [MonthNumberOverall],

                  [MonthName],

                  [MonthAbbreviatedName],

                  [MaoriMonthName],

                  [CalendarQuarterNumber],

                  [CalendarQuarterName],

                  [CalendarQuarterAbbreviatedName],

                  [CalendarYear],

                  [YearMonth],

                  [MonthNameYear],

                  [FinancialMonth],

                  [FinancialQuarter],

                  [FinancialYear],

                  [FinancialQuarterAbbreviatedName],

                  [FinancialQuarterName],

                  [LastDayInMonth],

                  [DaylightSavingsTime],

                  [NationalHolidayFlag],

                  [NationalHolidaysDescription],

                  [RegionalHolidaysFlag],

                  [RegionalHolidaysDescription],

                  [WeekBeginDate],

                  [SameDayYearAgo]    

           FROM @date

           WHERE DateKey NOT IN (SELECT DISTINCT DateKey FROM [load].[Date])

    END

    And this is the defined datasets I have:

    {
        "name": "LoadDateAzureSQLTable",
        "properties": {
            "published": false,
            "type": "AzureSqlTable",
            "linkedServiceName": "AzureSqlLottoResults",
            "typeProperties": {
                "tableName": "load.Date"
            },
            "availability": {
                "frequency": "Hour",
                "interval": 1
            }
        }
    }

    And

    {
        "name": "LoadDateOnPremSQLTable",
        "properties": {
            "published": false,
            "type": "SqlServerTable",
            "linkedServiceName": "ProphesyDateDimension",
            "typeProperties": {
                "tableName": "load.Date"
            },
            "availability": {
                "frequency": "Hour",
                "interval": 1
            },
            "policy": {
                "externalData": {
                    "retryInterval": "00:01:00",
                    "retryTimeout": "00:10:00",
                    "maximumRetry": 3
                }
            }
        }
    }

    Appreciate any help.

    Thanks,

    Rob


    MS SQL Guy

    Tuesday, August 4, 2015 1:49 AM
  • Hi Rob,

    From your Activity setting "type": "SqlServerStoredProcedure", you are using the stored procedure activity instead of the Copy Activity. For more details, this doc introduces the stored procedure activity.

    If you want to use the above example in this post, you need to set the activity type as "CopyActivity" to leverage the Copy Activity feature. Refer to this doc about more informations.

    Thanks,

    Yingqin

    Tuesday, August 4, 2015 7:16 AM
  • Thanks Yingqin, even with you change I am still having the same error message returned. 

    MS SQL Guy

    Wednesday, August 5, 2015 3:05 AM
  • Hi Rob,

    Please notice the #3 statement in the above example "Notice that the parameter name of the sp should be same as the tableName defined in Table Json file". You set the table name as "load.Date", but the parameter name of the sp is set as "date". Could you try correct the setting?

    If you still met issues, could you provide the runId and timestamp for your failed run? I can take a further look on it.

    Wednesday, August 5, 2015 7:32 AM
  • You can't create a param in sql with a . (period) in it.

    I'm facing the same issue using a custom schema.

    Is using a custom schema supported even?

    Wednesday, November 18, 2015 8:57 PM
  • If I have hundreds of tables to move then I do need to create those much of the Table Types. Is there any alternative way to bypass so many Table types
    Friday, June 24, 2016 1:51 PM
  • Hi,

    Did you manage to find a solution to this? Running into the same problem

    Monday, October 17, 2016 5:02 AM
  • Hello,

    Your output tablename should be the same name as the stored procedures TableType property. The output table name does not need to be a physical table. I found it easiest to replace the period with an underscore so Rob could have set his output table name to load_Date and the stored procedure param to load_Date.

    Monday, October 17, 2016 10:31 PM
  • Hi Anna,

    Thanks for your question. To call a stored procedure for inserting data, both SqlWriterStoredProcedureName and SqlWriterTableType are needed. Here is a sample to illustrate how to use it.

    1. The Json of the Activity is like this:

       "sink":
       {
        "type": "SqlSink",
        "SqlWriterTableType": "MarketingType",
        "SqlWriterStoredProcedureName": "spOverwriteMarketing"
       } 

    2. The Json of the target Table is like this:

            "location":
            {
                "type": "AzureSqlTableLocation",
                "tableName": "Marketing",
                "linkedServiceName": "AzureSqlLinkedService"
            },

    3. The stored procedure is defined like this. It handles input data from your provided sqlReaderQuery, and insert into the target table. Notice that the parameter name of the sp should be same as the tableName defined in Table Json file.

        CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY
        AS
        BEGIN
           INSERT [dbo].[Marketing](ProfileID, State)
           SELECT * FROM @Marketing
        END

    4. The table type is defined like this. Notice that the schema of the table type should be same as the schema returned by your provided sqlReaderQuery.

        CREATE TYPE [dbo].[MarketingType] AS TABLE(
            [ProfileID] [varchar](256) NOT NULL,
            [State] [varchar](256) NOT NULL,
        )


    thanks for this answer, I have a question though, how is the stored procedure going to work if there are different sql server schemas (different from dbo), given that "the parameter name of the sp should be same as the tableName defined in Table Json file" sp parameters cannot have special character

    the activity fails because it can't find the sp parameter admin.table_name, what would be the workaround?

    UPDATE: the answer to my question is here

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1cac2912-c8b6-4a78-98af-b8b5ce2109cb/sqlsink-in-copyactivity-azuresql-to-azuresql-with-custom-schema?forum=AzureDataFactory

    • Edited by saulcruz Monday, November 27, 2017 2:47 AM
    Monday, November 27, 2017 1:38 AM
  • What would be the syntax for multiple table types, where multiple inputs are manipulated in the stored procedure for a single output ?
    Monday, December 11, 2017 12:31 PM