Porting Data into SQL using DTS wizard

susanl
04-19-2004, 10:33 PM
Hi,

I am porting data from a mySQL database into a SQL server DB by dumping it out to a flat file and using the DTS wizard and while I am managing to import the users etc. I have a serious problem when it comes to importing the actual forum data (it's from one bulletin board to another)

My first problem
Naturally there are relationships between the topic headers and the replies. In the MySQL database, the relationship is as follows:
all the posts have unique id's
headers posts have topic id = 0
reply posts have topic id = the ID of the header post

However in the SQL Server database way it works is, the header posts have the same topic id as they do unique id. This means I have to do a lookup to get the topic id while performing the data transform and it also means that I cannot get the topic ID from the flat file, so there isno way of correlating all the posts.I have to be honest with you, I do not know how to do this in the wizard. I tried to create a package from scratch but only found it confusing.

You may ask, why not simply copy over the unique id's en masse from the MySQL database.

Wherein lies my second problem

In the SQL Server database (the destination) all messages are stored in the one table with different keys for whichever forum they belong to. They all have their own unique id. Whereas the MySQL has a unique id for each table. So you could get message no. 123 in one board and also in another. That means that there are going to be non-unique keys all over the place unless I can maybe see if the original database can have all its keys redefined (a big task!)

I cannot see any way out of this one, but if anyone has any suggestions at all, I'd be delighted to hear them!!

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum