Update SP List using Sharepoint Destination List

Sep 16, 2009 at 4:07 PM

I have several SSIS taks that delete and recreate SP lists and are working fine. I am trying to update two fields in a particular SP list which is giving me the following error:

    [SharePoint List Destination] Error: Error on row ID="1": 0x1 - Unspecified error, such as too many items being updated at once (batch), or an invalid core field value.

I am getting the data to update, including the Sharepoint ID from a database table. The fields are being mapped in the SP Destination List. I've checked all of the fields and they are of the same data type. I have also verified that the items do exist in the SP List.

Any help on this would be greatly appreciated.

Jon

Coordinator
Sep 29, 2009 at 4:45 AM

Which fields are the ones that are giving you issues specifically? 

Unspecified errors are hard ones, they are super vague and I was able to find a bunch out through trial and error.  Sometimes it can be a constraint on a column, or something that does not allow empties, or putting a number in a string field, stuff like that.  If you can narrow it down to specific fields, then use the SSIS Dataflow visualizer to examine the exact data going into that field (click on the line connecting the dataflow components) and try adding that data to the list yourself and see if SP gives you some sort of error.

 

Sep 29, 2009 at 9:07 AM

Kevin

First of all, thanks for responding to this issue. I have done a lot of searching on this and haven’t been able to find any remote resolutions. I will get what info I can and send it to you as soon as I can. Unfortunately we are dealing with confidential data so I don’t think I will be able to send you a data file. I will get what I can and send it off to you as soon as I can though.

Thanks

Jon

From: kevinidzi [mailto:notifications@codeplex.com]
Sent: Tuesday, September 29, 2009 12:46 AM
To: Wheeler, Jon
Subject: Re: Update SP List using Sharepoint Destination List [SQLSrvIntegrationSrv:69127]

From: kevinidzi

Which fields are the ones that are giving you issues specifically?

Unspecified errors are hard ones, they are super vague and I was able to find a bunch out through trial and error. Sometimes it can be a constraint on a column, or something that does not allow empties, or putting a number in a string field, stuff like that. If you can narrow it down to specific fields, then use the SSIS Dataflow visualizer to examine the exact data going into that field (click on the line connecting the dataflow components) and try adding that data to the list yourself and see if SP gives you some sort of error.

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLSrvIntegrationSrv@discussions.codeplex.com)

To start a new discussion for this project, email SQLSrvIntegrationSrv@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Oct 5, 2009 at 12:16 PM

Kevin

There was a bad record in the sharepoint list that we fixed. I’m no longer getting that general error, but now I’m getting a time out error after 1 minute. Is there a way to increase the timeout value. The last run I did, therer were a total of 212 records to be updated and it updated 124 before the timeout occurred. I’ve tried modifying the batch size to no avail.

Thanks

Jon

From: kevinidzi [mailto:notifications@codeplex.com]
Sent: Tuesday, September 29, 2009 12:46 AM
To: Wheeler, Jon
Subject: Re: Update SP List using Sharepoint Destination List [SQLSrvIntegrationSrv:69127]

From: kevinidzi

Which fields are the ones that are giving you issues specifically?

Unspecified errors are hard ones, they are super vague and I was able to find a bunch out through trial and error. Sometimes it can be a constraint on a column, or something that does not allow empties, or putting a number in a string field, stuff like that. If you can narrow it down to specific fields, then use the SSIS Dataflow visualizer to examine the exact data going into that field (click on the line connecting the dataflow components) and try adding that data to the list yourself and see if SP gives you some sort of error.

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLSrvIntegrationSrv@discussions.codeplex.com)

To start a new discussion for this project, email SQLSrvIntegrationSrv@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Coordinator
Oct 13, 2009 at 6:46 AM

Wow, you must have a very wide row to make it timeout at that small a size.. Did you try a worst case batch size of 1 and see if you still get that issue? 

Due to the way that it is an XML blob going over the wire, the batch size is the only way to really tweak that.  Perhaps the timeout could be increased, there has been requests to make that a bit more configurable. I am thinking of making some updates shortly to the component, i'll keep this in consideration if i do so.

Oct 13, 2009 at 9:51 AM

Kevin

Thanks for responses on this issue. I think I have discovered the problem. The list that I am trying to update has a connection back to an application database and is synchronized by a process supplied by the vendor. I created a duplicate list that was not set up to be synchronized and I was able to update 1900+ records in a few seconds.

The way the synchronization works is when sharepoint is updated it immediately tries to update the SQL database with the new information. Before it tries to update the database it has to check the application security to ensure the person doing the update has permissions in the application to update that data. I think the added processing of trying to update the application database and having to check the security is adding the additional time, and processing, so that the update is timing out.

I am going to check with the vendor about this and see if there’s anything that we can.

Again, thanks for your help on this matter.

Jon



This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


This email has been scanned and certified safe by SpammerTrap(r)



Feb 25, 2010 at 5:26 PM

I am currently getting the same error behavior in my SSIS to Sharepoint package.

[SharePoint List Destination] Error: Error on row ID="1": 0x1 - Unspecified error, such as too many items being updated at once (batch), or an invalid core field value. 

I have also recieved the same time out issue alot lately.

My metrics are as follows (600 rows x 266 Columns - The Update to Sharepoint takes forever!).

Besides bad field data what else can cause unspecified errors? In the mean time i will try to data quality the data going over.

Feb 26, 2010 at 9:22 AM

I’m still getting the Timeout issue. The only way around that, right now, is to run it manually until all of the updates have been processed successfully.

As far as the other error, in my case it was that a separate field had incorrect data in it. It wasn’t one of the fields that I was updating. As it turned out, it was an email address field that had a trailing blank at the end of it.

Jon

From: RWhittten [mailto:notifications@codeplex.com]
Sent: Thursday, February 25, 2010 3:54 PM
To: Wheeler, Jon
Subject: Re: Update SP List using Sharepoint Destination List [SQLSrvIntegrationSrv:69127]

From: RWhittten

I am currently getting the same error behavior in my SSIS to Sharepoint package.

[SharePoint List Destination] Error: Error on row ID="1": 0x1 - Unspecified error, such as too many items being updated at once (batch), or an invalid core field value.

I have also recieved the same time out issue alot lately.

My metrics are as follows (600 rows x 266 Columns - The Update to Sharepoint takes forever!).

Besides bad field data what else can cause unspecified errors? In the mean time i will try to data quality the data going over.

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLSrvIntegrationSrv@discussions.codeplex.com)

To start a new discussion for this project, email SQLSrvIntegrationSrv@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Coordinator
Mar 3, 2010 at 5:05 PM

If you happen to just let the adapters pick all of the columns when they are not needed, then it updating fields you did not intend may happen as it 'passes' through.

In the adapter properties, you can turn off columns (can't do it in the default views) so that the adapter completely ignores columns you do not care about.  It also reduces your impact if those fields are later dropped or changed.