none
SharePoint List Destination Fails on a Job but manually and in power shell runs fine

    Question

  • Hello everyone... Im having a problem with a Integration Services 2008 package.  Im using an OLE DB Source and a SharePoint List Destination... when I run it from the BIDS works fine... when I run it from power shell... works fine...   when I try to run it from a transact sql command... it says that the job run successfully but nothing is done at all and if I go into the history the error is the one I post bellow also...

    and finally  when I try to do it from a Job using Integration Services it can promt 2 things... a time out or an error or the following error.  

       Description: Error on row ID="1": 0x80070005 - Unspecified Error - Check SharePoint Server Logs if possible.

    Description: Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.PipelineProcessException: Errors detected in this component - see SSIS Errors

       at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)

       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

    End Error

    Error: 2011-08-02 19:07:46.34

       Code: 0xC0047022

       Source: Cat_AutContAgent SSIS.Pipeline

       Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "SharePoint List Destination 1" (821) failed with error code 0x80131500 while processing input "Component Input" (829). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

     

    I tried to use a Proxy account and use a sharepoint administrator...but no luck, it says something about the privileges...but that user is the most powerful user on sharepoint...

    Please can you give me an advice? I really need to automatize the package and Im running out of ideas...

     

    Thank you in advance...

    Regards

    Adriana

    Wednesday, August 03, 2011 2:20 AM

Answers

  • Hi adriana legorreta,

    Here are a couple of things you can look at before changing the timeout:


    1. How much memory do you have on the SQL box running the package? Everything is pulled into memory, so if you have 30000 rows at - let's say - 150KB per row (I'm guessing, here), you've now attempted to pull 4.5 gig of data into memory. That would definitely slow things down.
    2. Make sure your source data access mode is "SQL Command" instead of "Table or View" - it's (most of the time) more efficient.
    3. In the properties of the data flow task, try playing around with the "DefaultBufferMaxRows" - it defaults to 10000.
    4. Check your indexes on your source table. Seven seconds sounds like a long time for a 30000 row return set.

    Hope that help!

    Thanks,
    Eileen


    Friday, August 19, 2011 8:19 AM

All replies

  • Hello,

    Well its been a nightmare... but I made it work...

    --> I added the SQL Server Agent account to the sharepoint Owners... so now it works running a package from a job.

    Next issue:  The time out... I have to add a dummy data flow that reads from a sharepoint list and writes to a sql database and then the next data flow...the one I care ... to read data from sql database and write it to a sharepoint list.    

    I DONT UNDERSTAND !!! why adding this to the etl... makes the time out go away. 

    =/

     

    Wednesday, August 03, 2011 2:50 PM
  • Next issue:  The time out... I have to add a dummy data flow that reads from a sharepoint list and writes to a sql database and then the next data flow...the one I care ... to read data from sql database and write it to a sharepoint list.    

    I DONT UNDERSTAND !!! why adding this to the etl... makes the time out go away. 

    =/

     

    I don't understand what is your issue here? did you get timeout error? or any other error? where in first data flow or second one? if yes what is exactly error message?

    http://www.rad.pasfu.com
    My Submitted sessions at sqlbits.com
    Wednesday, August 03, 2011 8:15 PM
  •  

    Yes I get timeout error.

     

    1. Data Flow Task  ( just goes and read a dummy list in sharepoint=)

    2. Data Flot Tast (Extract from SQL, evaluates with a conditional split, and insert into sharepoint list)

     

    If I take out the first data flow and the time out error is offical on the ETL.

     

    Thursday, August 04, 2011 9:25 PM
  • Hi adriana legorreta,

    Here are a couple of things you can look at before changing the timeout:


    1. How much memory do you have on the SQL box running the package? Everything is pulled into memory, so if you have 30000 rows at - let's say - 150KB per row (I'm guessing, here), you've now attempted to pull 4.5 gig of data into memory. That would definitely slow things down.
    2. Make sure your source data access mode is "SQL Command" instead of "Table or View" - it's (most of the time) more efficient.
    3. In the properties of the data flow task, try playing around with the "DefaultBufferMaxRows" - it defaults to 10000.
    4. Check your indexes on your source table. Seven seconds sounds like a long time for a 30000 row return set.

    Hope that help!

    Thanks,
    Eileen


    Friday, August 19, 2011 8:19 AM
  • Hi Eileen,

     

    Thank you for your tips, you are right, we changed the Batch size to one( it might take more time but it helped not to have the timeout error) in the sharepoint list destination properties but is definetly it has to do with some things you suggested. I realized that even though a time out error was prompting the files where inserted on the sharepoint list... the thing is that it stopped the process there and therefore the other steps couldn`t make it.

     

    Once again thank you for your time and answers.

     

    Regards

    Adriana

    Friday, August 19, 2011 3:22 PM