Problem to import datetimeoffset from csv file to a table via SSIS

Question

I've a csv as a source file that I'm trying to load the data from this file to a stage SQL server table. There are three date columns. The below is an example of values of these columns:

2007-10-02T08:46:13+02:00

On SQL server side, I've defined datetimeoffset(7) as datatype and in SSIS connection manager selected database timestamp with timezone [DT_DBTIMESTAMPOFFSET] as datatype. But I'm getting the following error for importing datetime columns:

Data conversion failed. The data conversion for column "column1" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Flat File Source [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Flat File Source.Outputs[Flat File Source Output].Columns[column1]" failed because error code 0xC0209084 occurred, and the error row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[column1]" 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. Error: 0xC0202092 at Data Flow Task,

Flat File Source [2]: An error occurred while processing file "\path to file.csv" on data row 2. Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Do you how to fix it? Or why I'm getting this error?

Answer 1

In the Flat File connection manager, leave the column data type to string (DT_WSTR or DT_STR) and in the Data Flow Task add a Derived Column Transformation with the following expression:

(DT_DBTIMESTAMPOFFSET, 7)REPLACE([DateColumn],"T"," ")

Example:

Answer 2

try first to load it as string (length 25 or so) and then try to substring it using Converting Columns in ssis and then you can try and cast it to datatype of datetime in another Converting Columns before loading to your table.

Answer 3

I was having the same problem. When i tried to export data that already have date time stamp it produces date format like below. If you change your date format i hope it will work.
My date format -> 2022-09-14 11:00:00.0000000 +00:00
Your date format -> 2007-10-02T08:46:13+02:00