CI/CD dataflow issue

[GeekAlfPro]

Hello,

In Fabric, i have 2 workspaces (tests and prod)

i have azure devops repos (tests and prod)

i use the pipeline deployment to switch between both.

i have a datapipeline that calls 4 dataflows Gen2 that write data in a datawarehouse.

so far so good.

To avoid time consuming, i tried to set up library variables with the workspaces and the warehouses.

i found this documentation that indicates how to modify the mashup.pq file to drive destination by variables
but despites all my efforts, my prod warehouse is not being populated by the prod dataflows, it is always linked to the testing WS
I publish all git updates
i opened/saved the prod datapipeline and dataflows
without success.

Any idea of what should be checked ?

Thanks !

[v-sshirivolu]

Hi @GeekAlfPro  ,
Sorry for the inconvenience , It would be a good idea to submit a support ticket to Microsoft. Their team can thoroughly investigate the problem within your specific environment and provide detailed assistance to identify the exact root cause. This approach will help ensure that the issue is properly diagnosed and resolved.To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

[GeekAlfPro]

Hello @tayloramy 

thanks for your answer.

For the mashup.pq

i edited my dev file

saved it

git add

git commit 

git push

then i verified my code

that is the same in both environments

[StagingDefinition = [Kind = "FastCopy"]]
section Section1;
[DataDestinations = {[Definition = [Kind = "Reference", QueryName = "moeny Numérique_DataDestination", IsNewTarget = false], Settings = [Kind = "Manual", AllowCreation = false, ColumnSettings = [Mappings = {[SourceColumnName = "Année Budgétaire", DestinationColumnName = "Année Budgétaire"], [SourceColumnName = "Ligne Budgétaire", DestinationColumnName = "Ligne Budgétaire"], [SourceColumnName = "Commentaires", DestinationColumnName = "Commentaires"], [SourceColumnName = "Budget Initial", DestinationColumnName = "Budget Initial"], [SourceColumnName = "ID", DestinationColumnName = "ID"], [SourceColumnName = "Modifié", DestinationColumnName = "Modifié"], [SourceColumnName = "Budget_Quasi_ID", DestinationColumnName = "Budget_Quasi_ID"]}], DynamicSchema = false, UpdateMethod = [Kind = "Replace"], TypeSettings = [Kind = "Table"]]]}]
shared #"Budget Direction Numérique" = let
  Source = SharePoint.Tables("https://url.com/sites/DptNumrique-bibli", [Implementation = "2.0", ViewMode = "All"]),
  #"Navigation 1" = Source{[Id = "d8705c22-aa38-484b-b16e-6bb0035814d4"]}[Items],
  #"Colonnes supprimées" = Table.RemoveColumns(#"Navigation 1", {"Title", "Balise de couleur", "ID de ressource de conformité", "Direction", "Modifié par", "Créé", "Type de contenu", "Créé par", "Version", "Pièces jointes", "Modifier", "Type", "Nombre d'éléments enfants", "Nombre d’enfants du dossier", "Paramètres de l’étiquette", "Étiquette de rétention", "Étiquette de rétention appliquée", "Étiquette appliquée par", "L’élément est un enregistrement", "Application créée par", "Application modifiée par"}),
  #"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Budget_Quasi_ID", each [Année Budgétaire] & " - " & [Ligne Budgétaire]),
  #"Type de colonne changé" = Table.TransformColumnTypes(#"Colonne personnalisée ajoutée", {{"Budget_Quasi_ID", type text}, {"Budget Initial", type number}}),
  #"Valeur remplacée" = Table.ReplaceValue(#"Type de colonne changé", null, 0, Replacer.ReplaceValue, {"Budget Initial"})
in
  #"Valeur remplacée";
