I've got a DTS transformation with source data in a SQL Server table and
destination data source being a file. I must use this type of transformation
here, so no other options are applicable to this situation. What happens is
the source of the transformation is a query similar to this example, with
the parameter "123" being the variable:
SEELCT Name, Value FROM Table WHERE ParentID=123
The issue that I'm having here is that if the query above returns no
results, the destination file is still created as an empty file. This is a
problem because I process this file and then move it to a share from where
my clients can get it. I don't want them to get empty files. To deal with
the fact that the DTS transformation destination is created no matter what,
I thought I'd check before the transformation to see that the prepared
statement returns a value.
I added an "Execute SQL Task" that executes a stored procedure containing
logic that is meant to act as a conditional statement. Basically, on the
Failure-event, I allow the DTS package to exit. And on Success, I let it
continue by executing the transformation of data to destination file. This
is what the "conditional" run to decide whether to transform or not looks
-- Count the number of rows returned by the query
-- If there are no rows, raise an error
RAISERROR('', 16, 4)
As you can see, I could only get it the package to fail by calling
RAISERROR. This is not good because it'll log this error in the EventLog,
and it happens in the majority of cases. I only want serious errors to be
written to the log, and this isn't exactly an error. My question is, would
there be a better way for me to handle this situation? The behavior
triggered by RAISERROR is what I want: the Failure-event to be fired and
allow the package to avoid executing the transformation. I would, however,
like to "suppress" the RAISERROR from being written to the log and be able
to handle that in the application. I'm of course open to alternative
approaches to solve this. DTS is very new to me.
Was it clear what I wanted to do and why my current solution isn't working?
Open to any suggestions. Thanks!