CODEWITHSUNDEEP
sqlsql-serverssissql-server-2019
Aryan
Aryan

Reputation: 57

Column Datatypes Issue in sql server 2019 when Import Flatfile using SSIS

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.

Upvotes: 0

Views: 133

Answers (1)

Thom A
Thom A

Reputation: 95949

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.

Upvotes: 1

Related Questions