shared #"moeny Numérique_DataDestination" = let
  Pattern = Fabric.Warehouse([HierarchicalNavigation = null, CreateNavigationProperties = false]),
  Navigation_1 = Pattern{[workspaceId = ws_Dirnum_id]}[Data],
  Navigation_2 = Navigation_1{[warehouseId = wh_Dirnum_id]}[Data],
  TableNavigation = Navigation_2{[Schema = "dbo", Item = "Fact_Budget_DN"]}[Data]
in
  TableNavigation;

But it doesn't work unfortunately...

[tayloramy]

Hi @GeekAlfPro, 

THere's no reference to the variable library in that M code.  

If you want it to pull the value from the variable library, you will need to reference the variable library like this: 

Variable.ValueOrDefault("$(/**/My Library/My Variable)", "Sample")

Then, after you've done the git commit and git push, you still need to do a git pull in the workspace. You can do that by clicking the update all button in the source control menu of the workspace. 

https://learn.microsoft.com/en-us/fabric/cicd/git-integration/git-get-started?tabs=azure-devops%2CAz...

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[GeekAlfPro]

i modified my code like this

[...]

in
  #"Valeur remplacée";

shared #"moeny Numérique_DataDestination" = let
    // 🔧 Lecture des variables de bibliothèque avec valeur par défaut
    ws_Dirnum_id = Variable.ValueOrDefault("$(/**/My library/ws_Dirnum_id)", "00000000-0000-0000-0000-000000000000"),
    wh_Dirnum_id = Variable.ValueOrDefault("$(/**/My library/wh_Dirnum_id)", "00000000-0000-0000-0000-000000000000"),

    // 🧭 Navigation dynamique Fabric (Warehouse cible)
    Pattern = Fabric.Warehouse([HierarchicalNavigation = null, CreateNavigationProperties = false]),
    Navigation_1 = Pattern{[workspaceId = ws_Dirnum_id]}[Data],
    Navigation_2 = Navigation_1{[warehouseId = wh_Dirnum_id]}[Data],
    TableNavigation = Navigation_2{[Schema = "dbo", Item = "Fact_Budget_DN"]}[Data]
in
    TableNavigation;

i've done a pull but without success

[GeekAlfPro]

Hi,

i tried the library variables with a pipeline but it doesn't work either !

has anybody managed to set up library variables with a pipeline and dataflow Gen 2 ?

[tayloramy]

Hi @GeekAlfPro, 

in 

    ws_Dirnum_id = Variable.ValueOrDefault("$(/**/My library/ws_Dirnum_id)", "00000000-0000-0000-0000-000000000000"),

you need to replace My library with the name of your variable library. 

I just made a super simplistic test for variable libraries in my enviornment and they work without issue. 

Here is my workspace: 

in DEV, this is my variable library: 

and this is the query in my dataflow: 

this results in this record in the lakehouse: 

So it properly pulled from the variable library. 

Now when I deploy this to PROD with a deployment pipeline: 

and I update the prod variable library: 

I see the value of the prod variable library in the table in my prod lakehouse: 

This is quite simplistic, but shows that variable librarys work without issue. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[GeekAlfPro]

Hello @tayloramy 

Thanks for the time

i was wrong on the "My Library" i modified it and it works fine in the dev environment

i deployed it in my prod environment but i have this error msg now

Budget Direction Numérique_WriteToDataDestination : Un problème est survenu lors de l’actualisation du flux de données : « Data source credentials are missing or invalid. Please update the connection credentials in settings, and try again. ». Code d’erreur : 999999. (ID de requête : 17e56f91-9b52-482e-90b0-234f7eb53bc3).

for my variables library i set it up like this

it's sligthly different from yours, but it should work, no ?

and my code is 

