Forum Discussion

Ravindra_'s avatar
Ravindra_
Frequent Visitor
1 year ago
Solved

Environment Switching in Power BI Custom Connector Using M language(Staging <-> Production)

Hi Team,

I’m developing a Power BI Custom Connector and would like to implement a mechanism to switch between Staging and Production environments dynamically.

Is it possible to design a build where the connector determines the environment (staging or production) at runtime using:

  • A system environment variable, or

  • A local configuration file like .txt or .json

I’m looking for a reliable approach using M language within the connector logic to support this kind of setup.

Has anyone implemented such functionality in their custom connector or can recommend a best practice?

Appreciate your help and insights!

Thanks,

Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

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

    For your scenario implementing environment switching in a Power BI Custom Connector the recommended best practice is to use user-defined parameters via the connector UI. This approach ensures:

    • Compatibility with both Power BI Desktop and Power BI Service
    • Compliance with security and sandbox restrictions
    • Flexibility to scale across different environments without hardcoding or relying on local files


    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

5 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    Why not just use parameters to swap connection string or schema like you would with other connectors?

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    1 year ago

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

    For your scenario implementing environment switching in a Power BI Custom Connector the recommended best practice is to use user-defined parameters via the connector UI. This approach ensures:

    • Compatibility with both Power BI Desktop and Power BI Service
    • Compliance with security and sandbox restrictions
    • Flexibility to scale across different environments without hardcoding or relying on local files


    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    Hi Ravindra,

    It is possible to implement a mechanism to switch between Staging and Production environments dynamically while developing a Power BI Custom Connector. You can achieve this using M language. Here are two suggestions:

    1. Using System Environment Variable:

      • Power BI Custom Connector can read system environment variables. You can use these variables to determine which environment the connector is running in.
      • For example, you can create an environment variable named Environment. This variable can have values like Staging or Production.
      • In M language, you can read this variable and use the appropriate connection strings.

    2. Using Local Configuration File:

      • The connector can read a local configuration file (e.g., .txt or .json) where you store environment information.
      • By reading the file content using M language, you can determine which environment the connector is running in.
      • For example, you can have a config.json file with the following structure
    {
  "environment": "Staging"
}
​
    • In M language, you can read this file and use the appropriate connection strings.


    Example M Code:

    let
    // Reading environment variable
    environment = Environment.GetVariable("Environment"),
    // Reading configuration file
    config = Json.Document(File.Contents("config.json")),
    environment = config[environment],

    // Connection strings based on environment
    source = if environment = "Staging" then
        "https://staging.api.example.com/data"
    else
        "https://production.api.example.com/data"
in
    source

    With these methods, you can dynamically determine which environment your Power BI Custom Connector is running in and use the appropriate connection strings. I hope this helps!

    Thanks
    translation and formatting supported by AI

    • shripathikamath's avatar
      shripathikamath
      Regular Visitor
      1 year ago

      I have a question related to the second approach (using a local configuration file.)  Are there special permissions I need to set?  Because I get a security error when trying to read a file placed in the C:\documents\Public folder.