Hi,

I have a DTS package which I am using to upload around 10,000 rows
(using a DTS Transfer Objects Task). Our Internet connection only
provides upload speeds of around 10 kb/s so this process can take quite
some time. This is not the problem, however, the problem is that our
Intenet connection is also unstable and sometimes drops out completley.


When the connection drops the DTS package fails and when I double click
the dialog I see a 'Bulk Copy execution failed' error. If I check the
DTS logs I see:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error.
Check your network documentation.

Is there a way to avoid DTS from throwing an error at this point and
keep trying the connection until it is found again since the connection
usually only drops for a few seconds at a time? My boss keeps asking me
'Why doesnt DTS behave like FTP so that it is possible to continue the
transfer from the same point when the connection failed?'. I cannot
really answer that question myself and since I only have a modicum of
experience in DTS, so I turn to the experts! Can anyone help?

Thank you in advance,

Darren

P.S. I would love faster and more reliable Internet Connection but we
are operating in an underdeveloped country and we have already the
'best' available!

DTS will be working in a single batch. It has no way of knowing where in
10,000 rows it failed.

If you have FTP with restart, why not export to file, use FTP, then
re-load at the end. use FTP restart in the middle. I don't know of any
FTP clients with simple restart that can be automated however.

You could architect a solution that was more easily restartable within
DTS. Use a Data Pump task instead and set a small fetch and commit size.
use a SELECT statement for the extract, and ORDER BY something suitable.
You could then query the destination to detect the maximum row you last
retrieved, and use this in the source query to help select only rows you
have not retrieved. Run this in some form o loop, until all rows have
been returned.

I would also question, why download 10,000 rows. Are they all new every
time? If not implement some tracking to only download changed rows. If
you have a slow link the best thing to do will be to minimize the amount
of data, so restrict the rows and columns you transfer. Perhaps use a
checksum or timestamp to detect changed rows. You may need to transfer
the verification value, but that should be smaller than the row itself.

Some food for thought I hope.


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

Thank you for your ideas, Darren.

May I ask another question? Is there a tolerance when a DTS package
fails like this? For example, if the connection drops for 1 millisecond
would that cause it to fail? Is there a setting where I might be able
to control this tollerance?

Darren

Is the DTS running via scheduled job?

If so, in the advanced tab, you can increase the number of retry attempts to
3.. 4.. 5.. whatever you think. It wont start frm the same place, but at
least wont fail and die at first go.


<jensendarren (AT) hotmail (DOT) com> wrote

jensendarren (AT) hotmail (DOT) com wrote:
I have no idea, that sounds like a rather deep network issue. If the
connection is broken, however long that takes according to the network
and protocol etc, it is gone for good. There is nothing at the
application level that will do this.

You could look into the lower level network settings. At what point is a
packet delay classed as a non-delivery for example. Just thinking out
loud. I suspect you would need full control over all devices that make
up the link, routers, switches etc, which if this is not your own line
and hardware, it will be impractical.

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