SSIS Expression doesn't work for concatenation.

  • I have this SSIS expression and it is evaluated as below with the default values replacing the SSIS variables. This is the raw expression:

    "Select "+(DT_STR, 20, 1252)@[User::BatchID]+ " as BatchID, '" +@[User::BatchDateISO]+ "' as BatchDateISO, 'FH' as RecordType, 'AS400' as SourceSystem, CASE WHEN LEN("+(DT_STR, 10, 1252)@[User::FileLoadNumber]+") = CAST(1 as VARCHAR) THEN '0'+'"+ (DT_STR, 10, 1252) @[User::FileLoadNumber]+"'+'"+@[User::BatchDateISO]+"' ELSE '"+ (DT_STR, 10, 1252) @[User::FileLoadNumber]+"'+'"+@[User::BatchDateISO]+"' END as FileLoadNumber"

    It is evaluated as:

    Select 9 as BatchID, '20121017' as BatchDateISO, 'FH' as RecordType, 'AS400' as SourceSystem, CASE WHEN LEN(9) = CAST(1 as VARCHAR) THEN '0'+'9'+'20121017' ELSE '9'+'20121017' END as FileLoadNumber

    This is returning the result without prepending 0, which means always the ELSE part in CASE is chosen for FileLoadNumber column value.

    If the BatchID value is of single digit it has to concatenate with BatchDateISO by appending 0 in front of BatchID

    If BatchID is 9, then the result should be 0920121017 for FileLoadNumber column

    If it is 19, then the result should be 1920121017 for FileLoadNumber column

    Thanks a lot!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply