Not able to convert string to DateTime using expression in SSIS

3
votes

I have 2 variables in SSIS package as CurrFile of string and File_Dt of DateTime datatype. I want to extract DateTime from CurrFile and store it into File_Dt variable. For this I am using expression as below:

(DT_DBDATE) SUBSTRING( @[User::CurrFile], 21,14 )

After doing SUBSTRING the value is 20190725001614. Now, I want to store this value as DateTime in File_Dt variable. I am getting this error while doing so.

Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DBDATE.

1
sql-serverdatetimessisexpressionetl
May be duplicate of this? stackoverflow.com/questions/32485422/… Casting a string to a DT_DBDATETIME must use a specific string format. – FembotDBA

1 Answers

1
votes

You have to make sure that the string contains a data time value with the following format: yyyy-MM-dd HH:mm:ss. Also use DT_DATE or DT_DBTIMESTAMP instead of DT_DBDATE Try the following expression:

(DT_DBTIMESTAMP) SUBSTRING( @[User::CurrFile], 21,4 ) + "-" +
SUBSTRING( @[User::CurrFile], 25,2 ) + "-" + 
SUBSTRING( @[User::CurrFile], 27,2 ) + " " +
SUBSTRING( @[User::CurrFile], 29,2 ) + ":" + 
SUBSTRING( @[User::CurrFile], 31,2 ) + ":" + 
SUBSTRING( @[User::CurrFile], 33,2 )