Setting Transformation Error Variable from when SQL Lookup fails was created by bruce.gibbins
I was wondering if you would consider an enhancement that would allow us to set the <Transformation Error> After Execution variable to a specific value if a SQL Lookup action is aborted on no value found in a transformation.
I have done a screenshot to explain but in our case we need to do an SQL lookup to see if some data has arrived first before doing the rest of the transformation and parent package. In this case I need a value from the inbound file in the lookup. Hence the transformation is triggered, I then pull a value from the inbound file (a date) and use it in the lookup to see if some data from another source has been already received. If that data is not there yet then it is ok for this whole transformation and parent package to end gracefully and we will try again later as it is in a scheduled job.
So I need some way of recording the fact that the lookup has 'failed' but it isn't critical and can be aborted but then store some type of value in either the After Execution <Transformation Error> variable or a user defined package Variable that we can interrogate to then determine if we can just end the parent package successfully or not.