Forum Discussion
Parameterized connections in Data pipelines
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, ...
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.
19 Replies
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 WorkspaceISSUE:
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?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.
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?
-
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.
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.
