I would create all destination tables first, then use a DTS package to xfer the data.
Then, in the package(s), I would create a step that truncates the table(s), then copies the data. That way, you can run the package(s) multiple times until you are comfortable that the data was successfully migrated. Once you have your tables created correctly, then, you can run the DTS packages w/o worrying about the package changing the datetype/lengths. If you continue to have problems with the identity, you might need to check the "enable identity insert" option on the task's options tab. M!ke -----Original Message----- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Saturday, September 24, 2005 9:59 AM To: CF-Talk Subject: SOT: Migrating MySQL to SQL Server I need to migrate numerous web sites from MySQL 4.0 to SQL Server. My datatypes and queries are all pretty simple, except that I make use of auto_increment columns. This should be fine since SQL Server supports the identity property, but I'm having some difficulty using DTS to migrate the data. Part of the annoyance is that DTS seems to ignore field width definitions for varchars... ie, a varchar(255) in mysql gets changed to varchar(x) where X is the largest current value in that field. Easy enough to work around because I can edit the SQL during the data transformation process. DTS also seems to think that mysql "int(11)" fields should be "decimal" in SQL Server. Again, easy enough to fix. My problem is with the auto_increment columns. I edit my CREATE statement to set the identity property, and the create works fine but the data load fails. Has anyone had any experience migrating this kind of data from MySQL to SQL Server, and maybe offer up some tips? Thanks. Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219171 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
