locked
[SharePoint List Source [296]] Error: Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a running package which reads data from SharePoint List and dumps into SQL tables. I have 3 new list columns which I want to update into SQL table.

    1) I opened container for this list and table

    2) Refreshed mapping tab in SharePoint list source item properties

    3) mapped columns in OLE DB Destination

    When I ran this container, i got following error.


    [SharePoint List Source [296]] Error: 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)

    Soruce Sharepoint list columns are string (255 chars) and date time. Thus I tried setting external column size as 260 chars and output columns as 300 chars, to avoid any truncation or issues. But still same issue.

    Question: How can I verify which column or data is causing issue?

    Container snapshot

    DataFlow snap


    • Edited by ShahidAliK Tuesday, August 11, 2020 8:20 AM updated with error screenshot
    Monday, August 10, 2020 12:02 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ShahidAliK,

    We can add error flow redirection after the source to extract the error data.

    The following links will be helpful:

      Lesson 4-3: Add error flow redirection

      An overview of Error Handling in SSIS packages

    Best Regards,

    Mona

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 11, 2020 5:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I tried adding error output to write defective records into a flat file. But unfortunately its not reaching till derived columns activity. Its failing in first step while reading itself, i guess.

    I have updated screenshot in question. Thank you for reply.

    Tuesday, August 11, 2020 8:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ShahidAliK,

    Please edit the Error Output in the source.

    We can also use OData Source to read SharePoint Lists data.

    Please refer to the following links:

      OData Source

      Reading SharePoint Lists with Integration Services 2017

    Best Regards,

    Mona

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 12, 2020 2:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I am getting an issue while connecting it through oData source. I am able to connect other sites and read list data but my site is secured with LDAP authentication and hence few pages are accessible without login like login, About us and rest content is secured.

    Somewhere I read that oData needs only connects to secured sites. If I can use oData source with LDAP or not, if yes, how to configure credentials. I tried putting domain and username password but that did not help.


    • Edited by ShahidAliK Wednesday, August 19, 2020 12:36 PM more info
    Wednesday, August 19, 2020 12:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ShahidAliK,

    May I know if you can set Error output in the SharePoint List Source Editor?

    Best Regards,

    Mona

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 20, 2020 8:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    No, I could not add/configure error output for "Sharepoint List source" as there is no option for this component. Its available for "derived columns" or "OLEDb destination" only.

    If you can suggest how to do it or alternative to it. I searched on internet but unable to find any relevant info.


    • Edited by ShahidAliK Friday, August 21, 2020 7:41 PM correction in grammmer
    Friday, August 21, 2020 7:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ShahidAliK,

    May I know if you can add the length of the columns?

    Best Regards,

    Mona

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 24, 2020 8:40 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes, I can modify length and type as well. So I tried following.

    1) Sharepoint list column - 255 Arabic/English text

    2) External column :  260 Unicode DT_WSTR

    3) Output column :  300 Unicode DT_WSTR

    Addl. info, this package is working in production without any issue. I exported from production and now modifying it for adding 3 new columns and this issue arises. There are many columns in this list with same datatype (string, 255) but I am unable to find the root cause.

    Monday, August 24, 2020 9:46 AM