Cannot update Sharepoint field to NULL from Non-NULL value?

Jun 6, 2012 at 11:35 PM

Hello,  I'm using the SharePoint List Destination control trying to update a Sharepoint list.  I have a SSIS Data Viewer set up prior to the SharePoint Destination control running and I can see many rows that "should" be getting updated.  These are string values that I'm trying to update from a Non-NULL value like "PSC" and I want to set them equal to NULL.  It appears (when running the package in Visual Studio) that the SharePoint List Destination control runs successfully (turns green) and I can see from the Data Viewer that many rows should be getting passed to Sharepoint, but when I refresh the Sharepoint site, I'm not seeing the Non-NULL values changing to NULL.  When I insert totally "new" rows into the Sharepoint list, the values that are NULL show up properly in Sharepoint as NULL.  The problem I'm seeing seems to be related to updates, specifically when going from a Non-NULL value to NULL.  Has anyone experienced the same thing?  If so, is there a work around?  Thanks!

Apr 2, 2014 at 8:15 AM
Any updates on this specific issue, I'm using the latest sharepoint list adapter version and it seems this still isn't possible ?
Nov 13, 2014 at 9:40 AM
Hi matthewwalk / dlangenb

for text fields I've gotten around this limitation by introducing an ISNULL in the SQL Source query.
This replaces the NULL value with an empty string, which is actually data which is updated to the SharePoint list.

ISNULL([FieldName], '') AS [FieldName]

But now I'm running into the same problem with FLOAT and DATETIME fields.
Have tried to convert them to VARCHAR, because then then the solution above would also apply, but as the columns in SharePoint are 'Number' and 'Date/Time' SharePoint doesn't accept it when I supply the values for those columns in a text-format.

So I have to find a solution for this, but at least for text-fields there's a solution.
Jan 14, 2015 at 10:44 AM
Edited Jan 14, 2015 at 10:50 AM
All,

has been a while before I was able to get back on this.
While the 'solution' for the TEXT/VARCHAR field was already posted, I indicated that there was no solution for Numeric (FLOAT) or Date (DATETIME) fields yet.

well, did some testing...

For FLOAT the solution is easy (if you business accepts this): While I was unable to have an empty string displayed in SP, we've managed this by replacing the NULL value with the '0' (zero) value. (because it is an 'amount' '0' (zero) amount fits the need for our business)
So, on your SQL-source-query you would have something like this:

ISNULL([FieldName], 0) AS [FieldName]


Then the DATETIME field, this was the hardest part to solve, specifically because it needs to be addressed on the SQL side as well as on the SP side.
It works like this: on the SQL side, we've replaced the NULL value with date: '1-1-1900', then on the SP side we've created a 'calculated field' with the following code:
=IF(YEAR([DATE-INPUT])>1900;[DATE-INPUT];"")
So, this checks to see if there's a '1900' date and if it has one, it outputs an empty string, if it has none, it just outputs the Date.
On SQL Side: ISNULL([FieldName], '1-1-1900') AS [FieldName]

So, hopefully, there are other people who may benefit from this solution.

Cheers!

Raas
Feb 26, 2015 at 5:43 AM
Raas,
Does your solution for datefields require two column in sharepoint -- column A that SSIS writes the 1/1/1900 value to, and then column B that is the calculated column? Or can the formula in column A reference its own value? I was hoping to not have to create a second field, but I've tried pasting your formula exactly as written and also replacing [DATE-INPUT] with my field name. Both ways I get a message that the formula contains a syntax error is not supported. So I'm assuming it requires two columns. In that case, I'm out of luck. SP 2010 has a limit of 48 date columns and we already have 47.... ;^\
Feb 26, 2015 at 5:45 AM
Out of luck because I have about 10 fields I need to implement the solution for...
Mar 8, 2015 at 9:23 AM
Hi Roxy,

unfortunately yes, this solution requires 2 fields:
  • the original [DATE-INPUT] field to which SSIS posts data, which is actually hidden from the viewers
  • the calculated one [CALCULATED-DATE] which is displayed to the viewers.
I didn't know about the 48 date column limitation. I don't have that many.