Sql-server – SSIS Package with SharePoint fails when scheduled, but runs fine manually from SSIS Store (SQL 2008)

I have an SSIS job that gets data from a SharePoint 2010 list that runs perfectly from Visual Studio and also if I trigger it manually from the SSIS store, but not when scheduled as a job in the SQL Server DB Engine instance.

I am doing everything using just one super user account called "spadmin".

• It is the account I am using to log into a Windows 7 workstation from which I designed the SSIS package and I tend to run SSMS.
• Is the account used to log into SQL server and have full admin rights to the local server, and to SQL (everything).
• It is also this user's credentials that I am using in the SSIS package to connect to everything I need.
• It is also the account specified to run the scheduled job.

If I connect to the SQL server's SSIS store (not the DB engine) via SQL Server Management Studio (SSMS) and I right click on the job in question that is stored under "Stored Packages\MSDB" and choose to execute it, the job will run without any issues. This happens whether I am using the local SSMS installed on the SQL Server in question, or if I am using a SSMS installation on a remote workstation.

However if I schedule the job through the same SQL's server database engine, the job will fail — both on a schedule and if I try to run the job manually.

Now here is the puzzling bit: The job will not fail if I have on the background a remote desktop connection into the SQL server with that super user account (i.e. spadmin) while I run the job. By on the background I mean that I am not doing anything on this remote desktop connection except login in with the super user account.

When the job fails, I get the following "Bad Gateway" error (see end of post) that suggests the problem is accessing SharePoint. However since I can run this job via the SSIS store with the same account for which the job has been scheduled, there is no doubt that this job is capable of running from the SQL Server.

Server build: 10.50.1617

I am going mental here. Any ideas of what the problem might be?

Here is the full error message for completeness sake:

Message Executed as user: MYDOMAIN\spadmin. …n 10.50.1600.1 for
64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 13:19:34 Error: 2017-01-27 13:19:34.76 Code: 0xC0047062
Source: Copy sharepoint list data SharePoint List Source [1]
Description: System.ServiceModel.ProtocolException: The remote server
returned an unexpected response: (502) Bad Gateway. —>
System.Net.WebException: The remote server returned an error: (502)
Bad Gateway. at System.Net.HttpWebRequest.GetResponse() at
System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan
timeout) — End of inner exception stack trace — Server
stack trace: at
System.ServiceModel.Channels.HttpChannelUtilities.ValidateRequestReplyResponse(HttpWebRequest
request, HttpWebResponse response, HttpChannelFactory factory,
WebException responseException, ChannelBinding channelBinding) at
System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan
timeout) at
System.ServiceModel.Channels.RequestChannel.Request(Message message,
TimeSpan timeout) at
System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message
message, TimeSpan timeout) at
System.ServiceModel.Channels.ServiceChannel.Call(String action,
Boolean oneway, ProxyOperationRuntime operation, Object[] ins,
Object[] outs, TimeSpan timeout) at
System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage
methodCall, ProxyOperationRuntime operation) at
System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage
message) Exception rethrown at [0]: at
System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg) at
System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ViewsService.ViewsSoap.GetViewCollection(GetViewCollectionRequest
request) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ViewsService.ViewsSoapClient.ViewsService_ViewsSoap_GetViewCollection(GetViewCollectionRequest
request) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ViewsService.ViewsSoapClient.GetViewCollection(String
listName) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ViewsAdapter.GetSharePointListViews(String
listName) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ViewsAdapter.GetViewList(String
listName) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.LookupViewName(String
listName, String viewName) at
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListServiceUtility.GetFields(Uri
sharepointUri, NetworkCredential credentials, String listName, String
viewName) at
Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.GetAccessibleSharePointColumns(String
sharepointUrl, String listName, String viewName) at
Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.ValidateSharePointColumns()
at
Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.Validate()
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostValidate(IDTSManagedComponentWrapper100
wrapper) End Error Error: 2017-01-27 13:19:34.76 Code:
0xC0047017 Source: Copy sharepoint list data SSIS.Pipeline
Description: component "SharePoint List Source" (1) failed validation
and returned error code 0x80131501. End Error Error: 2017-01-27
13:19:34.76 Code: 0xC004700C Source: Copy sharepoint list data
SSIS.Pipeline Description: One or more component failed
validation. End Error Error: 2017-01-27 13:19:34.76 Code:
0xC0024107 Source: Copy sharepoint list data Description:
There were errors during task validation. End Error DTExec: The
package execution returned DTSER_FAILURE (1). S… The package
execution fa… The step failed.