Error: SQL Server Integration Services: Error at SharePoint Site

Description

You configure a SQL Server Integration Services package containing a SharePoint List Source or OData Source in a Data Flow.

When attempting to execute the SSIS package in SQL Server 2012 Data Tools, an error returns:

!  Error at SharePoint Site:  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.
at System.Net.HttpWebRequest.GetResponse()
at System.ServiceModel.Channels.HttpChannelFactory'1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpantimeout)
---End of inner exception stack trace---


Solution

1) Open the SSIS Project in SQL Server Data Tools

2) Right Click on the Project, Properties

3) Choose Debugging

4)  Change the Run64BitRuntime property from True to False (MS-SSIS Blog, 2012)

5)  Save the Project

6)  Attempt to run the package again


Key Words
SSIS, Run64BitRuntime


References

Codeplex (2011).  NTLM authentication error in SharePoint List Adapter for SSIS.  Retrieved February 25, 2015 from http://sqlsrvintegrationsrv.codeplex.com/discussions/269590.

Microsoft SQL Server Integration Services Blog (September, 2012).  Switch package from 64bit to 32bit.  Retrieved August 14, 2014 from
http://microsoft-ssis.blogspot.com/2012/09/swtich-package-from-64bit-to-32bit.html