Conditional Split Expression not Evaluating string Variable

Question

Post reply

Conditional Split Expression not Evaluating string Variable

Sid Childers

Ten Centuries

Points: 1079
More actions
November 10, 2009 at 9:31 am

#156393

Good Morning All,
I have a SSIS package that pulls data from our charge detail table. I need to exclude records that have specific department numbers. I attempted to do this by first creating a string Variable, named strDept, and setting it to my list of departments = 7012,7187,7643,7741,7742,7743. Then, my conditional split expression is written as HNAECD (database field name of type WSTR) != @[User::strDept]. The expression editor liked it, but the package returns no records when run. I also tried defining strDept1 through strDept6 setting each variable to one of the department numbers above and used HNAECD!=@[User::strDept1]||HNAECD!=@[User::strDept2] etc. This also did not work.
Am I not using the conditional split correctly? Should a different transformation be used? If conditional split is the correct one, what do I need to do to have the package exclude charge detail records with the department numbers specified in the variable, strDept?
Thanks, in advance, for your help and guidance.
Sid

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kbatta SSCrazy Points: 2274 More actions · Answer 1

In HNAECD!=@[User::strDept1]||HNAECD!=@[User::strDept2] etc.

switch from logical or (||) to logical and (&&).

HNAECD is not in (strDept1 OR strDept2 OR...)

is the same as

HNAECD is not in strDept1

AND HNAECD is not in strDept2

AND ...

Would it be possible to store the departments to be skipped in a table? Then you could either join to it when extracting the data or do a lookup. This way if the list changes, only the table needs to be updated.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 2

If you have more than just a few exclusion conditions, you might use a lookup transform instead of a conditional split. For your lookup query, use something similar to the following in your query:

SELECT '7012' [DepartmentNumber]

UNION ALL

SELECT '7187'

UNION ALL

SELECT '7643'

...

Configure your lookup to send unmatched rows to the error output (in SSIS 2005), or to the unmatched row output (in 2008). You can use that alternative output to simply discard the excluded rows or send them to a file or table for review.

This is usually a much cleaner way to do this, especially if you've got a lot of exclusion conditions.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jay Bowers SSC Veteran Points: 227 More actions · Answer 3

In the conditional split, each dept number would be its own case ...

Case 1 Dept == '7654'

Case 2 Dept == '7655'

etc.

Then, point the default conditional split output (the one where it doesn't match any of the conditions/cases) to the next data flow object.