I have column in flatfile contain value like. 2021-12-15T02:40:39 01:00
When I tried to Insert to table whose column datatype is datetime2.

It throwing Error as :

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

What could be best datatype for such values.

CodePudding user response：

It seems the problem is two-fold here. One, the destination column for your value should be a datetimeoffset(0) and two that SSIS doesn't support the format yyyy-MM-ddThh:mm:ss for a DT_DBTIMESTAMPOFFSET; the T causes it problems.

Therefore I suggest that you define the column, MyCol, in your Flat File Connection as a DT_STR. Them, in your data flow task, use a derived column transformation which replaces MyCol and uses the following expression to remove the T and with a space ( ):

(DT_DBTIMESTAMPOFFSET,0) (REPLACE(Mycol,"T"," "))

This will then cause the correct data type and value to be inserted into the database.