Azure Data Factory executes stored procedure twice (and concurrently)

Matthias Delafontaine 16 Reputation points
2024-06-26T14:40:32.42+00:00

I have an ADF pipeline executing a stored procedure on an Azure SQL database.

Retries is set to 0, so this procedure should be executed exactly once. Now it turns out that it sometimes executes twice, i.e. a second time when the first execution is still running, so concurrently causing all kinds of problems.

The monitoring screen in data factory is more or less providing evidence of the second execution:

User's image As you can see the execution duration of 1606 seconds (~27 minutes) is not in line with the 39 minutes reported duration. But this completely corresponds to our own logging where we can see that the first execution lasted about 39 min and the second execution about 27 minutes, until both ended up in an error.

Am I doing something wrong or is this really Azure Data Factory failing?

Below is the code of the stored procedure pipeline activity:

    "name": "Load Stream 3",
    "type": "SqlServerStoredProcedure",
    "dependsOn": [],
    "policy": {
        "timeout": "0.12:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "storedProcedureName": "[dbo].[usp_LoadStream]",
        }
    },
    "linkedServiceName": {
        "referenceName": "myLinkedService",
        "type": "LinkedServiceReference"
    }
}
Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,801 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.