Unable to get SPROC metadata using Lookup Activity (InvalidLinkedServiceReference)

GP-2266 31 Reputation points

Hello All

I am coming up with a weird issue inside ADF where I am unable to get the list of SPROCs in a SQL Server database using the Lookup Activity. The error I get is:

Error code InvalidLinkedServiceReference
Details Invalid linked service reference. Name: LS_SQLSVR_XXXX

If I use the stored procedure activity, using the same LinkedService it works no problem and I am getting the metadata just fine. I suspect it may have something to do with the Dataset as a Loookup activity requires one and stored procure does not. To also add, using the Lookup activity I am able to use Table and Query options just fine but not sproc option.

If I hardcode the SPROC names in the Lookup activity previewing the data seems to execute that SPROC on the server as well. The LinkedService is configured to connect to an OnPrem SQL Server, and uses Self Hosted IR.

Here is the Lookup Activity JSON:

    "name": "Get Addresses",
    "type": "Lookup",
    "dependsOn": [],
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    "userProperties": [],
    "typeProperties": {
        "source": {
            "type": "SqlServerSource",
            "queryTimeout": "02:00:00",
            "partitionOption": "None"
        "dataset": {
            "referenceName": "DS_SQLDB_ExternalData",
            "type": "DatasetReference"
        "firstRowOnly": false

Here is the Dataset JSON:

    "name": "DS_SQLDB_ExternalData",
    "properties": {
        "linkedServiceName": {
            "referenceName": "LS_SQLSVR_XXXX",
            "type": "LinkedServiceReference",
            "parameters": {<obfuscated>
        "annotations": [],
        "type": "SqlServerTable",
        "schema": []
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,474 questions
{count} votes

1 answer

Sort by: Most helpful
  1. GP-2266 31 Reputation points

    The issue was that the LinkedService created was not published into live mode and merged into all the GIT branches. For whatever reason, certain activities within ADF require the LS to be published, and some do not.