InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 01:21:36 GMT20http://www.sqlservercentral.com/Forums/Topic1374110-148-1.aspxCross post. See:[url=http://www.sqlservercentral.com/Forums/Topic1374114-364-1.aspx]http://www.sqlservercentral.com/Forums/Topic1374114-364-1.aspx[/url]Thu, 18 Oct 2012 12:24:15 GMTkl25http://www.sqlservercentral.com/Forums/Topic1374110-148-1.aspxI 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 FileLoadNumberIf the BatchID value is of single digit it has to concatenate with BatchDateISO by appending 0 in front of BatchIDIf BatchID is 9, then the result should be 0920121017 for FileLoadNumber columnIf it is 19, then the result should be 1920121017 for FileLoadNumber columnThanks a lot!Wed, 17 Oct 2012 20:00:54 GMTLearner093