Having issue with .contains

Here is what I have $message.contains(‘successful’) ? 1 : 0 . This should give me a count of “1” if the Stored Procedure executing correctly. I then have a UNION, AGGREGATE and a ROUTER to count the SQL EXEC snaps, however it is only counting the first one and not the rest. At the end I have an email if $cnt == 10 then Success email, if $cnt < 10 then Failure email. Since the counts are not happening the emails are not generating. Any help is appreciated.

Please share your pipeline so it is easier to understand where the issue is.

Here is what I have, In the mapper(s) I have:

The Union then

The aggregate:


The router:


The email section:


Sorry, email is just the PASS or FAIL from the count. There is not two separate routers at the end.

what I would suggest is the capture the documents right before feeding to Aggregate snap, that should give you an idea what might be wrong. Please share the json doc (from Union output view) if you need more assistane.

You can either do validate to obtain it, in case there is any issues. You can do JSON formatter and File Writer to capture for debugging purposes.

The thing is I had it working where the .contains it was doing the count of “1”. Then the Aggregate snap would do the counts 1,1,1… Then the router would have the cnt == 10 it would send the Success Email, if the cnt < 10 It would send the Failure email. Right now from the SQL Execute Snap I am not getting a count when it runs. I was before and now I am not.

Should I only have Execute here:



You do want have Validate & Execute on any snap you want it to run during Validate
Screen Shot 2021-05-04 at 3.23.38 PM

Last thing this is the UNION at the Beginning, it is set up like an SSIS package. Fist two are before the Union so they start first and then the other 9 go after.

Now you have all of my pipeline and how it is running, any thing here that looks wrong?

If you have both the Group By field and the the Count going to the field cnt, you will just get one output.

?? Meaning take out the group by? From docs I read it was better to have the group by. The main issue is once the EXEC is done to then hit the Mapper where I have the $message.contains (‘Successful’) ? 1 : 0

Agree with Craig, you should take out the group by