locked
Doubt about ADF with Sink and Stored Procedure - Time out / Waiting execution RRS feed

  • Question

  • 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 merge the data to destination azure sql database table. There is all configuration that i used below.

    But when the pipeline run, the output DataSet Always return “Timeout”, is this a sql connection problem or configuration?

    I checked to allow Azure Services Connection in SQL Azure Database Firewall and my inputDataSet has a external:true.

    Azure Connection

    {

        "name": "EvenSQLAzure",

        "properties": {

            "hubName": "evendatafactory_hub",

            "type": "AzureSqlDatabase",

            "typeProperties": {

                "connectionString": "Data Source=tcp:xxxxxx.database.windows.net,1433;Initial Catalog=XXXX;Integrated Security=False;User ID=gdenis;Password=**********;Connect Timeout=30;Encrypt=True"

            }

        }

    }

    Input DataSet

    {

        "name": "GC-EmpreendimentoDataset",

        "properties": {

            "structure": [

                {

                    "name": "codEmpreendimento",

                    "type": "String"

                },

                {

                    "name": "NomeEmpreendimento",

                    "type": "String"

                },

                {

                    "name": "TotalAreaUtil",

                    "type": "String"

                },

                {

                    "name": "TotalArea",

                    "type": "String"

                },

                {

                    "name": "Logradouro",

                    "type": "String"

                },

                {

                    "name": "NumeroLogradouro",

                    "type": "Int32"

                },

                {

                    "name": "Bairro",

                    "type": "String"

                },

                {

                    "name": "Cep",

                    "type": "String"

                },

                {

                    "name": "idMunicipio",

                    "type": "Int32"

                },

                {

                    "name": "idRegiao",

                    "type": "Int32"

                },

                {

                    "name": "Data_Controle",

                    "type": "Datetime"

                },

                {

                    "name": "NomeRegiao",

                    "type": "String"

                },

                {

                    "name": "NomeMunicipio",

                    "type": "String"

                }

            ],

            "published": false,

            "type": "SqlServerTable",

            "linkedServiceName": "EvenGCLinkedServer",

            "typeProperties": {

                "tableName": "v_EMPREENDIMENTOS"

            },

            "availability": {

                "frequency": "Minute",

                "interval": 15

            },

            "external": true,

            "policy": {

                "externalData": {

                    "retryInterval": "00:01:00",

                    "retryTimeout": "00:10:00",

                    "maximumRetry": 3

                }

            }

        }

    }

    Output DataSet (Azure SQL Database)

    {

        "name": "EmpreendimentoReplication",

        "properties": {

            "activities": [

                {

                    "type": "Copy",

                    "typeProperties": {

                        "source": {

                            "type": "SqlSource",

                            "sqlReaderQuery": "SELECT codEmpreendimento, NomeEmpreendimento, TotalAreaUtil, TotalArea, Logradouro, NumeroLogradouro, Bairro, Cep, idMunicipio, idRegiao, NomeRegiao, NomeMunicipio FROM v_EMPREENDIMENTOS"

                        },

                        "sink": {

                            "type": "SqlSink",

                            "sqlWriterStoredProcedureName": "spEmpreendimentoSync",

                            "storedProcedureParameters": {},

                            "sqlWriterTableType": "EmpreendimentoType",

                            "writeBatchSize": 10000,

                            "writeBatchTimeout": "60.00:00:00"

                        },

                        "translator": {

                            "type": "TabularTranslator",

                            "columnMappings": "Bairro:Bairro,Cep:Cep,Logradouro:Logradouro,NomeEmpreendimento:NomeEmpreendimento,NomeMunicipio:NomeMunicipio,NomeRegiao:NomeRegiao,NumeroLogradouro:NumeroLogradouro,TotalArea:TotalArea,TotalAreaUtil:TotalAreaUtil,codEmpreendimento:codEmpreendimento,idMunicipio:idMunicipio,idRegiao:idRegiao"

                        }

                    },

                    "inputs": [

                        {

                            "name": "GC-EmpreendimentoDataset"

                        }

                    ],

                    "outputs": [

                        {

                            "name": "AZ-EmpreendimentoDataset"

                        }

                    ],

                    "policy": {

                        "timeout": "00:30:00",

                        "delay": "00:10:00",

                        "concurrency": 3,

                        "executionPriorityOrder": "NewestFirst",

                        "style": "StartOfInterval",

                        "retry": 3,

                        "longRetry": 1,

                        "longRetryInterval": "00:00:00"

                    },

                    "scheduler": {

                        "frequency": "Minute",

                        "interval": 15

                    },

                    "name": "Activity-0-[dbo]_[v_EMPREENDIMENTOS]->[dbo]_[Empreendimento]"

                }

            ],

            "start": "2017-05-23T00:21:12.453Z",

            "end": "2099-12-31T02:00:00Z",

            "isPaused": false,

            "hubName": "evendatafactory_hub",

            "pipelineMode": "Scheduled"

        }

    }

    Tabel Type

    CREATE TYPE [dbo].[EmpreendimentoType] AS TABLE(

           [codEmpreendimento] [varchar](8) NOT NULL,

           [NomeEmpreendimento] [varchar](90) NULL,

           [TotalAreaUtil] [varchar](20) NULL,

           [TotalArea] [varchar](20) NULL,

           [Logradouro] [varchar](90) NULL,

           [NumeroLogradouro] [int] NULL,

           [Bairro] [varchar](80) NULL,

           [Cep] [varchar](20) NULL,

           [idRegiao] [int] NULL,

           [NomeRegiao] [varchar](90) NULL,

           [idMunicipio] [int] NULL,

           [NomeMunicipio] [varchar](90) NULL

    )

    GO

     

    Procedure

    CREATE PROCEDURE [dbo].[spEmpreendimentoSync] @Empreendimento [dbo].[EmpreendimentoType] READONLY

    AS

    BEGIN

     

           MERGE [dbo].[Empreendimento] AS tpr

                 USING @Empreendimento AS spr

                 ON tpr.codEmpreendimento = spr.codEmpreendimento

           WHEN MATCHED THEN

                 UPDATE SET

                        tpr.NomeEmpreendimento = spr.NomeEmpreendimento

                        ,tpr.TotalAreaUtil = spr.TotalAreaUtil

                        ,tpr.TotalArea = spr.TotalArea

                        ,tpr.Logradouro = spr.Logradouro

                        ,tpr.NumeroLogradouro = spr.NumeroLogradouro

                        ,tpr.Bairro = spr.Bairro

                        ,tpr.Cep = spr.Cep

                        ,tpr.idRegiao = spr.idRegiao

                        ,tpr.NomeRegiao = spr.NomeRegiao

                        ,tpr.idMunicipio = spr.idMunicipio

                        ,tpr.NomeMunicipio = spr.NomeMunicipio

           WHEN NOT MATCHED THEN

                 INSERT (codEmpreendimento, NomeEmpreendimento, TotalAreaUtil, TotalArea,Logradouro, NumeroLogradouro,Bairro,Cep,idRegiao,NomeRegiao,idMunicipio,NomeMunicipio)

                 VALUES(spr.codEmpreendimento, spr.NomeEmpreendimento, spr.TotalAreaUtil, spr.TotalArea,spr.Logradouro, spr.NumeroLogradouro,spr.Bairro,spr.Cep,spr.idRegiao,spr.NomeRegiao,spr.idMunicipio,spr.NomeMunicipio)

    ;

    END

    Tuesday, May 23, 2017 7:22 PM