Forum Discussion
CI/CD dataflow issue
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 !
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.
Hello,
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
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.
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.
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.
Hello v-sshirivolu
i was out my office this week,
i will try next one.
i'll give you feedback
Thanks again
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.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