Unable to get SPROC metadata using Lookup Activity (InvalidLinkedServiceReference)

Question

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": []
    }
}

Answer 1

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.