copy data from mysql to ms sql

[darwin101]

Hi all

Is is possiable to copy data from Server A (MySql outside of firewall, internet) to Server B(SQL Server, inside firewall, intranet) using DTS? What I need to do is copy all of the data in tables from Server A to Server B then delete all data on Server A. This needs to happpen like every 5 minutes.

Any advice/pointers/references would be greatly appreciated.
thanks

 

[ptheriault]

If you can ping the server and create and ODBC connection then you should be able to get the data from it. The problem is going to be with your Network admins. They are going to have to open a port so the two servers can see each other. I don't think they are going to like that. It is going to pose a big risk to your network.

--- Paul
-- The Database server looks fine, it must be the Network!

 

[mrdenny]

Pinging the server isn't required. It also isn't required for the MySQL server to know about the MSSQL server.

Install the MySQL drivers on the MS SQL Server. If you can connect to the MySQL server from the MS SQL Server then you can setup your package. You'll want to setup connections to both servers, then draw transformations from the MySQL server to the MS SQL server. Then truncate the data on the remote side.

If the data is being entered into the MySQL server in real time you will want to setup some checking so that only data that was been transfered is deleted.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com