I have an SSIS package that has a single OData source (sharepoint site source). The package runs fine from Visual Studio. Once deployed to a SQL instance I try to run the package via an Agent Job (uses the Agent service account to execute), and I get the following:
SharePoint List to Stage:Error: System.Net.WebException: The remote server returned an error: (500) Internal Server Error.
at System.Net.HttpWebRequest.GetResponse()
at Microsoft.SqlServer.IntegrationServices.DataFeedClient.Http.HttpWebRequestWrapper.GetResponse()
at Microsoft.SqlServer.IntegrationServices.DataFeedClient.Http.WebRequestHelper.GetResponseWithThreadSafeRetry(IWebRequest& request,
Func`1 createWebRequest, Int32 retryCount, Int32 retrySleepInMilliseconds, Boolean isOAuth, Func`2 refreshRequestWithNewAccessToken, Func`2
getStatusCode)
at
Microsoft.SqlServer.IntegrationServices.DataFeedClient.Http.WebRequestHelper.GetResponseWithThreadSafeRetryAndImpersonation(IWebRequest&
request, DataFeedConnection connection, Func`1 createWebRequest)
at Microsoft.SqlServer.IntegrationServices.DataFeedClient.DataFeedCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.IntegrationServices.OData.ODataSource.GetSchemaTable()
at Microsoft.SqlServer.IntegrationServices.OData.ODataSource.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)
The service account running the package has Full Control permissions on the SharePoint list in question. I have several other packages on the same server that use OData feed from the same SharePoint farm with the same service account without issue.
Turned out that the error was on the SharePoint server... the lookup column threshold in Central Admin was set to 8 (the default), but the number of lookup columns in the list exceeds that. Since I'm a farm admin, I bypass the restriction. I just wish that the error that goes through to SSIS was a little more explanatory than just (500).