While trying using SharePoint List Source and Destination in one of my SSIS package I kept getting the error The remote server returned an error 401 Unauthorized.
Executed as user: domainName\serviceAccountName. …rsion 10.x.x.x for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:36:21 PM Error: 2014-01-01 Code: 0xC0047062 Source: [data flow name] [SharePoint List Source] Description: System.ServiceModel.Security.MessageSecurityException: The HTTP request is unauthorized with client authentication scheme ‘Ntlm’. The authentication header received from the server was ‘NTLM’. —> System.Net.WebException: The remote server returned an error: (401) Unauthorized.
I read many forums and blogs that suggest changing the credentialType to Windows, to use the 2012 beta version of the list task, change some configuration on Registry none of that worked for me.
What did work for me was instead of specifying a username and password for custom credentials. I changed to Use Credentials of Executing Process.
Make sure the service account of your SSIS server has enough privilege on the list you are working on. If by any change your SharePoint admin is not going give your Service Account rights to access the list you are working on or if you must use a different account you need to create a credential that references the your other user, add a proxy that references the credential then assign the proxy to the SSIS package to execute the package as the context of that account.