SSIS Expression doesn't work for concatenation.

• Learner093

  Old Hand

  Points: 359

  More actions

  October 17, 2012 at 9:11 pm

  #263433

  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!

• kl25

  Hall of Fame

  Points: 3752

  More actions

  October 18, 2012 at 12:23 pm

  #1550643

  Cross post. See:

  http://www.sqlservercentral.com/Forums/Topic1374114-364-1.aspx