[StagingDefinition = [Kind = "FastCopy"]]
section Section1;
[DataDestinations = {[Definition = [Kind = "Reference", QueryName = "Budget Direction Numérique_DataDestination", IsNewTarget = false], Settings = [Kind = "Manual", AllowCreation = false, ColumnSettings = [Mappings = {[SourceColumnName = "Année Budgétaire", DestinationColumnName = "Année Budgétaire"], [SourceColumnName = "Ligne Budgétaire", DestinationColumnName = "Ligne Budgétaire"], [SourceColumnName = "Commentaires", DestinationColumnName = "Commentaires"], [SourceColumnName = "Budget Initial", DestinationColumnName = "Budget Initial"], [SourceColumnName = "ID", DestinationColumnName = "ID"], [SourceColumnName = "Modifié", DestinationColumnName = "Modifié"], [SourceColumnName = "Budget_Quasi_ID", DestinationColumnName = "Budget_Quasi_ID"]}], DynamicSchema = false, UpdateMethod = [Kind = "Replace"], TypeSettings = [Kind = "Table"]]]}]
shared #"Budget Direction Numérique" = let
  Source = SharePoint.Tables("https://url", [Implementation = "2.0", ViewMode = "All"]),
  #"Navigation 1" = Source{[Id = "d8705c22-bb55-482b-c55d-6bb0039314d4"]}[Items],
  #"Colonnes supprimées" = Table.RemoveColumns(#"Navigation 1", {"Title", "Balise de couleur", "ID de ressource de conformité", "Direction", "Modifié par", "Créé", "Type de contenu", "Créé par", "Version", "Pièces jointes", "Modifier", "Type", "Nombre d'éléments enfants", "Nombre d’enfants du dossier", "Paramètres de l’étiquette", "Étiquette de rétention", "Étiquette de rétention appliquée", "Étiquette appliquée par", "L’élément est un enregistrement", "Application créée par", "Application modifiée par"}),
  #"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Budget_Quasi_ID", each [Année Budgétaire] & " - " & [Ligne Budgétaire]),
  #"Type de colonne changé" = Table.TransformColumnTypes(#"Colonne personnalisée ajoutée", {{"Budget_Quasi_ID", type text}, {"Budget Initial", type number}}),
  #"Valeur remplacée" = Table.ReplaceValue(#"Type de colonne changé", null, 0, Replacer.ReplaceValue, {"Budget Initial"})
in
  #"Valeur remplacée";
shared WorkspaceId = let
  Source = Variable.ValueOrDefault("$(/**/vl_variable_Dev_Prod/ws_Dirnum_id)")
in
  Source;
shared WarehouseId = let
  Source = Variable.ValueOrDefault("$(/**/vl_variable_Dev_Prod/wh_Dirnum_id)")
in
  Source;
shared #"Budget Direction Numérique_DataDestination" = let
  Pattern = Fabric.Warehouse([HierarchicalNavigation = null, CreateNavigationProperties = false]),
  Navigation_1 = Pattern{[workspaceId = ws_Dirnum_id]}[Data],
  Navigation_2 = Navigation_1{[warehouseId = wh_Dirnum_id]}[Data],
  TableNavigation = Navigation_2{[Schema = "dbo", Item = "Fact_Budget_DN"]}[Data]
in
  TableNavigation;

[tayloramy]

Hi @GeekAlfPro, 

WHen you first run it in prod, you need to re-configure the connections on the datasources. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[GeekAlfPro]

Hello @tayloramy 

After reconfiguring the connections i've had this error message : 

Un problème s’est produit lors de l’actualisation du flux de données. Code d’erreur : ModelBuilderOutputDestinationDestinationEvaluationExceptionResult. (ID de requête : 00000000-0000-0000-0000-000000000000).

Thanks !

[v-sshirivolu]

Hi @GeekAlfPro ,
Since everything worked before the CI/CD migration, the error you’re seeing likely points to a permissions or connection issue after deployment. Even if your syntax is correct, CI/CD doesn’t transfer credentials by default, so the dataflow in the target workspace may be running with an identity that doesn’t have access to the data source. Please check Dataflow - Settings - Data source credentials in the destination workspace and update or re-enter them. Also, make sure the linked connections exist in the target environment and match those in your dataflow JSON. Confirm which identity the dataflow is using your account or the workspace’s Managed Identity. If this identity doesn’t have access to the source, the refresh will fail even if deployment was successful. Once credentials and access are set up, the refresh would work as expected.

