May 31, 2012 at 2:44 pm
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)
June 12, 2012 at 11:05 am
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
September 23, 2013 at 12:56 pm
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.
October 16, 2013 at 1:10 am
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 !!!
October 17, 2013 at 11:07 am
Hi,
Removing unnecessary columns fixed the problem. Thank you!
April 28, 2014 at 1:09 pm
I have only one field but still getting an same error and running package in 32 bit
May 4, 2014 at 10:15 pm
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.
May 5, 2014 at 12:26 am
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
September 15, 2014 at 4:03 pm
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
October 27, 2015 at 10:09 am
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