DTS to File: Not Create File On Empty Result

DTS to File: Not Create File On Empty Result

Post by Toma » Wed, 05 Mar 2003 19:10:04



Hi,

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
like:

    -- 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!

 
 
 

DTS to File: Not Create File On Empty Result

Post by Allan Mitchel » Wed, 05 Mar 2003 20:58:54


Hello, Tomas!

OK  Using SQL Server 2000 keep with the ExecuteSQL task.  assign the result
of SELECT COUNT(*) from TABLE WHERE ParentID = 123 to a global variable
using the Parameters button.

Next in an AX Script task after this check the value of the GV.  If = 0 then
disable the task that does the pump and enable the exit gracefully.

Have a look at this

Multiple Paths in Workflow
http://www.sqldts.com/default.aspx?6,103,218,7,1

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: 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
: like:

:     -- 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.

      ---  Microsoft Outlook Express 6.00.2600.0000

 
 
 

1. DTS package files on empty text file

Hi,

I have a simple dts package with one Text connection as source, a SQL server
connection as destination and the transform data task exports from text file
to SQL server table. In normal circumstances the DTS package works fine.
If text file is empty (no data), the DTS package file complaining about
missing columns in text file. Is there any workaround this?

Platform: SQL 2000 SP2

Thanks

2. Atlanta Delphi Developers Group - Meeting

3. Creating text file (DTS or NOT)

4. changing SQL server name

5. Creating Text File (DTS or NOT)

6. Check MDX Calculated Members sintax

7. Importing a Text File via DTS and adding the File Name as a column via DTS

8. Why is there no option -U with pg_dump?

9. empty source file gives error when DTS executes

10. DTS 7 Error on Empty File

11. Empty Text Files with DTS Transforms

12. Empty source file to DTS package

13. dts error while loading empty fields from a text file