Datasets keep losing gateways

[cottrera]

Hi 

I am having some gateway issues. To ensure that out Data Warehouse   (DW)daily early morning proceedures (daily run)  are not disturbed. The Data Warehouse switch off all gateways at there end. Once the DW daily run has finished anytime between 3:30am and 8:30am they turn the gates ways back on. A power automate scrip then works its way througn refreshing the datasets. However randomly some datasets lose there gateways.

 this means adding selecting them again. Does any know of a way to stop this or is it something I have to live with.

thankyou
Richard

[Wikkleyn_81]

@cottrera If the Semantic Models refresh are triggered using Power Automate, I would ask them to something in the line to below. 

In SSIS add a final job step that:
- Executes a stored procedure.

- Updates a control table (e.g., ETL_Run_Status).

- Inserts a record into an "ETL Complete" table.

The Power Automate flow with a SQL trigger such as: When a row is added, modified, or deleted. 

Then start with the Model refreshes.

[GilbertQ]

Hi @cottrera 

I suspect this is happening because when the refresh is going to happen it looks to the gateway and if the gateway is offline it then assumes that that connection is no longer active. What I would recommend doing is chit chat to the team to have the gateways always on as I really don't see you are. It should be off and then cause all these additional headaches. Which would take more time and effort (in cost terms) instead of just leaving the gateways on!

[Parchitect]

You shouldn't have to live with this. A gateway that's merely offline makes refreshes fail, but the dataset-to-gateway mapping is stored configuration — it shouldn't vanish just because the service was down. When mappings drop daily, something in the nightly off/on process is changing identity or visibility, and that's findable.

One mechanic worth knowing: in the semantic model settings, the gateway list is filtered — it "only lists gateways that have matching data source definitions for every on-premises source used". So anything the nightly process changes about those data source definitions, connections, or your access to them will surface exactly as what you're seeing.

Three checks that each point to a different cause:

1. Ask the DW team exactly how they "switch off" the gateways. Stopping the Windows service is one thing — but if their script disables, removes, or recreates connections or data sources, that's the answer: recreated connections are new objects, and datasets bound to the old ones need manual remapping.
2. Next time one breaks, before fixing it: check whether the dataset's owner / "configured by" changed overnight (takeovers are a classic cause of connection settings resetting), and whether "Maps to" is empty versus showing an error.
3. Compare refresh history timestamps on the broken datasets against the gateway-off window — if the broken ones are those your Power Automate flow hit before the gateways were fully back, add a status check or delay at the start of the flow.

The bigger fix, though: you may not need the bounce at all. You already orchestrate refreshes after the DW run finishes — so disable the schedules on these models and let the flow do all refreshing, with the gateway left up 24/7. If the DW must also be protected from report queries during the load, block that at the database end (for example, disable the BI service account during the window) rather than bouncing gateway infrastructure everything depends on. No daily bounce, no daily remapping lottery.

The likely cause is #1— the script touches the connections, not just the service. Can you share what it actually does?

Reference: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot

🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist

💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.