Equivalent SSIS expression

Question

I am unable to convert the following SQL to a corresponding SSIS expression and cast it to DATE format

 SUBSTRING(A.FILENAME,13,2)+'-'+SUBSTRING(A.FILENAME,15,2)+'-'+SUBSTRING(A.FILENAME,17,4)

This is the best I could get

DT_DATE(SUBSTRING(@[User::V_LoadFileName],13,2)+'-'+SUBSTRING(@[User::V_LoadFileName],15,2)+''+SUBSTRING(@[User::V_LoadFileName],17,4))

Any suggestions?

Answer 1

String to cast to Date in SSIS has to be YYYY-MM-DD.
If you want to cast String to Datetime, it should be YYYY-MM-DD HH:MIS:SS.

Answer 2

The reasoning for the formatted data in Ferdipux's answer is that your environment may not be using the same style of DATE (i.e. YYYY/MM/DD vs YYYY/DD/MM).

Also, your example has syntax problems. Casting in SSIS is a little odd. You close your parenthesis BEFORE the variable.

(DT_BOOL)"0" returns FALSE

Note also the use of TWO quotations in SSIS. So you will likely need to use '"' in your script to work.

Some examples of casting in SSIS:

( «type» ) «expression» (DT_I4) (DT_STR, «length», «codepage» ) (DT_DATE) (DT_BOOL) (DT_WSTR, «length» ) (DT_NUMERIC, «precision», «scale» ) (DT_DECIMAL, «scale» ) (DT_DBTIMESTAMP)

Lastly, if you can, use the Expression Tester. Made life REALLY easy for me in designing my own SSIS packages. :D

http://expressioneditor.codeplex.com/