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 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