Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Incorrect concatenation in SSIS expression. What could be wrong? Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 8:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 30, 2013 7:28 AM
Points: 9, Visits: 48
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!
Post #1374110
Posted Thursday, October 18, 2012 12:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 460, Visits: 1,683
Cross post. See:
http://www.sqlservercentral.com/Forums/Topic1374114-364-1.aspx
Post #1374538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse