Not able to convert string to DateTime using expression in SSIS

3

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.

sql-server
datetime
ssis
expression
etl
asked on Stack Overflow Aug 2, 2019 by Jay Desai • edited Aug 4, 2019 by Hadi

1 Answer

1

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 )
answered on Stack Overflow Aug 3, 2019 by Hadi

User contributions licensed under CC BY-SA 3.0