Best way to tranfer data from 2000i to MSSQL2000

[john230873]

The company I work for was bought up using Dataflex, migrated to Pervasive and is currently on Pervasive 2000i SP3. Due to how the company grew at the moment it has to stay with PSQL for the main transactions. The idea is to have a MSSQL server, load it once a night and use it as a data warehouse for reports. Currently the fastest way we have found to extract the data from Pervasive is using the API's, this takes about 3 hours (largest file being 4,000,000 records). To do this we have about 10 programs running at the same time which reads each record and write them to csv’s. Once all the CSV's are written then a DTS package runs on the MSSQL to load them in. Whole process takes about 6 hours.

I would like to do away with the CSV files and all the import programs and use the ODBC or OLEDB layer of Pervasive but I am having problems.

Speed is the big issue the importing reading of the PSQL data is so slow reading at about 400 records a second, is this the norm ?

I have noticed that the CPU on the computer running the server is at 100 %, this is a HT P4 2.8 gig. Should this happen?

Any help would be appreciated

 

[vbajock]

Are you familiar with DTS packages in SQL Server ? Set up a Pervasive ODBC connection on the SQL Server host machine. Next, look for Data Transformation Services in the SQL Server Enterprise Manager under the database you want to move Pervasive data to. Start DTS and a wizard interface appears. You can use the wizard to move your data. If you've got someone who can program in VB or Access VBA, you can code custom DTS packages to do whatever you want them to do. Also, Wrox press has a book on DTS that is really good, you might want to get a copy.

Solutions to your speed problems lie in your hardware. Use a gigabit connection between the PSQL box and the MS SQL box. Stuff both of them full of Ram. Use a fast disk, etc. We are using two dual processor servers, both with 2 gigs of Ram and Ultra 360 SCSI disks using Intel Gigabit cards and a Linksys switch, both are running 1.8 gig CPu's. We can move a couple of million records in about two hours.

When you say "the CPU on the computer running the server is at 100 %, this is a HT P4 2.8 gig" you didn't say whether this is the MS SQL or PSQL server. The server pulling the data is going to really get jacked.