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

SSIS Expression doesn't work for concatenation. Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 9:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:27 PM
Points: 9, Visits: 49
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!
Post #1374117
Posted Thursday, October 18, 2012 12:23 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
Cross post. See:
http://www.sqlservercentral.com/Forums/Topic1374114-364-1.aspx
Post #1374537
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse