Forum Discussion
OData connection error using Data Gateway
Hi All, I would like to test the ODATA connector using PowerBI as a source with "data gateway" (1) In powerbi.com - I have successfully configured the Data Gateway Cluster on the machine (On-Premis...
Hi, Anonymous
This shows that the problem is indeed the url format, because this kind of custom URL may not be accepted by PBI service. I want to know if the error in the service after you remove the (), is it still the same as before, or is it different? Have you tried changing odbc connector to web connector?
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-janeyg-msft ,
I have deleted the parantheses "() "and the error is as follows:
Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster.
Please find more details below about specific errors for each gateway instance."Hide details
Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
TGKDEMO: Unable to connect to the data source undefined.
Underlying error code: -2147467259
Underlying error message: We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)'
is not valid for the expected payload kind 'Collection'.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: OData
Microsoft.Data.Mashup.ValueError.DataSourcePath: http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000002
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
I have tried with web connector, it works fine but the data output is not correct (the structure does not reflect the data present in the XML). In local PowerBI, I see the correct result only when I use the ODATA Connector. For this reason, I need to use the ODATA type as data source for the data gateway.
Anonymous
Please try to upgrade the gateway to the latest version or use a personal gateway.
Reference: Update an on-premises data gateway | Microsoft Docs
If it doesn't work, I think there is nothing I can do, because probably pbi service can't accept this form of url in odata.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-janeyg-msft ,
I have upgraded the gateway and it's already connected but I have the same error.
I extract this log from the data gateway service... it might help us...
thanksDM.EnterpriseGateway Error: 0 : 2021-05-28T06:30:41.0858693Z DM.EnterpriseGateway 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 0000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 25E31978 [DM.Analytics] Performance counters not installed, application performance metrics will not be traced (category = Microsoft Power BI Enterprise Gateway) Starting trace on 5/28/2021 7:35:53 AM UTC UserDomainName: NT SERVICE UserName: PBIEgwService MachineName: DH-TEMPLATE DM.EnterpriseGateway Error: 0 : 2021-05-28T07:55:46.8108664Z DM.EnterpriseGateway 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 0000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 25E31978 [DM.Analytics] Performance counters not installed, application performance metrics will not be traced (category = Microsoft Power BI Enterprise Gateway) DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 727CA6CB [DM.Pipeline.Diagnostics] Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: .; ErrorShortName: MashupDataAccessValueException[ErrorCode=-2147467259,HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259,HResult=-2147467259] DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 8AA61592 [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorCode = <pi>-2147467259</pi> DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 57671F5E [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorMessage = <pi><pi>We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'.</pi></pi> DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 6A5E25DC [DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingHResult = <pi>-2147467259</pi> DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 869CC7B9 [DM.Pipeline.Diagnostics] Exception data: Microsoft.Data.Mashup.ValueError.DataSourceKind = <pi>OData</pi> DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 776F86BB [DM.Pipeline.Diagnostics] Exception data: Microsoft.Data.Mashup.ValueError.DataSourcePath = <pi>http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/Datasource_000002()</pi> DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1782060Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 7BA84428 [DM.Pipeline.Diagnostics] Exception data: Microsoft.Data.Mashup.ValueError.Reason = <pi>DataSource.Error</pi> DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1801627Z DM.EnterpriseGateway 1df837c5-441c-4efa-8d0a-4535b4b5ea81 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGTD 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 1225614D [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=2055, err=MashupDataAccessValueException, rootcauseErrorEventId=0) DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1801627Z DM.EnterpriseGateway ac9af32c-d963-4947-97e6-21cd5a85c0ac 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGEC 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 669B8A7A [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=2085, err=MashupDataAccessValueException, rootcauseErrorEventId=0) DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.1811409Z DM.EnterpriseGateway 5438b017-9d88-41e0-a808-edd376cbd666 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MGPP 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a 171C71E8 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=2090, err=MashupDataAccessValueException, rootcauseErrorEventId=0) DM.EnterpriseGateway Error: 0 : 2021-05-28T08:16:56.2465725Z DM.EnterpriseGateway af4d5279-7eb4-4fa9-bd91-c5144bc256ec 34ca72cb-2844-ebdb-43d5-e7f2d7cc9db3 MDSR 607425aa-07c7-461a-8a0d-72993ff5ac97 b5555749-b87c-4516-99c9-3940c2cb167a b5555749-b87c-4516-99c9-3940c2cb167a ED937467 [DM.GatewayCore] Error processing request: [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: . GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError GatewayVersion= Reason= ---> [1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'. at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader() at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()</pi> GatewayPipelineErrorCode=DM_GWPipeline_UnknownError GatewayVersion= InnerType=MashupValueException InnerMessage=<pi>We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'.</pi> InnerToString=<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): We couldn't parse OData response result. Error: The context URI 'http://localhost:9090/tagetikcpm/api/aih/data/v1/TGK_06/001/$metadata#Collection(CCHTagetik.Aih.type_000002)' is not valid for the expected payload kind 'Collection'. at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader() at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext()</pi> InnerCallStack= at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader() at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext() ([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace:) at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass46_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader() at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext() --- End of inner exception stack trace --- ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException.StackTrace:) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsyncImpl>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__4.MoveNext()