locked
Azure SQL Copy activity with stored procedure & DocumentDB RRS feed

  • Question

  • Hello,

    Is it possible to use Azure SQL Copy activity with stored procedure that upserts(Update/Insert) data into the target table
    when copying data from  DocumentDB to  Azure database?

    Sunday, March 13, 2016 6:00 PM

All replies

  • Monday, March 14, 2016 12:06 PM
  • Thank you for answer.  I have written the example that does work fine without stored procedure properties. 
    Data reads from .json- files in DocumentDB and writes to SQL table. 
    But I need to  Update/Insert data in SQL table. 
    Would you look to this example and explain little more what is wrong and how must this work. 

    {
        "name": "OrderSQLTable",
        "properties": {
            "structure": [
                {
                    "name": "OrderId",
                    "type": "Int32"
                },
                {
                    "name": "TotalValue",
                    "type": "Decimal"
                },
                {
                    "name": "OrderStatus",
                    "type": "String"
                }
                
            ],
            "published": false,
            "type": "AzureSqlTable",
            "linkedServiceName": "AzureSqlLinkedService",
            "typeProperties": {
                "tableName": "Order"
            },
            "availability": {
                "frequency": "Day",
                "interval": 1
            }
        }
    }

    {
        "name": "DocDbToOrderSqlPipeline",
        "properties": {
            "activities": [
                {
                    "type": "Copy",
                    "typeProperties": {
                        "source": {
                            "type": "DocumentDbCollectionSource",
                            "query": "SELECT c.OrderId, c.TotalValue,  c.OrderStatus.OrderOrderStatus FROM c",
                            "nestingSeparator": "."
                        },
                        "sink": {
                            "type": "SqlSink",
                            "sqlWriterStoredProcedureName": "spUpdateOrder",
                            "sqlWriterTableType": "OrderType",
                            "writeBatchSize": 10000,
                            "writeBatchTimeout": "60.00:00:00"
                        }
                    },
                    "inputs": [
                        {
                            "name": "DocumentDbTable"
                        }
                    ],
                    "outputs": [
                        {
                            "name": "OrderSQLTable"
                        }

    ****************************

    CREATE PROCEDURE [dbo].[spUpdateOrder] @Order [dbo].[OrderType] READONLY
    AS
    BEGIN
    MERGE [dbo].[Order] AS D
                USING @Order AS S
                ON D.OrderId = S.OrderId
    WHEN MATCHED 
    THEN
                UPDATE SET
    D.TotalValue = S.TotalValue,
    D.OrderStatus = S.OrderStatus,

    WHEN NOT MATCHED
    THEN
                INSERT ([OrderId]
          ,[TotalValue]
          ,[OrderStatus]);
    END
    Monday, March 14, 2016 2:36 PM
  • I have searched our service logs based on your pipeline name, and only found 2 Copy failures. From the error message, the stored procedure name is "spUpdateOrder1" and the parameter name is "@ordernr" which cannot be found.

    Is there any incorrect setting? Or could you further provide the runId if this is not the issue you met?

    Wednesday, March 16, 2016 12:06 PM
  • Thanks ! This example works fine.
    Wednesday, March 16, 2016 1:29 PM