October 30, 2015 at 11:07 am
Hey guys I have the following expression below that I have broken down to make it easier to read with the conditional logic. My issue is, for some reason, that when the value of tokenCount renders a 5 that means that there will not be a 5th comma. Therefore I'm using an expression when tokencount = 5 to not call for the findstring of the 5th comma....but I am when TOKENCOUNT = 6. For some reason ssis is acting like it "HAS" to be able to calculate all expressions regardless of the conditional logic. Below is what I have. The first line is what I have the ssis variable set to. If someone can help me around this I'd deeply appreciate it. I will be using this in a derived column task and replacing the ssis variable name with a column name but since the Derived column tasks has no way of testing I just test with sample values in ssis variable expression builder.
@[User::Variable] = "1221 MAIN ST, 216, HOLYOKE, MA, 1040"
TOKENCOUNT(@[User::Variable],",") == 5 ? SUBSTRING(@[User::Variable],FINDSTRING(@[User::Variable],",",3) + 1,(FINDSTRING(@[User::Variable],",",4) - (FINDSTRING(@[User::Variable],",",3) + 1)))
:
[highlight=#ffff11](This causes it to fail cause there is no FINDSTRING(@[User::Variable],",",5) but it shouldn't matter this shouldn't be run when TokenCount = 5)[/highlight]
TOKENCOUNT(@[User::Variable],",") == 6 ? SUBSTRING(@[User::Variable],FINDSTRING(@[User::Variable],",",4) + 1,(FINDSTRING(@[User::Variable],",",5) - (FINDSTRING(@[User::Variable],",",4) + 1)))
:
SUBSTRING(@[User::Variable], FINDSTRING(@[User::Variable],",",2) + 1,(FINDSTRING(@[User::Variable],",",3) - (FINDSTRING(@[User::Variable],",",2) + 1)))
October 30, 2015 at 12:02 pm
I figured out a way around it. The reason I was getting an error was, apparently, even if one of the expressions weren't supposed to be used when there wasn't 5 commas in the string it still had to complile/validate. This caused a negative value in my math for the length of one of my substring functions. To get around this I added ABS() around all math calculations so when there wasn't a 5th comma it would still come up as a positive number....Its ok cause when that value is negative it wont be used.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply