Azure SSAS gateway connection issue - Received error payload from gateway service

Question

We process the Azure AS tabular model using SSIS on-prem (using a sql agent job).
Intermittently (maybe once a week on random days), we receive this error message:

The JSON DDL request failed with the following error: Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: Received error payload from gateway service with ID 494439: An exception encountered while accessing the target data source..

We have a retry on the sql agent job and 90% of the time it succeeds on the retry attempt so there's no action to be taken by us. But we'd like to fix this by finding the root cause. We're having a hard time troubleshooting what the issue could be and where to start.

Anyone else experience this?

Azure AS
SQL Server 2017 and the Microsoft on-prem data gateway

Answer 1

Hi @Gabriel Flores ,

Welcome to Microsoft Q&A Platform. Sorry that you are experiencing this intermittent issue. Please share your thoughts on this

• Please suggest if you are using latest version of on-premise gateway as upgrading helps sometimes to resolve this transient issues
• Please share compatibility version used in Azure AS model (check if it is 1400 as SQL server 2017 is specified in the description)
• Please share if Azure AS is in a region that is close to data source (On-premise in this case) and it is recommended to keep it close to avoid network issues
• Also since it is a transient issue, this also might be during downtime of the corresponding region which can be tracked here
• If not already seen, please check the documentation on troubleshooting on-premise gateway issues and see if it helps.

Please let us know if the issue appears again and does not fall in any of these categories, we will assist further in debugging the same.