Hi there,

I have a lot of DTS's that I have ineherited (and I am a bit new to this)
where data is being moved from one server to another overnight. The DTS's
are set up in the form where a DELETE FROM table script is ran, followed by
the transformation of the data e.g.

DELETE FROM Server2.Table1
On Success
Transform Server1.table1 to Server2.Table1

However we have some problems with Server1 which are intermittent, and what
I would like to do is stick some test before the DELETE FROM statement to
check that there are records in Server1.table1, before I proceed.

I was thinking of just a select SQL statement perhaps (on success carry on,
on failure quit with an email), but how would I close this afterwards? What
I am after is whether there is a standard test for a connection really.

Thanks in advance

Josh

Testing for a connection will not help as you want to know how many records
are in a table don't you ?
Why?
If there are no records then nothing will be transferred.

If the test for records in Server1.Table1 is only to see if you can query
the DB then

1. Do this in an ExecuteSQL statement. If it fails then you exit. If it
succeeds then you pump the records.
2. If the problems with Server1 are as intermittent as you say then there
is no guarantee that the server will respond to the pump even if it responds
to the request beforehand.

What are the intermittent errors? It will probably server best to get these
sorted first ?



--


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

Thanks Allen,

The intermittent errors I am refering to is a much bigger data load into
Server1, which sometimes works, and sometimes doesn't. This is controlled by
a different department in a different country, so I have to live with this.
However it is an overnight load, so my DTS runs around 8.00am, so if the big
load has worked I am ok, if not I do not want to delete all my records
unnecessarily.

I will test with a SQL count query and try to work out how I can get the DTS
to check for greater than 0 results to determine whether the rest of the DTS
should run.

Regards

Josh


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Ahh now I see why didn't you say <grin>?

OK.

Do they know their job failed ?
If they do then have them put a flag somewhere for you to pick up (DBtable,
text file etc).
You then read this and determine whether to go or not.

If they just let their job run and they won't tell you the outcome so you
have to play Sherlock Holmes yourself then if the way to check is to use a
SELECT COUNT(*) FROM TABLE then

Using an ExecuteSQL task assign the result of the statement to a Global
Variable. In an Active Script task following that read the variable's
value. Use workflow to route your package accordingly

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