Parameterized connections in Data pipelines

[cw88]

Hello,
Has anyone already used the new feature “Parameterized connections in Data pipelines” from the march summary? Unfortunately, apart from the small section in the blog, I can't find anything about it, e.g. an example with connection, parameters, ...

[jochenj]

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?

[arpost]

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.

[v-saisrao-msft]

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. 

[cw88]

@v-saisrao-msft . 

I want to parametrize a connection to an onprem sql server, working with Fabric Data Pipeline. Use case: general parameterization of connections to be more flexible in case of changes.

First question: Do i have to enter the connectionid or the name? If it is the ID: Where do I get the ID of a connection?

- 

[v-saisrao-msft]

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

Parameterized connections in Microsoft Fabric Data Pipelines offer a way to pipeline flexibility, especially across different environments or datasets. When working within the Fabric UI, you should reference the connection name, not the connection ID. The connection name can be found under Settings > Manage connections and gateways, and it must match exactly. 
For on-premises SQL Server connections, ensure the connection is configured through an On-premises Data Gateway using Basic authentication, which is currently required. To implement parameterization, you can define pipeline parameters such as Database Name and reference them using dynamic expressions like @pipeline().parameters.DatabaseName within the source settings of your Copy Data activity. This enables scenarios such as dynamically switching between databases without modifying the pipeline structure. 

If you need to switch entire connections, you can also parameterize the connection name using a parameter like Connection Name and reference it as @pipeline().parameters.ConnectionName. 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[cw88]

Hello @v-saisrao-msft ,

for me it does not work to parameterize the connection name using a parameter

When I start the pipeline, the status of the used activity lookup remains on Queued, nothing happens... 

[v-saisrao-msft]

Hi  @cw88,

You're absolutely right to highlight this behavior, and thanks for testing the new feature. I'd like to follow up on my previous response with some clarification. While parameterizing the connection name using a pipeline parameter (e.g., @pipeline().parameters.ConnectionName) is indeed a capability introduced in the March update of Microsoft Fabric, it appears that this functionality is not yet fully supported for on-premises SQL Server connections configured via the On-premises Data Gateway. 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[cw88 · Accepted solution]

Hello @v-saisrao-msft ,

sorry, but your answer doesn't help me. 

I don't know if it is working or not, and it is no documentation how it works... 

For all others who want to use the feature: For my case i works, when i use the connection id as parameter in "connection". To get the connectionid, I stored the fixed connection and found the id in the json.

[doubles]

@cw88 any chance you could screenshot what you did here - i tried what i think you mean and still not working for me

[cw88]

Screenshot for sql server onprem as Source:

For connection i use the Connection ID from the connection settings. Hope it helps!

[doubles]

Interesting, for some reason when I use a connection ID for SQL on-prem connection it's asking for a workspaceID and SQL Database ID: 

[cw88]

Mayby a difference between connection type sql server and sql database? I use sql server, where database is not mandatory.

[doubles]

@cw88 thanks haha, that was the issue. however, this still does not work for me, not its just saying it can't resolve the connection: 

(i confirmed the connection works and the guid is correct)

[jochenj]

did you already try to
- remove all that expressions make the input static

- ribbon>view>JSON --> check how the lookup actvitiy has which properties defined

- compare with the results of your expressions if you dynamically inject the same ids..

[doubles]

@jochenj that worked, thanks so much. learning a lot today haha

[doubles]

fwiw, the connection guids seem to be case-sensitive, i had the right guid, its just all caps in the connection settings in the UI and lowercase in the json

[jochenj]

Try changing the value in dropdown "connection type" to something other and back.. hd this sometimes that the gui not refreshes the type specific fields

[doubles]

thanks, that didn't work unfortunately

[v-saisrao-msft]

Hi @cw88,
Thanks for the follow-up, and I really appreciate you taking the time to share your working approach this will definitely help others running into the same issue.

Using the connection ID (instead of the connection name) as a parameter and retrieving it from the exported pipeline JSON is a smart and effective workaround for on-prem SQL Server connections via the Gateway.

Please Mark your approach as the accepted solution so other community members can find it more quickly. Thanks again for helping drive clarity on this new feature!