SSIS: Conditional Split

Auerelio Vasquez
Auerelio Vasquez used Ask the Experts™
on
I'm trying to do to conditions:

One, if the incoming table, has duplicate rows or if the value is null in a column, then i want the condition to send an email, if not, then continue with the data transformation.

1. Can i use a query in conditional Split  ? ,
2.  i know one case can be null, but i can't figure out how to express that in the correct syntax.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Auerelio VasquezETL Developer

Author

Commented:
Basically,

What i'm trying to Do.

if this condition is true or the value of the column (product) is null

then send email task.... Otherwise, move to the next task.

here is the query:

SELECT COUNT(product), product
FROM dbo.imp_ProductExclusionFlag
GROUP BY product
HAVING COUNT(PRODUCT) > 1
Since the approach I usually take is to bring everything into a staging table and then process it into the Production tables, I would simply use a set of 2 or 3 queries to accomplish this.  One (or, possibly 2) would pump the duplicates and NULL containing rows to the exceptions table.  I would then use another one to add the data to the Production table.  (Although, I would probably go ahead and code that last one as either a MERGE query or a set of 3, i.e. an UPDATE, an INSERT, and a DELETE)
Snr. Development Manager
Commented:
you can use condition like

where product is null or product in (select product from (select product, count(*) cnt from dbo.imp_productexclusionflag group by product having count(*)>1) x)
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Auerelio VasquezETL Developer

Author

Commented:
yes, well i was not able to use a conditional split, i just used the on success and created to additional tasks, and use the logic, if the row count was greater than 0, or if any row was null, to use one path, and if it wasn't true to move to another task.... i still would like to get more information on using conditional splits. thanks for your comments.
Auerelio VasquezETL Developer

Author

Commented:
i understand how the conditional split works, for each row, it works great, if you are checking a value row by row. However, the resutls of a query is what i'm trying to find out for my second condition.

i guess i could throw the results of a query into a variable, and use a count, if the count is greater than a certain number, i could use that to send the results another way......
If you wanted to do it with "brute force and awkwardness", you could include the row count on each row and then use the conditional split to redirect the data based upon that column (and probably omitting that column ;-).
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial