Equivalent Ssis Expression

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.FI

Solution 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.

Solution 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/