bamapie
asked on
SSIS Conditional Split
I have a Conditional Split in my SSIS job that hits on 10 different conditions. It sends the default output to Table A.
I'd like to send every OTHER conditions to Table B. How do you send multiple conditions to a single table?
I could combine all 10 conditions into one big hairy test, but it would be very untidy.
How can I do this?
Thanks
I'd like to send every OTHER conditions to Table B. How do you send multiple conditions to a single table?
I could combine all 10 conditions into one big hairy test, but it would be very untidy.
How can I do this?
Thanks
you can set one for your first condition then all the others to "Conditional Split Default Output "
ASKER
If I understand your response, the issue is that "other" isn't a condition that can be described.
It's kind of like, imagine I'm processing street addresses for the U.S. I don't want anything with the street name "Oak", "Maple", "Main", or "Broadway", and I don't want anything from Paducah, KY; and I don't want anything from a state name that that ends in "a".
So I have conditions for each of these situations, and the rest of it (default) I keep.
Now I've decided that, I'd like all those conditions above, I actually do want to keep--in a single, different table. So I now want to route all of those "other" conditions to a single table.
(My example above is of course a simplification; there are 10 or so rules like that.)
But again, I'm not 100% sure I understand your suggestion--but I think you're saying, make one positive (keepers) test and route all the other to the new table. But that one (very long) rule gets me back to the same problem--it's just really untidy and inelegant.
It's kind of like, imagine I'm processing street addresses for the U.S. I don't want anything with the street name "Oak", "Maple", "Main", or "Broadway", and I don't want anything from Paducah, KY; and I don't want anything from a state name that that ends in "a".
So I have conditions for each of these situations, and the rest of it (default) I keep.
Now I've decided that, I'd like all those conditions above, I actually do want to keep--in a single, different table. So I now want to route all of those "other" conditions to a single table.
(My example above is of course a simplification; there are 10 or so rules like that.)
But again, I'm not 100% sure I understand your suggestion--but I think you're saying, make one positive (keepers) test and route all the other to the new table. But that one (very long) rule gets me back to the same problem--it's just really untidy and inelegant.
Hmm I see what you mean now, I don't think it is possible to do that way (but not sure), I would instead change the master query or view or SP to "clean"or "standardize" the result set to include a "TAG" field with A or B.
Something like before the split creating a Derived Column with
TAG=(cond1=true)=1+(cond2= true)=1+co nd3...
then check for TAG value and send to appropiate table, at the end this will kind of give you what you were trying to avoid but IMO much "TIDY" in the split cond. part
I'll keep brain open if some solution including only SSIS snaps in and will let you know.
Something like before the split creating a Derived Column with
TAG=(cond1=true)=1+(cond2=
then check for TAG value and send to appropiate table, at the end this will kind of give you what you were trying to avoid but IMO much "TIDY" in the split cond. part
I'll keep brain open if some solution including only SSIS snaps in and will let you know.
You may see this link: http://oakdome.com/programming/SSIS_ConditionalSplit_Multicast.php.
Not sure in your case this will help or not but hope this work for you.
Not sure in your case this will help or not but hope this work for you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi bamapie,
Well there multiple options are there-(You can choose based on your need.)
1. You can write a condition in such a way that all the data will go in 1 destination. I think you can use OR in that.
2. You can use a merge statement, in this you will have to sort the data first.
3. You can use a union all (already given by Nakul)
It should look like below in case of union all.
Hope it helps!
Well there multiple options are there-(You can choose based on your need.)
1. You can write a condition in such a way that all the data will go in 1 destination. I think you can use OR in that.
2. You can use a merge statement, in this you will have to sort the data first.
3. You can use a union all (already given by Nakul)
It should look like below in case of union all.
Hope it helps!
ASKER
Perfect. Had never used that before. Thank you!