Another possibility would be to add both MySQL databases as linked
servers and just use a stored procedure to select from one and insert
into the other using openquery . Then schedule the procedure as a SQL
Server Agent job.
This has worked for me in the past.
Daniel Kasak wrote:
> Thomas Johnson wrote:
>> I have MS DTS package that pulls records from one MySQL server and
>> puts them into an identical table on another MySQL server. The Query
>> is like the following:
>> Select * from scheme.table where modify between @a and @b;
>> Modify is a TimeStamp field that has default and on Update set to
>> CurrentTimeStamp. We always pull records from the last pull (@a) to
>> the present (@b = Now()) which are set at the beginning of the DTS
>> package and stored in a local table on the source DB. We pull 6
>> tables in this package. Initially we have done all 6 in parallel but
>> we have rewritten them to be sequential. When we run them together,
>> 90% of the time, one DATA PUMP gets zero rows even when there is
>> data. The Data Pump does not report an error. When we go back into
>> the design and execute the Data Pump manually it works. Is there a
>> MySQL setting we should have set that may be causing this? We are
>> using ODBC 3.51.12 with MySQL 5.0.24. It seems to fail for the same
>> table consistently and then after few attempts will switch and start
>> to fail for a different table, but always work when we execute just
>> that ONE Data Pump manually through the designer.
> If it *always* works from the DTS designer, but sometimes fails when
> simply executed, then this sounds like a DTS problem to me. Try
> turning on ODBC logging and triggering a failed execution. Then see if
> you can get an ODBC log for a successful execution.
> Personally I've found DTS to be pretty flaky - especially when dealing
> with non-Microsoft products. Maybe you'd be better off doing the
> transfer some other way? You could always use mysqldump to dump your
> table from one server and then import it into another server. You
> could do a really simple script that does the mysqldump, then hits a
> web page on the destination server, which triggers the import.