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

%3CLINGO-SUB%20id%3D%22lingo-sub-1235726%22%20slang%3D%22es-ES%22%3EODataQuery%20Error%20in%20SSIS%20and%20Data%20Factory%20but%20not%20in%20PBI%20or%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1235726%22%20slang%3D%22es-ES%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20not%20able%20to%20connect%20a%20data%20source%20using%20OData%20with%20SSIS%20or%20Data%20Factory.%20At%20the%20same%20origin%20I%20can%20connect%20both%20excel%20and%20PBI.%20The%20source%20is%20a%20link%20to%20a%20query%20that%20I%20have%20published%20in%20a%20Blackbaud%20CRM%20%3CA%20href%3D%22https%3A%2F%2Fxxxxxxx.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3D4xxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxx.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3D4xxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPBI%20connection%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Elet%3CBR%20%2F%3ESource%20-%20OData.Feed(%22%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxx.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3Dxxxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxxx.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3Dxxxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%3C%2FA%3E%22%2C%20null%2C%20%5BImplementation%22%222.0%22%5D)%3CBR%20%2F%3Ein%20Source%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EThe%20credential%20is%20windows%20authentication%20usign%20altenate%20credentials%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20connection%3A%3C%2FP%3E%3CP%3EData%20Source-%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxxs.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3Dxxxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%3BNamespaces%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxxxs.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3Dxxxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%3B%20Namespaces%3C%2FA%3E%20to%20Include*%3B%20Max%20Received%20Message%20Size-4398046511104%3BIntegrated%20Security-Basic%3B%20User%20ID-XXX-xxxxxxxx%3B%20Persist%20Security%20Info-false%3B%20Base%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxs.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3Dxxxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EUrl-https%3A%2F%2Fxxxxxxxs.sky.blackbaud.com%2Fxxxxxs%2FODataQuery.ashx%3Fdatabasename%3Dxxxxxs%26amp%3BAdHocQueryID%3Df5c3d3ac-0477-4fac-b18b-4402b8abbc8a%3C%2FA%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EData%20Factory%20Connection%3A%3C%2FP%3E%3CP%3EIn%20Data%20Factory%20I've%20tried%20with%20windows%20and%20Basic%20Authentication%20and%20always%20the%20same%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EError%3C%2FP%3E%3CP%3E%3CSPAN%3EFailed%20to%20create%20OData%20connection%20to%20RequestUrl%20Fail%20to%20get%20metadata%20of%20odata%20service%2C%20please%20check%20if%20service%20url%20and%20credential%20is%20correct.%20Activity%20ID%3A%20a29ac5c2-def7-40e1-b90a-ec5985cd8083.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20SSIS%20I've%20tried%20basic%20authentication%20and%20windows%20aut%20(starting%20SSDT%20with%20the%20same%20account%20I%20connect%20via%20Excel)%20and%20I%20always%20get%20the%20same%20error%20message%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EFailed%20to%20check%20connection%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3ERemote%20server%20error%3A%20(401)%20Unauthorized.%20(System)%3C%2FP%3E%3CP%3E------------------------------%3CBR%20%2F%3EProgram%20Location%3A%3C%2FP%3E%3CP%3Ein%20System.Net.HttpWebRequest.GetResponse()%3CBR%20%2F%3Ein%20Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.TestConnectionMiddle(Object%20callback)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

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-0...

 

PBI connection:

let
Origen = OData.Feed("https://xxxxxxxx.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac-...", 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... 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-...

 

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!!!

0 Replies