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