Forum Discussion

jjponce's avatar
jjponce
Copper Contributor
Mar 18, 2020

ODataQuery Error in SSIS and Data Factory but not in PBI or Excel

Hello

I am not able to connect a data source using OData with SSIS or Data Factory. At the same origin I can connect both excel and PBI. The source is a link to a query that I have published in a Blackbaud CRM https://xxxxxxx.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=4xxxxs&AdHocQueryID=f5c3d3ac-0477-4fac-b18b-4402b8abbc8a

 

PBI connection:

let
Origen = OData.Feed("https://xxxxxxxx.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac-0477-4fac-b18b-4402b8abbc8a", null, [Implementation="2.0"])
in Origen

 

The credential is windows authentication usign altenate credentials

 

Excel connection:

Data Source=https://xxxxxxxxs.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac-0477-4fac-b18b-4402b8abbc8a;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;User ID=XXX\xxxxxxxx;Persist Security Info=false;Base Url=https://xxxxxxxs.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac-0477-4fac-b18b-4402b8abbc8a

 

Data Factory Connection:

In Data Factory I've tried with windows and Basic Authentication and always the same error

 

Error

Failed to create OData connection to RequestUrl Fail to get metadata of odata service, please check if service url and credential is correct. Activity ID: a29ac5c2-def7-40e1-b90a-ec5985cd8083.

 

With SSIS I've tried basic authentication and windows aut (starting SSDT with the same account I connect via Excel) and I always get the same error message 

 

===================================

No se pudo comprobar la conexión

===================================

Error en el servidor remoto: (401) No autorizado. (System)

------------------------------
Ubicación del programa:

en System.Net.HttpWebRequest.GetResponse()
en Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.TestConnectionMiddle(Object callback)

 

Thanks a lot!!!

No RepliesBe the first to reply

Resources