Importing date audit fields from Salesforce into SQL Server

Question

I have exported LeadHistory data from Workbench into a local CSV. I am now trying to import that via SQL Server's import wizard, but am getting stuck on the CreatedDate field. The wizard suggests the source field is of type DT_DATE and wants to map it to SQL Server's DateTime, yet throws an error on import.

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Source - LeadHistory_csv.Outputs[Flat File Source Output].Columns[CreatedDate]" failed because error code 0xC0209084 occurred, and the error row disposition on "Source - LeadHistory_csv.Outputs[Flat File Source Output].Columns[CreatedDate]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

I have tried various other date options for the source (reference):

• DT_DBTIME2
• DT_DBTIMESTAMP
• DT_DBTIMESTAMP2
• DT_DBTIMESTAMPOFFSET

All of these map to SQL Server's datetime, which is what we've used in the past.

A sample source value looks like this: 2013-07-18T20:28:27.000Z

Any thoughts on how to get the import wizard to handle this? Do I need to pre-process the field and transform it?

Answer 1

The approach I ended up taking was to load the data as a string into a staging table and then use SQL Server's CONVERT function:

CONVERT(datetime, CreatedDate, 127)

Where 127 is the format code for ISO8601 with time zone Z. I'm sure a full SSIS package could have handled the conversion, but for a one-time data load that was worth the effort.

Reference: http://technet.microsoft.com/en-us/library/ms187928.aspx

Answer 2

I know SSIS supports ISO8601 (the Salesforce format) since 2008. DT_DBTIMESTAMP should be the one. Try removing the Z at the end though - not sure if SQL Server understands that.