[SalesForce] Importing date audit fields from Salesforce into SQL Server

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?

Best Answer

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

Related Topic