I would like to trap and route errors where the host name or database name on a SQL Server account is not valid and therefore an error is thrown when trying to use the account in a SQL Server Select snap. I have the pipeline with the Select in it set to use an error pipeline but do not want this account error to be handled by the error pipeline. I want to log it separately elsewhere. I set the Select snap to route error data to error view but this error is fatal so it doesn’t get routed. However, if it were set the Select snap to route to error pipeline it wouldn’t be fatal and would be handled by the error pipeline. Is there away to route the invalid account error on the Select snap? I realize I could create a separate child pipeline for the other snaps (Child Pipeline 1 and Child Pipeline 2) in the pipeline so they still have the normal error pipeline and set this pipeline to use a different pipeline to log separately but didn’t want to create a whole other level and pipeline for this.
It WOULD be nice if they had this functionality, but it seems many ETL tools don’t. One thing you can do is have a simple process start THIS process, using a pipeline execute, and trap the error THERE. That is what I am doing for a similar problem in snaplogic. Also, don’t forget to add exit to THIS pipeline as well as the one I am talking about, so an error triggers a failure. That way if even the last pipeline fails on the lowest level, the upper level will show an error, so you can handle it all the conventional ways.