none
Copy activity with Sink stored procedure fails RRS feed

  • Question

  • I am trying to copy data from a Azure SQL DB to a on-prem SQL DB with the copy Activity and a Sink stored procedure. I can successfully copy data doing a standard copy activity without the stored proc so it isnt a connection issue.

    But When I try with a stored proc it fails. I dont believe it has anything to do with the SQL table type in SQL as I removed it and the same error happened.

    The error in SQL Profiler is: Error: 102, Severity: 15, State: 1 Incorrect syntax near ' '.

    It also has this line recorded: declare @a ExportType select * from @a which fails if I run this line

    Here is the Stored Proc

    ALTER PROCEDURE [dbo].[spExport] @CRM [dbo].[ExportType] READONLY
    AS
    BEGIN
        insert into CRM
        select upper(name) from @CRM    
    END
    

    Here is the Table Type

    CREATE TYPE [dbo].[ExportType] AS TABLE(
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [name] [nvarchar](50) NULL
    )
    GO
    

    Here is the config below.

    {
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Pipeline.json",
    "name": "CopyToLocal",
    "properties": {
        "description": "copy from Azure sql to local sql",
        "activities": [{
                "name": "Copy to local with sproc",
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "SqlSource"
                    },
                    "sink": {
                        "type": "SqlSink",
                        "SqlWriterTableType": "ExportType",
                        "sqlWriterStoredProcedureName": "spExport"
                    }
                },
                "inputs": [{
                        "name": "SourceDataset"
                    }
                ],
                "outputs": [{
                        "name": "SprocDataset"
                    }
                ],
                "policy": {
                    "concurrency": 10,
                    "executionPriorityOrder": "OldestFirst",
                    "retry": 3,
                    "timeout": "00:05:00"
                },          
               "scheduler": {
                    "frequency": "Minute",
                    "interval": 20
                }
            }
        ],
        "start": "2017-08-02T08:45:00Z",
        "end": "2019-05-11T00:00:00Z"
    }

    Thursday, September 14, 2017 11:45 PM

All replies

  • here is a similar thread with a very similar code which seems to have worked: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e71dfed1-f810-4799-bc80-afe52d14e927/how-to-use-sqlwriterstoredprocedurename-and-sqlwritertabletype-in-adf-as-sql-sink?forum=AzureDataFactory

    may you also supply your actual error message?

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, September 15, 2017 6:56 AM
  • Hi,

    The ARM template seems to be OK. You still need a table where the data is inserted (You mentioned that you removed it).

    It looks to my that your stored procedure is incorrect. I would try to explicitly defined the schema name and the columns. So something like:

    insert into [dbo].[CRM] (name) select upper(name) from @CRM


    if that is not working, you can try with cursors. The advantage of cursors is that you can easily add custom behavior on each record (e.g. check for existence, insert into multiple tables, ...)

    DECLARE @name NVARCHAR(MAX);

    DECLARE tableListCursor FOR SELECT upper(name) from @CRM;

    OPEN tableListCursor;

    FETCH NEXT FROM tableListCursor INTO @name;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    INSERT INTO [dbo].[CRM] (name) VALUES (@name)

    FETCH NEXT FROM tableListCursor INTO @name;

    END

    Cheers,

    Tom


    Have a look at my mobile games on http://www.blugri.com

    Friday, September 15, 2017 9:05 AM