sql server dts: You would use workflow to gracefully halt a package. Instead of checking the
file as in the example below, use an Exec SQL Task to query the table and
return the result to a global variable, using the Parameters support in the
task. Then test the global variable.

How can I check if a file exists?
(http://www.sqldts.com/default.aspx?211)


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

[quoted text, click to view]

The idea is to have the first step to check a table for a value. If there
is a value then it will proceed to the next step, if there is no value found
the package will not continue. As I am not a programmer by nature, could
someone assist with some ideas or sample. I would think it would sample but
it is not clicking for me what to return to cause it to halt.

I download your dts package about article: How to export all tables in a
database from http://www.sqldts.com/default.aspx?299

Here are the reproduce steps:

1. rename DTSBulkExport.msi.2 to DTSBulkExport.msi and install the
custom task DTSBulkExport.msi
2. open ExportTestMS.dts from SQL Enterprise Mgr -> DTS -> open package
3. modify the conn info
4. modify the properties of the BulkExportTask to reflect local SQL
server/login
5. create a working path c:\temp\test
6. enable IIS->SMTP service if it is not already enabled
7. run the package, three text files "pmm_customers*" should be created
under c:\temp\test plus another file "pmm_complete"
8. modify one text file e.g. pmm_customer.txt to read only
9. rerun the package, one task should fail (because pmm_customer.txt is
not writable). An email notice is expected for this task failure, and this
time the file "pmm_complete" should not be created.

If I modify one text file e.g. pmm_customer.txt to read only, I'm having
trouble with last step because I can't catch this failed task correctly.
DTS is able to catch the failed task. My biggest concern is how to catch the
failed task inside the script code.

Thank you very much!