Date AND Time column not getting Time value from Sharepoint Destination

May 13, 2009 at 7:37 PM

I LUV the new Sharepoint Source and Destination transforms for SSIS, but I'm having difficulty getting the time portion of a Date and Time column in Sharepoint to have any value other than 12:00.  My SSIS package has two columns one using the system variable 'StartTime', and a derived column that is a GETDATE().  These are mapping to StartTime and EndTime columns in Sharepoint that are defined as Date and Time columns, and the column mapping in SSIS shows both the source and target columns as DT_DBTIMESTAMP.  My SSIS LocaleID is English (United States) and the Sharepoint Culture is en-US, and the Sharepoint site locale is English (United States).  I can insert items successfully, but the time portion of my StartTime and EndTime columns is always 12:00, even though a Data Viewer just prior to the Sharepoint Destination transform shows valid time values such as 03:27.

Any ideas what I'm doing incorrectly?

Thanks!

Jun 16, 2011 at 3:28 PM

I also have this issue going up against SP2010.  Basically, the processing of a DB_TIMESTAMP is not done properly in the component. My work around is to convert the DB_TIMESTAMP variable into a DT_WSTR variable formatted as "yyyy-mm-dd hh:mm:dd" which the component handles fine.  Here is an example expression converting variable MyTimestamp to a DT_WSTR (you could also do it in a script component):

  • (DT_WSTR,4)YEAR(MyTimestamp) + "-" + SUBSTRING("0" + (DT_WSTR,2)MONTH(MyTimestamp),1,2) + "-" + SUBSTRING("0" + (DT_WSTR,2)DAY(MyTimestamp),1,2) + " " + SUBSTRING("0" + (DT_WSTR,2)DATEPART("Hour",MyTimestamp),1,2) + ":" + SUBSTRING("0" + (DT_WSTR,2)DATEPART("Minute",MyTimestamp),1,2) + ":" + SUBSTRING("0" + (DT_WSTR,2)DATEPART("Second",MyTimestamp),1,2)

Good luck.

-Robert

Coordinator
Aug 3, 2011 at 6:18 AM

True, that was I believe fixed in the beta version released last year; I'm finally prepping a new non-beta release this evening.