Why can I insert a time '2010-12-01T22:17:18.610Z' into datetime2(3) directly in SQL Server, but, SSIS drops the time if I configure the input column as (DT_DBTIMESTAMP2 or DT_DBTIME2) or fails completely if I format the input column as a String (DT_STR)? 

CREATE TABLE [dbo].[timetester]([testtime] [datetime2](3) NULL) ON [PRIMARY]

With DT_DBTIME2, etc... it imports column and loses the time: 2010-12-01 00:00:00.000

With DT_STR, the SSIS package fails with "Invalid character value for cast specification."

I've tried every variation of column types that I can think of to no avail.  I've created a workaround package with a Derived Column that replaces the T & Z which inserts the datetime correctly.

REPLACE(REPLACE(lastModifiedTimestamp,"T"," "),"Z","")

However, I want to know what is going wrong inside of SSIS that can't process the T & Z when a direct SQL INSERT statement works just fine.  INSERT INTO [dbo].[timetester] VALUES('2010-12-01T22:17:18.610Z')

Thanks,

Otto

datetime2 does not store the time zone info you gave in the value but

datetimeoffset can support it but requires formatting

Because the datetime format '2010-12-01T22:17:18.610Z' doesn't supported in SSIS.