none
Sink stored procedure activity RRS feed

  • Question

  • Hello, I try to add a new column when copying data from a blob to an azuretable.
    I took the marketing example from azure.microsoft.com and tried to change it to see how it works.

    In the given link it states that the table type should be the same as the input source. Yet the error I am receiving suggests otherwise: Column 'new' does not exist in the table 'MarketingType'

    I started with adding a small csv file to a blob and trying to simulate the process.

    Code examples included below.

    "name": "marketing",
        "properties": {
            "structure": [
                {
                    "name": "profileid",
                    "type": "String"
                },
                {
                    "name": "state",
                    "type": "String"
                }
            ],
            "published": false,
            "type": "AzureBlob",
            "linkedServiceName": "fromserver",
            "typeProperties": {
                "fileName": "marketing.csv",
                "folderPath": "fromcontainer",
                "format": {
                    "type": "TextFormat",
                    "rowDelimiter": "\n",
                    "columnDelimiter": ","
                }
            },
            "availability": {
                "frequency": "Hour",
                "interval": 1
            },
            "external": true,
            "policy": {}
        }
    }
    {
        "name": "marketingdest",
        "properties": {
            "structure": [
                {
                    "name": "profileid",
                    "type": "String"
                },
                {
                    "name": "State",
                    "type": "String"
                },
                {
                    "name": "new",
                    "type": "String"
                }
            ],
            "published": false,
            "type": "AzureSqlTable",
            "linkedServiceName": "toserver",
            "typeProperties": {
                "tableName": "marketing"
            },
            "availability": {
                "frequency": "Hour",
                "interval": 1
            }
        }
    }


    {
        "name": "marketing",
        "properties": {
            "description": "marketing",
            "activities": [
                {
                    "type": "Copy",
                    "typeProperties": {
                        "source": {
                            "type": "BlobSource"
                        },
                        "sink": {
                            "type": "SqlSink",
                            "sqlWriterStoredProcedureName": "spOverwriteMarketing",
                            "storedProcedureParameters": {
                                "stringData": {
                                    "value": "d0001"
                                }
                            },
                            "sqlWriterTableType": "MarketingType",
                            "writeBatchSize": 10000,
                            "writeBatchTimeout": "60.00:00:00"
                        }
                    },
                    "inputs": [
                        {
                            "name": "marketing"
                        }
                    ],
                    "outputs": [
                        {
                            "name": "marketingdest"
                        }
                    ],
                    "policy": {
                        "timeout": "01:00:00",
                        "concurrency": 1,
                        "executionPriorityOrder": "NewestFirst",
                        "style": "StartOfInterval"
                    },
                    "scheduler": {
                        "frequency": "Hour",
                        "interval": 1
                    },
                    "name": "marketing",
                    "description": "marketing"
                }
            ],
            "start": "2015-09-23T00:00:00Z",
            "end": "2015-09-24T00:00:00Z",
            "isPaused": false
    
        }
    }

    in SQL i created the following objects

    CREATE PROCEDURE [dbo].[spOverwriteMarketing] @Marketing [dbo].[MarketingType] READONLY, @stringData varchar(256)
    AS
    BEGIN
        
        INSERT [dbo].[Marketing](ProfileID, State,new)
        SELECT 
    		*
    		,@stringData
    	 FROM @Marketing
    END
    
    CREATE TABLE [dbo].[Marketing](
    	[ProfileID] [varchar](256) NOT NULL,
    	[State] [varchar](256) NOT NULL,
    	[new] [varchar](256) NULL
    )
    
    
    CREATE TYPE [dbo].[MarketingType] AS TABLE(
        [ProfileID] [varchar](256) NOT NULL,
        [State] [varchar](256) NOT NULL,
    )




    • Edited by Florian Devos Wednesday, September 23, 2015 4:59 PM forgot the error message
    Wednesday, September 23, 2015 4:56 PM

Answers

  • Hi Florian,

    The failure is caused by that the target dataset defines one additional column "new" which is not defined in your table type. The table type schema should be the same as the input source, and it should be also the same as the dataset definition. You could try removing the column "new" in the target dataset to make your copy work.

    • Marked as answer by Florian Devos Thursday, September 24, 2015 7:38 AM
    Thursday, September 24, 2015 2:30 AM

All replies

  • Hi Florian,

    The failure is caused by that the target dataset defines one additional column "new" which is not defined in your table type. The table type schema should be the same as the input source, and it should be also the same as the dataset definition. You could try removing the column "new" in the target dataset to make your copy work.

    • Marked as answer by Florian Devos Thursday, September 24, 2015 7:38 AM
    Thursday, September 24, 2015 2:30 AM
  • Hi,

    Thank you very much for the answer. I removed the column "new" in the destination dataset and it works. It also makes more sense.

    If I can put it this way, the source dataset its destination is the tabletype that 'enters' the stored procedure. This tabletype should also be defined as the dataset destination within ADF. The outcome of the stored procedure (the Marketing table) is nowhere defined in the DataFactory.


    Thursday, September 24, 2015 7:38 AM