I seem to have lost my post “Ups and Downs” in my data migration. Oops. Oh well, I’ll try to re-iterate some of those thoughts here.

I’ve finally, after much cursing and swearing, managed to migrate the posts and comments from MSSQL to MySQL. My first attempt was through MSSQL’s DTS.

DTS to MySQL has some known bugs when dealing with fields that are BLOBs. If you’re attempting to move data using the CopyColumn method in DTS, it will fail even if you cast your column to another type. For example;

SELECT CAST) as body).

My next step was to try DTSing the file to a plain text file and then using MySQL’s Load Data command. Unfortunately, I encountered issues with this as well as it wouldn’t import my date fields correctly (it would import them as all zeros).

From there, in between the cursing, I tried various combinations of DTS and Load Data, and even some distributed queries using Linked Servers. I finally found a nugget of information on the web that allowed me to import all the data in one fell swoop.

The trick is to use DTS to export the data to a CSV file. Then using Excel, select the columns that are dates and go to Format —> Cells. Select a Custom format and then enter in yyyy-mm-dd hh:mm:ss. Re-save the CSV file and then run your Load Data command on your MySQL server.

Using this method, I was able to import all my data including dates and text fields.