[GeekAlfPro]

Thanks @v-sshirivolu 

It is the same user that access both workspaces and have access to the source and destination too.

To wrap up the steps i did the followings

in my dev Workspace

I created a DF Gen2 to connect a sharepoint list and an excel file on sharepoint.

i created a library variable

i followed the instructions here to set up the Library variables

Références de variables dans dataflow - Microsoft Fabric | Microsoft Learn

i modified the mashup file to link to the VL

[StagingDefinition = [Kind = "FastCopy"]]
section Section1;
[DataDestinations = {[Definition = [Kind = "Reference", QueryName = "Budget Direction Numérique_DataDestination", IsNewTarget = false], Settings = [Kind = "Manual", AllowCreation = false, ColumnSettings = [Mappings = {[SourceColumnName = "Année Budgétaire", DestinationColumnName = "Année Budgétaire"], [SourceColumnName = "Ligne Budgétaire", DestinationColumnName = "Ligne Budgétaire"], [SourceColumnName = "Commentaires", DestinationColumnName = "Commentaires"], [SourceColumnName = "Budget Initial", DestinationColumnName = "Budget Initial"], [SourceColumnName = "ID", DestinationColumnName = "ID"], [SourceColumnName = "Modifié", DestinationColumnName = "Modifié"], [SourceColumnName = "Budget_Quasi_ID", DestinationColumnName = "Budget_Quasi_ID"]}], DynamicSchema = false, UpdateMethod = [Kind = "Replace"], TypeSettings = [Kind = "Table"]]]}]
shared #"Budget Direction Numérique" = let
  Source = SharePoint.Tables("https://url", [Implementation = "2.0", ViewMode = "All"]),
  #"Navigation 1" = Source{[Id = "d8705c22-0000-0000-0000-6bb0039314d4"]}[Items],
  #"Colonnes supprimées" = Table.RemoveColumns(#"Navigation 1", {"Title", "Balise de couleur", "ID de ressource de conformité", "Direction", "Modifié par", "Créé", "Type de contenu", "Créé par", "Version", "Pièces jointes", "Modifier", "Type", "Nombre d'éléments enfants", "Nombre d’enfants du dossier", "Paramètres de l’étiquette", "Étiquette de rétention", "Étiquette de rétention appliquée", "Étiquette appliquée par", "L’élément est un enregistrement", "Application créée par", "Application modifiée par"}),
  #"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Budget_Quasi_ID", each [Année Budgétaire] & " - " & [Ligne Budgétaire]),
  #"Type de colonne changé" = Table.TransformColumnTypes(#"Colonne personnalisée ajoutée", {{"Budget_Quasi_ID", type text}, {"Budget Initial", type number}}),
  #"Valeur remplacée" = Table.ReplaceValue(#"Type de colonne changé", null, 0, Replacer.ReplaceValue, {"Budget Initial"})
in
  #"Valeur remplacée";
shared WorkspaceId = let
  Source = Variable.ValueOrDefault("$(/**/vl_variable_Dev_Prod/ws_Dirnum_id)")
in
  Source;
shared WarehouseId = let
  Source = Variable.ValueOrDefault("$(/**/vl_variable_Dev_Prod/wh_Dirnum_id)")
in
  Source;
shared #"Budget Direction Numérique_DataDestination" = let
  Pattern = Fabric.Warehouse([HierarchicalNavigation = null, CreateNavigationProperties = false]),
  Navigation_1 = Pattern{[workspaceId = ws_Dirnum_id]}[Data],
  Navigation_2 = Navigation_1{[warehouseId = wh_Dirnum_id]}[Data],
  TableNavigation = Navigation_2{[Schema = "dbo", Item = "Fact_Budget_DN"]}[Data]
