I have a child pipeline which depends on successful execution of other parent pipeline. In parent pipeline, I am updating some MySQL tables from external source. Then in child pipeline, this updated MySQL tables are used in custom SQL query. I need to ensure that before running this custom query in child pipeline, parent pipeline has properly updated the tables in MySQL DB and it didn’t failed hence before running child pipeline I need to know if the parent pipeline was executed successfully or not. How do I achieve this?
You could rely on the default error behavior for snaps and the Pipeline Execute snap to achieve this.
Take that pipeline for example. In order for the Pipeline Execute snap to run, both the MySQL update snaps must succeed. Otherwise, the Pipeline will stop executing.
@tlikarish thanks for reply. Actually both my Parent and Child pipeline completely independent. In your example you are doing MySQL update and calling the Pipeline Execute Snap to call the child pipeline. But in my case in Parent pipeline, I am doing mapping and using diff snap to update and insert the MySQL table.
Also pipeline used in Pipeline Execute snap must have some kind of input open which in my case there is no input in child pipeline.
Basically think like we have multiple pipelines and we need to chain them together so that successful run of one pipeline triggers execution of next pipeline in the chain. All these pipelines in chain do not have open input or output which is required by the Pipeline Execute snap. If any of the pipeline fails in the chain, then execution stops at that pipeline and further pipelines in chain are not executed until chain is restarted from the beginning. Is it possible to do in SnapLogic?
Yes, this is possible. It might be non-obvious, but the Pipeline Execute snap can invoke a child pipeline that does not have any unlinked views. When it does this, then it will execute the pipeline and wait for its completion before proceeding. The output of the Pipeline Execute when there is no unlinked output view is the status of the child pipeline’s execution. So it is possible to use Pipeline Execute to do some simple orchestration like you’re describing.
@tlikarish Thank you for the suggestion. Would it be possible for you provide one example which will help me understand better what you are suggesting?
Why don’t you try using conditional or router snap to check if the execution was successful? You can check on some parameter basically, which comes as (success)output from your first operation and then you can use pipeline execute to invoke your child pipeline.
Here’s an example project of what I was thinking of. In this project, there is a parent pipeline with 3 Pipeline Execute snaps. Each Pipeline Execute will execute child pipelines: step 1, step 2, and step 3. The parent will execute step 1, but step 2 is designed to fail. Notice that since it failed, the Pipeline Execute called step 3 is not executed. Is this a helpful example of what I had proposed?
projects_exe-on-success.zip (3.5 KB)
@tlikarish Thank you for the example! It is working as expected. The only thing I need to make sure is that there are not open input or output in the child pipelines
@tlikarish for this to work, we need to have no open inputs or output in pipeline. But there are snaps which do not have optional input/ouput. In such case do you know of the dummy snap that I can use to close of the open output of the snap in a pipeline?
Currently, I am using MySQL Execute snap with SQL statement as SELECT 1; but this is not efficient as this still makes connection to a database hence I am looking to replace this with some dummy snap to close of the output.
Some snaps you’re able to remove the output view, but not all of them. If you’re not able to remove the output view, then one way to work around that would be to use the Exit snap.
If the upstream snaps have no expected output, then you could use an Exit snap with the threshold set to 0.
If there is output and you’d like to ignore it, then you could use a Head snap in front of the Exit to limit the output to 1 and set the threshold to 1 on the Exit. Something like this: