MSsql to MySql - cronjobing

[davidcitron]

Hello all,

i have got an issue with a client here, who is running an internal CRM system.

we need to get information into our Webserver (Linux-MySQL5) database (dealer lists, product lists, ....) and this information is stored in the CRM.

but the CRM is Windows-MSsql. The output is .DAT files.

I understand extracting a .DAT file on my MySql server is not an option.

Would it be "easy" to output in .csv rather than .dat ? What would be your advice here ?

Please do not hesitate to contact me if you have professional advice/proposition.

All the best, david

[omrsafetyo]

Yes, export the data as csv - you can do this easily with DTS; or you can do with BCP from the command line:

C:\> BCP database.dbo.table out mytable.csv -S<servername> -Uusername -Ppassword

DTS may better serve you.

Then upload it to your web server and load into mysql as follows:

LOAD DATA LOCAL INFILE '/mytable.csv' 
INTO TABLE myTable 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n';

This could rather easily be set up as an automated process if you are familiar with MS SQL jobs, VBScript, Shell Scripting and SQL; if you wanted to set this up as a daily routine or etc.

Step 1 - define an SQL job that exports the data to CSV everyday at 10PM
Step 2 - Create a VBScript that grabs the CSV output, and RCPs it to the web server
Step 3 - Have a background shell script that checks the directory where the file gets RCP'd to - when the file is created, have it run the MYSQL commands to import the data into the MYSQL database.
Step 4 - Archive the csv to a backup folder

Voila - a daily routine that updates your webserver from the CRM.

Depending on how many tables you have, this would be basically easy - especially since if you set it up right - e.g. use the table name as the output filename; you could make the scripts "smart" enough to parse out the table name, and you would only need one script for all tables it would run the import into a table based on the filename.

[pclfw]

You might like to have a look at MSSQLs MAKEWEBTASK function. Using this you can create the whole MySQL insert code for each table. It could save you the bother of having to reprocess the data from CSV tables.