in
  TableNavigation;

thanks to @tayloramy  i corrected my syntaxes errors 

After test, the DF seems to work fine, no errors

so i deploy all parts in the prod WS

i had errors of connections, and the generic one (only after the first launch) that i corrected

But the updates are still linked to the WH of the test WS

but, another thing to notice when i look at the json file on the prod and dev environnements, they are still the same despites i activated the prod one in the ws of prod !

[v-sshirivolu]

Hi @GeekAlfPro ,

Thanks for sharing all the details. From what you’ve described, it seems the Prod Dataflow is still pointing to the Test warehouse because either the variable values in Prod, the workspace code, or the Dataflow setup hasn’t fully updated. I suggest checking a few things - first, make sure the workspace and warehouse IDs in the Prod Variable Library are correct, and that your mashup.pq file is referencing these Prod variables properly. Next, after your Git deployment, pull the latest changes in the Prod workspace using Source Control - “Update all” so it actually uses the updated M code. Also, confirm that the Dataflow is activated in the Prod workspace and re-enter the data source credentials to ensure it points to the Prod warehouse. You can also add a temporary query to the Dataflow to output the WorkspaceId and WarehouseId values and verify they show the Prod IDs. If needed, you can temporarily hardcode the Prod IDs just to confirm everything writes to the right warehouse before switching back to using variables. Doing these steps should fix the issue where Prod keeps writing to the Test warehouse.

[GeekAlfPro]

Hello,

@v-sshirivolu 

how can add a temporary query as i read that 

Else i did as you suggest.

i face 2 issues 

First when i try to validate the flow in the prod ws 

i have this error message : 

and after i re-enter the data source connections, at only the first launch, i have this error message

[GeekAlfPro]

Hi,

i also tried to hard code the ws and wh id's.

it works for the df that get data from a sharepoint list but not for the one that get data from an excel file on sharepoint.

[v-sshirivolu]

Hi @GeekAlfPro ,

Thanks for sharing all the details. To help troubleshoot, you can create a temporary query in your Dataflow to check what Workspace and Warehouse IDs are actually being used in Prod. For example, create a blank query with this M code:

let
wsId = Variable.ValueOrDefault("$(/**/YourVariableLibrary/ws_Dirnum_id)", "Unknown"),
whId = Variable.ValueOrDefault("$(/**/YourVariableLibrary/wh_Dirnum_id)", "Unknown")
in
[WorkspaceId = wsId, WarehouseId = whId]
This will show the current values, and you can remove the query once you’ve verified them.

Regarding the Excel file issue on SharePoint, hard-coding the IDs works for SharePoint lists but can fail for Excel files because the connection needs proper access to the site or folder. Make sure that the credentials or service identity used in Prod has read permissions to the Excel file location.

Once you’ve run the temporary query and confirmed the IDs are correct, check that the Excel file credentials are valid. You can also try using a smaller test Excel file to rule out any file specific problems. If the IDs and credentials are set up correctly, the Dataflow should validate and refresh successfully.

[v-sshirivolu]

Hi @GeekAlfPro ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[GeekAlfPro]

Hello @v-sshirivolu 

i was out my office this week,

i will try next one.

i'll give you feedback 

Thanks again

[v-sshirivolu]

Hi @GeekAlfPro ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[GeekAlfPro]

Hello @v-sshirivolu 

i tried the temp query with your code 

let
wsId = Variable.ValueOrDefault("$(/**/YourVariableLibrary/ws_Dirnum_id)", "Unknown"),
whId = Variable.ValueOrDefault("$(/**/YourVariableLibrary/wh_Dirnum_id)", "Unknown")
in
[WorkspaceId = wsId, WarehouseId = whId]

But it displays "Unknown"

it seems that the code is ok

[tayloramy]

Hi @GeekAlfPro, 

I've not seen that error before, and I'm not able to replicate it. 

Can you explain in more detail what steps you take to make this error happen? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.