Parameterized connections in Data pipelines

I know this is an old thread but thought it might be worth posting this.

To make things a little more human friendly you can have a variables library containing the connection_name. 

Pass the connection name from the Variable Library as a parameter to a lightweight Python notebook that resolves the corresponding Fabric connection ID.

A copy data activity can use the notebook exit value as the dynamic connection. So in a notebook you'd have a parameter cell, somthing like this:

connection_name = ''

followed by a code cell, something like this:

import requests
from urllib.parse import quote

connection_name = connection_name.strip()

if not connection_name:
    raise ValueError("connection_name must be provided")

token = notebookutils.credentials.getToken(
    "https://api.fabric.microsoft.com"
)

headers = {
    "Authorization": f"Bearer {token}"
}

base_url = "https://api.fabric.microsoft.com/v1/connections"
url = base_url
matches = []

while url:
    response = requests.get(url, headers=headers, timeout=30)
    response.raise_for_status()

    payload = response.json()

    matches.extend(
        connection
        for connection in payload.get("value", [])
        if connection.get("displayName", "").casefold()
        == connection_name.casefold()
    )

    continuation_token = payload.get("continuationToken")

    url = (
        f"{base_url}?continuationToken="
        f"{quote(continuation_token, safe='')}"
        if continuation_token
        else None
    )

if len(matches) == 0:
    raise ValueError(
        f"Fabric connection not found: '{connection_name}'"
    )

if len(matches) > 1:
    raise ValueError(
        f"Fabric connection name is not unique: '{connection_name}'"
    )

connection = matches[0]

print(f"Connection name: {connection['displayName']}")
print(f"Connection ID:   {connection['id']}")

notebookutils.notebook.exit(connection["id"])

Rather than storing environment-specific connection GUIDs in parameters or a Variable Library, store the human-readable connection name. At runtime, resolve that name against the Fabric Connections API and pass the resulting connection ID to the Copy activity.

That avoids hard-coding a connection ID which could change if the connection is deleted and recreated, while still allowing DEV/TEST/PROD to use different connections through environment-specific Variable Library values.

A Variable Library expression containing the correct connection GUID works when the pipeline runs, but the Copy activity designer may fail to refresh metadata or preview data. Entering the same GUID literally allows the designer to refresh and preview.

we also on the way to leverage that new feature...following findings (without documentation):

Creation/Development Phase
1. Yes, you need to specify the ID, you can get the ID from GatewayMgt>Connections, of the connection as dynamic content. 

2. When you create a dynamic connection, dedendant on "connection type", additonal fields need to be provided:
         2.1 Type "Fabric SQL Database" = WorkspaceID and SQLDatabaseID needed
         2.2 Type "SQL Server" = DatabaseName?

Deployment Phase
Of course making connections dynamic is only one half of the story, the other half is to deploy this pipelines to other workspaces and run them with other parameter values...
Findings:
1. So if you have connections which need "WorkspaceID" you can leverage the system.workspaceID variable to make the parameter value dynamic across workspaces
2. The ConnectionID seems, at least for Fabric-conncetion-types like WH,LH,Database, the same accross workspaces.
3. For other connection-types you need a way to centrally manage different ConnectionIDs per Workspace

ISSUE:
The "THREAT AS NULL" settings are not deployed with Fabric Deployment Pipelines for our scenario 😞
Repro Steps:
1. Create a DEV and PROD Workspace
2. in the DEV workspace create a pipeline1 with a "Lookup" Activity to call a SQL Server Stored Procedure. Make the Connection for the DB with the proc dynamic.
- The SQL Proc needs to have multiple input parameters which are optional, so they need to be flagged in pipeline.LookupActivity.settings.parameter as "TREAT AS NULL"
- If you now deploy pipeline1 from DEV to PROD Workspace with Fabric Pipeline the "THREAT AS NULL" info get lost. If you open pipeline Editor>View>EDIT JSON Code we can see the difference causing the issue:

DEV Pipeline:

PROD Pipeline:

The current workaround is to update the pipelines in PROD after deployment manually , but this is not how it shoud be.... can someone from microsoft qualify that as an bug?

I have the same question. Not a lot of detail/documentation on how exactly to configure this. I am wanting to know how to use connections with Fabric SQL DBs, Fabric DWs, Notebooks, etc.

Hi cw88,
Thank you for reaching out to the Microsoft Fabric Forum Community. 

Just to better understand your scenario and possibly help further: 

• Could you please share which type of connection or data source you're trying to parameterize? 
• Are you currently working with Microsoft Fabric Data Pipelines or Azure Data Factory? 
• Also, may I ask what your intended use case is for parameterizing the connection? 

Thank you.