Hi, I have two sites and I need to transfer data from one site to the other on a daily basis. The table structure are exactly the same except one is with mysql and the other is sqlserver. I need to transfer data from the mysql site to sqlserver site. What is the best way to do this? I'm planning to dump everything in xml and write a stored procedure on the sqlserver to load and parse the data. Is this is a good way of doing it? I read about the xml bulk load with sql server, will that work if I need to do some logic check before inserting?

avmood:

The table structure are exactly the same except one is with mysql and the other is sqlserver. I need to transfer data from the mysql site to sqlserver site. What is the best way to do this? I'm planning to dump everything in xml and write a stored procedure on the sqlserver to load and parse the data. Is this is a good way of doing it?

I would not do that.

SQL Server has a DTS/SSIS import wizard that can get data from various sources and import them into the SQL Server database/tables.

Because the table structures are the same as you said, then this is a straight forward import.

If you are using SQL Server 2000 then you would use the DTS import export wizard, if you are using SQL Server 2005 you would use the SSIS wizard. What you would do is set up a source destination which would be your MySQL database. SQL Server has connections to a MySQL database so now worries there. Then you set up the destination database which is your SQL Server. You would then set up which tables you want to export from MySQL and import into SQL Server. You can then define a schedule for how often it runs like 2am in the morning when activity should be at its lowest.

Will the ssis work if I need to do some comparison of the data between the two tables before inserting?

avmood:

Will the ssis work if I need to do some comparison of the data between the two tables before inserting?

What type of comparison/logic do you need?

The SSIS wizard supports basic transformation if need be. If you are after more complex imports like adding rules etc you can edit the SSIS package afterwards and add custom logic to it, much like the old SQL Server 2000 DTS packages.

I just need very basic logic. Base on the data in sql server 2005, I will either insert or update the data from mysql.

avmood:

I just need very basic logic. Base on the data in sql server 2005, I will either insert or update the data from mysql.

I have never done such logic within a DTS/SSIS package before so I cannot say, but i am sure it is possible as the package supports executing stored procedures.

From reading your first post, I thought it was a synchronisation task that you are after that is why I initially recommended DTS/SSIS.

Yah, its a task that I have to do everyday. I need to transfer data on a daily basis. But some data needs to be inserted, some needs to be updated to the sql server.

avmood:

Yah, its a task that I have to do everyday. I need to transfer data on a daily basis. But some data needs to be inserted, some needs to be updated to the sql server.

So both tables are not the same?

My assumption was it was and that the requirment was both tables have the SAME data.

Both table has the same table struture and data. But sometimes instead of inserting, I need to update the tables.

avmood:

Both table has the same table struture and data. But sometimes instead of inserting, I need to update the tables.

If both table has same data, then the destination table in SQL Server does not need to know whether it was an update or insert, because you can simply just drop the table and recreate it based on the MySQL one. 

Yes. but there are a lot data in the table. I cannot drop them and recreate them on a daily basis. And also, there other parts of the sql server site that is using the table, so I cannot just drop and recreate them.