SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sharepoint List Source Error -A possible reason might be you are trying to retrieve too many items...


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

Author
Message
LOOKUP_BI-756009
LOOKUP_BI-756009
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 1485
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)
apillay2
apillay2
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 128
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
bpowers
bpowers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1147 Visits: 960
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.
New Commer
New Commer
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 467
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 Smile Thanks a lot !!!
Alex Diaz-329446
Alex Diaz-329446
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 165
Hi,

Removing unnecessary columns fixed the problem. Thank you!
kdinuk
kdinuk
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 253
I have only one field but still getting an same error and running package in 32 bit
montu.saurabh
montu.saurabh
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 0
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58917 Visits: 13297
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. It works pretty great and you can easily select one column using the query parameters.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Tom Van Harpen
Tom Van Harpen
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2112 Visits: 1113
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
navinprabu
navinprabu
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 6
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" />
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search