none
Error importing SharePoint 2010 list into SQL Server 2008 R2 database through SSIS

    Question

  • Hi,

    I want to import SharePoint 2010 list data into a different SQL Server 2008 R2 database. The table structure for each list was created through exporting lists to excel and then using Import and Export wizard. I created an SSIS package using SharePoint SSIS Adapter. When I execute the package to test from BIDS, I get following error through SendMail task:

    Error code: -1073450910
    Error description: System.Runtime.InteropServices.COMException (0xC0047020): Exception from HRESULT: 0xC0047020
       at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100.AddRow(IntPtr ppRowStart)
       at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.AddRow()
       at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)


    Error code: -1073450952
    Error description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "SharePoint List Source - SP Sample" (2806) returned error code 0xC0047020.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.



    Error code: -1073450910
    Error description: 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(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

    BatchSize 1000 (some of the lists had 5000 records)

    DelayValidation = True

    Run64BitRuntime = False

    Please point out what wrong am I doing.

    Thanks in advance,

    Puja


    Puja

    Sunday, June 23, 2013 4:09 AM

Answers

  • Puja,

    One of your columns overflows the allocated limit. Check which column is causing the error and increase the size in the SharePoint Source component.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    • Marked as answer by PShah109 Tuesday, July 23, 2013 5:28 PM
    Friday, July 19, 2013 8:04 PM
  • Hi Puja,

    The error above is clear. You have a SharePoint server limit. You have to talk with your administrator to raise the limit.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    • Marked as answer by PShah109 Tuesday, July 23, 2013 5:28 PM
    Tuesday, July 16, 2013 7:55 PM

All replies

  • Hi Puja,

    Try the 64 bit mode = true


    Arthur My Blog

    Monday, June 24, 2013 5:33 PM
  • Hi Arthur,

    I did try with that, but the package is still failing.


    Puja

    Monday, June 24, 2013 5:39 PM
  • Then you may want to submit this error to the component site issues section.

    Arthur My Blog

    Monday, June 24, 2013 5:43 PM
  • I would suggest you post your issue on the issues page on codeplex.  However, I do see a comment of problems when >10,000 items are in this list.  Is that possible?

    Monday, June 24, 2013 7:08 PM
  • The list has maximum 5085 items. It is not that large list. Let me check for permissions on SharePoint lists, may be that could be a problem. I think this because, I can successfully import data from SharePoint development site and not from the production one.

    Puja

    Monday, June 24, 2013 7:13 PM
  • Try to reduce the BatchSize parameter. Try with 500 or even 100.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, June 24, 2013 8:02 PM
  • No luck changing the batch size. I have admin rights on development site, but just read-only access on production site. Could this be a problem?

    Puja

    Monday, June 24, 2013 9:06 PM
  • No luck changing the batch size. I have admin rights on development site, but just read-only access on production site. Could this be a problem?

    Puja

    Do a Fiddler trace session. Then post a copy of the log file.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, June 24, 2013 9:20 PM
  • I executed the package with admin account (admin or SQL Server as well as on SharePoint site), out of 6 SharePoint lists, 2 returned errors. # of records in SP-1: 3419 and # of records in SP-2 : 641.

    Tried keeping batch size as 10/50/100/500/1000. Nothing worked. Is there any other security at list level which is blocking the connection?

    I cannot install Fiddler as it is not allowed.


    Puja

    Tuesday, June 25, 2013 8:24 PM
  • I executed the package with admin account (admin or SQL Server as well as on SharePoint site), out of 6 SharePoint lists, 2 returned errors. # of records in SP-1: 3419 and # of records in SP-2 : 641.

    Tried keeping batch size as 10/50/100/500/1000. Nothing worked. Is there any other security at list level which is blocking the connection?

    I cannot install Fiddler as it is not allowed.


    Puja

    Puja,

    Install Fiddler on a test system and try from there. Or install and run from inside Virtual Machine (VMWare?).

    Btw this is the error you have to be watching for:

    Error description: 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.

    You may also try doing tracing from the SharePoint server side. There is an exception generated from the server side.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Tuesday, June 25, 2013 8:27 PM
  • Hi,

    I will post the fiddler trace when it is installed and I take the trace. I have unmarked this as answer for now.

    Thanks,


    Puja

    Monday, July 01, 2013 2:51 PM
  • Hi,

    Here is the fiddler trace:

    POST http://prod.domain.com/_vti_bin/lists.asmx HTTP/1.1
    Content-Type: text/xml; charset=utf-8
    SOAPAction: "http://schemas.microsoft.com/sharepoint/soap/GetListItems"
    Host: prod.domain.com
    Content-Length: 2944
    Expect: 100-continue

    <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><listName>SP

    Section</listName><query><Query xmlns=""/></query><viewFields><ViewFields xmlns=""><FieldRef Name="ID"/><FieldRef Name="ContentType"/><FieldRef Name="Title"/><FieldRef Name="Modified"/><FieldRef

    Name="Editor"/><FieldRef Name="_UIVersionString"/><FieldRef Name="SSBSECT_SUBJ_CODE"/><FieldRef Name="SSBSECT_CRSE_NUMB"/><FieldRef Name="SSBSECT_SEQ_NUMB"/><FieldRef

    Name="SSBSECT_PTRM_START_DATE"/><FieldRef Name="SSBSECT_PTRM_END_DATE"/><FieldRef Name="SSBSECT_PTRM_WEEKS"/><FieldRef Name="SSBSECT_CREDIT_HRS"/><FieldRef Name="SSBSECT_PROJ_ENRL"/><FieldRef

    Name="SSBSECT_MAX_ENRL"/><FieldRef Name="SSBSECT_VOICE_AVAIL"/><FieldRef Name="SSBSECT_CENSUS_2_DATE"/><FieldRef Name="SSBSECT_INTG_CDE"/><FieldRef Name="SSBSECT_PTRM_CODE"/><FieldRef

    Name="SSBSECT_LEC_HR"/><FieldRef Name="SSBSECT_LAB_HR"/><FieldRef Name="SSBSECT_OTH_HR"/><FieldRef Name="SSBSECT_BILL_HRS"/><FieldRef Name="SSBSECT_CAPP_PREREQ_TEST_IND"/><FieldRef

    Name="SSBSECT_TERM_CODE"/><FieldRef Name="SSBSECT_CRN"/><FieldRef Name="SSBXLST_MAX_ENRL"/><FieldRef Name="SSBXLST_XLST_GROUP"/><FieldRef Name="SSRCORQ_CRN_CORQ"/><FieldRef

    Name="SSBDESC_TEXT_NARRATIVE"/><FieldRef Name="SSRXLST_XLST_CRN"/><FieldRef Name="SSBSECT_ENRL"/><FieldRef Name="SSBXLST_ENRL"/><FieldRef Name="HCSP_NUMBER_MEETINGS"/><FieldRef

    Name="SCBCRSE_TITLE"/><FieldRef Name="Course"/><FieldRef Name="SSBSECT_CAMP_CODE"/><FieldRef Name="SSBSECT_SSTS_CODE"/><FieldRef Name="SSBSECT_SESS_CODE"/><FieldRef Name="SSBSECT_SCHD_CODE"/><FieldRef

    Name="SSBSECT_INSM_CODE"/><FieldRef Name="SSBSECT_GMOD_CODE"/><FieldRef Name="HCSP_SP_STATUS"/><FieldRef Name="HCSP_COURSE_KEY"/><FieldRef Name="HCSP_SECTION_KEY"/><FieldRef

    Name="HCSP_REF_TERM_CODE"/><FieldRef Name="HCSP_REF_CRN"/><FieldRef Name="HCSP_REF_SECTION_KEY"/><FieldRef Name="HCSP_REF_COURSE_KEY"/><FieldRef Name="HCSP_REF_ROWID"/><FieldRef

    Name="HCSP_SP_STATUS_COMMENTS"/><FieldRef Name="HiddenCourseId"/><FieldRef Name="SSBDESC_TERM_CODE"/><FieldRef Name="SSRCORQ_TERM_CODE"/><FieldRef Name="SSRRARE_AREA"/><FieldRef

    Name="SSRRARE_TERM_CODE"/><FieldRef Name="SSBSECT_PREREQ_CHK_METHOD_CDE"/><FieldRef Name="Created"/><FieldRef Name="Author"/><FieldRef Name="Attachments"/><FieldRef Name="Edit"/><FieldRef

    Name="LinkTitleNoMenu"/><FieldRef Name="LinkTitle"/><FieldRef Name="DocIcon"/><FieldRef Name="ItemChildCount"/><FieldRef Name="FolderChildCount"/><FieldRef

    Name="Course_x003a_SCBCRSE_TITLE"/></ViewFields></viewFields><rowLimit>1000</rowLimit><queryOptions><QueryOptions

    xmlns=""><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns><IncludeAttachmentUrls>FALSE</IncludeAttachmentUrls><Paging ListItemCollectionPositionNext=""/><ViewAttributes

    Scope="RecursiveAll"></ViewAttributes></QueryOptions></queryOptions></GetListItems></s:Body></s:Envelope>
    HTTP/1.1 500 Internal Server Error
    Cache-Control: private
    Content-Type: text/xml; charset=utf-8
    Server: Microsoft-IIS/7.5
    X-AspNet-Version: 2.0.50727
    X-Powered-By: ASP.NET
    MicrosoftSharePointTeamServices: 14.0.0.6112
    Date: Tue, 16 Jul 2013 19:15:39 GMT
    Content-Length: 745

    <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was

    thrown.</faultstring><detail><errorstring xmlns="http://schemas.microsoft.com/sharepoint/soap/">The query cannot be completed because the number of lookup columns it contains exceeds the lookup column

    threshold enforced by the administrator.</errorstring><errorcode xmlns="http://schemas.microsoft.com/sharepoint/soap/">0x80070093</errorcode></detail></soap:Fault></soap:Body></soap:Envelope>

    There are just 2 lookup columns for this SharePoint list. However, for other SharePoint list I don't get error for having more than 2 lookup fields.

    Thanks,


    Puja

    Tuesday, July 16, 2013 7:24 PM
  • Hi Puja,

    The error above is clear. You have a SharePoint server limit. You have to talk with your administrator to raise the limit.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    • Marked as answer by PShah109 Tuesday, July 23, 2013 5:28 PM
    Tuesday, July 16, 2013 7:55 PM
  • Hi,

    The lookup limit is now raised to 50, but still getting same error in Fiddler. What could be the issue?


    Puja

    Friday, July 19, 2013 2:53 PM
  • Hi,

    The lookup limit is now raised to 50, but still getting same error in Fiddler. What could be the issue?


    Puja

    Puja,

    Did you restart the SharePoint server? If you are still seeing issues, the problem is still with your server. Discuss with your admin.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, July 19, 2013 4:26 PM
  • Hi,

    The admin made changes to a test site instead of the production one. Setting the limit to 50 worked on production, but getting buffering error:

    Error description: Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

       at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

       at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

    I followed the solution posted on http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eeb64df2-1970-46f5-aa97-9824c33f3171/buffer-issue-on-one-particular-column-in-an-ssis-package but no luck :(


    Puja

    Friday, July 19, 2013 7:35 PM
  • Puja,

    One of your columns overflows the allocated limit. Check which column is causing the error and increase the size in the SharePoint Source component.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    • Marked as answer by PShah109 Tuesday, July 23, 2013 5:28 PM
    Friday, July 19, 2013 8:04 PM
  • Hi,

    The error is resolved!

    One of the columns was using DT_WSTR(30), but it was actually holding DT_WSTR(4000). Made changes in "Input and Output Properties"> "External columns" in Advanced Editor.

    Thanks a ton!


    Puja


    • Edited by PShah109 Tuesday, July 23, 2013 5:27 PM correction
    Tuesday, July 23, 2013 5:20 PM