November 13, 2011 at 3:52 am
Hi All,
I am building an SSIS package where I import a text file and want to apply some conditions to it using a Derived Column transformation.
The flat-file source has DataType of string[DT_STR] for all columns
Data Extract:
DateAmount
18/07/2011 4.5000
30/11/2011 0.0000
18/08/2011 4.5000
In the Derived Column transformation I am trying to look for Amount values which are not zero and change the 'Date' column to the 10th of that month.
My question is:
1. Should I be converting the 'Amount' column to a 'Numeric' datatype?
2. Is there a 'SQL LIKE' type expression which can be used?
I have managed to do this with a string look-up in the expression, but I want to be certain that any changes to the number format should not stop the package from running. For example if the 'Amount' column default changes to "0.00" the package should not crash.
Amount!= "0.0000"? "10" + RIGHT(Date,8) : Date
Thank you for looking
November 14, 2011 at 12:59 am
There is no LIKE operator in SSIS.
Maybe change the expression to:
SUBSTRING(Amount,1,3) != "0.0" ? "10" + RIGHT(Date,8) : Date
Notice that there is (currently) no LEFT function, so we need to use SUBSTRING to get the first 3 characters.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 15, 2011 at 8:12 am
Hi Koen,
Many thanks I will give that a go. Would thae expression capture all "0.0" or "0.00*" amounts?
Appreciate your help on this.
Regards,
November 16, 2011 at 12:13 am
amitabhbose2 (11/15/2011)
Hi Koen,Many thanks I will give that a go. Would thae expression capture all "0.0" or "0.00*" amounts?
Appreciate your help on this.
No problem, glad to help.
It will capture all 0.0* amounts.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply