Sharepoint List Source Error -A possible reason might be you are trying to retrieve too many items at a time (Batch size)

  • I am trying to get data from a Sharepoint List using the following CAML Query in SharePoint List Source

    <Query><Where><Contains><FieldRef Name='Function Affected'/><Value Type='Text'>IT</Value></Contains></Where></Query>

    I am able to query some other columns from the same SharePoint List without any error msg

    I have set the Batch Size to values between 10 and 1000, but still am getting the following error message.

    [SharePoint List Source [1]] Error: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unspecified SharePoint Error. A possible reason might be you are trying to retrieve too many items at a time (Batch size) ---> System.ServiceModel.FaultException: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs) 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.ListsService.ListsSoap.GetListItems(GetListItemsRequest request) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.ListsService_ListsSoap_GetListItems(GetListItemsRequest request) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.GetListItems(String listName, String viewName, XElement query, XElement viewFields, String rowLimit, XElement queryOptions, String webID) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointListItems(String listName, String viewId, XElement queryXml, XElement viewXml, Int16 pagingSize, XElement queryOptionsXml) --- End of inner exception stack trace --- at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointListItems(String listName, String viewId, XElement queryXml, XElement viewXml, Int16 pagingSize, XElement queryOptionsXml) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointListItemData(String listName, String viewId, IEnumerable`1 fieldNames, XElement query, Boolean isRecursive, Int16 pagingSize) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListServiceUtility.GetListItemData(Uri sharepointUri, NetworkCredential credentials, String listName, String viewName, IEnumerable`1 fieldNames, XElement query, Boolean isRecursive, Int16 pagingSize) at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

  • I have found exactly the same error. The following steps helped me.

    1. Make sure that you are executing in 32 bit.

    2. In the Show Advanced Editor for the Sharepoint List Adapter Source, go to Input and Output Properties and remove the unnecessary columns from there.

    That resolved my issue.

    Thanks

    A. Pillay

  • Did you ever find a resolution to this? I am running the x86 adapter and I am only trying to pull in one field, but still no luck.

  • apillay2 (6/12/2012)


    I have found exactly the same error. The following steps helped me.

    1. Make sure that you are executing in 32 bit.

    2. In the Show Advanced Editor for the Sharepoint List Adapter Source, go to Input and Output Properties and remove the unnecessary columns from there.

    That resolved my issue.

    Thanks

    A. Pillay

    Hey Dude! You helped me !! It solved my same issue 🙂 Thanks a lot !!!

  • Hi,

    Removing unnecessary columns fixed the problem. Thank you!

  • I have only one field but still getting an same error and running package in 32 bit

  • I am also facing the same problem and it is not resolved by running with 32bit. Also I am fetching only one column. Please suggest.

  • montu.saurabh (5/4/2014)


    I am also facing the same problem and it is not resolved by running with 32bit. Also I am fetching only one column. Please suggest.

    Which version of SSIS are you running? If SSIS 2012 or 2014, you can use the new OData Source[/url]. It works pretty great and you can easily select one column using the query parameters.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • any updates on this?

    I'm running an ETL pulling 3 lists each with multiple columns however one has many more fields than the other 2 and that's the one which is failing. All running in the same job and not running 32 bit mode, all unused columns are removed.

    I initially ran into problems with the fact that ssis determines meta data based on only a subset of rows so i ended up with columns that were too small, ive since increased those to nvarchar 4000 due to the fact that I don't know how big they could be. Also SSIS determined that some columns are ntext. So the buffer is quite large.

    The weird thing is that this runs fine on my workstation in SSDT but gets the "too many items" error when running on the 2012 server.

    I can modify the columns since I know some don't need to be that big, however I'd like to first get an idea of what the actual problem is.

    One thought was to pull in the data using 2 separate sources and grab half the columns from each and join them up but again would like to know the actual issue.

    thanks,

    Tom

  • Replying even though it is an old post .

    I too faced a similar issue and tried to remove unwanted columns etc m, but it dint work.

    The Issue was that for some columns the heading name displayed in share-point and SSIS was slightly different from the name that has to be used for CAML Query.

    You can dowload the CAML Query helper @ https://spcamlqueryhelper.codeplex.com/ , Compare the column name and build/Test your query in it.

    Eg : Field Name in Sharepoint : Building Name

    Field Name to be used in CAML Query was : <FieldRef Name="Building_x0020_Name" />

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply