Incorrect concatenation in SSIS expression. What could be wrong?

• Learner093

  Old Hand

  Points: 359

  More actions

  October 17, 2012 at 8:00 pm

  #393412

  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

  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:24 pm

  #1550644

  Cross post. See:

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