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?
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.
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/
