October 17, 2012 at 8:00 pm
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!
October 18, 2012 at 12:24 pm
Cross post. See:
http://www.sqlservercentral.com/Forums/Topic1374114-364-1.aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy