Tuesday, October 18, 2011

SQL Server: How to Import Data from Pervasive


To extract data from pervasive data files Pervasive database Engine is required. Latest Trial version 11 can be downloaded from http://www.pervasivedb.com/psqlv11/pages/default.aspx
Once you have installed pervasive Engine, follow following steps to view and import it into SQL Server.
1. Open Control Center through programs>Pervasive> PSQL v11> control Center and documentation.

2.       Make sure services pervasive PSQL Relational Engine and pervasive PSQL Transaction Engine are running (Expand services in Left panel)
3.       To open a database Expand Engines>[ server name] > Databases
4.       Right Click on Databases and select New>Database
5.       In Database Name Box give a name to your database.
6.       In Location Box locate the database files path.  (.mkd, .ddf, .dat)
7.       You can uncheck “relational integrity enforced”.
8.       Click Finish to complete the process.


 9.    Expand Database and you can see the added database.
10.   Double click on a table to see records
 

Moving data into SQL Server:
1.       Right click on target database and select Tasks> import data to open import and export wizard.
2.       Hit next on the welcome screen.

4.       To choose a data source click on the drop down list and select Pervasive Provider, Release 3.2
5.       Under Standard connection: specify pervasive Database Name, Host and server DSN as shown in figure below.  DSN DEMODATA is automatically created by Pervasive Engine.
6.       Click next.
7.       Chose SQL server native Client as Destination. Hit Next.
8.        Click next
9.     Provide Query to extract data from a given table. Click parse to verify. Click next
10.   Change Destination table name and click on Edit Mappings button.  You can also preview source data.
11.   In column mappings you can specify Field names, data type and size for destination table.
12.   Click "OK" button to close Column mappings and hit finish to execute the import process.
13.   On successful completion refresh database in SQL Server to see imported data.

3 comments:

  1. Nice. This method allows for one Table to be imported at a time. Is there a way to import multiple tables at a time?

    ReplyDelete
  2. Can we do multiple tables at a time?

    ReplyDelete
  3. I'm trying to use this method to import a table from a Pervasive database to SQL Server and it's only importing 999 rows. Any idea why it's not loading the entire table.

    ReplyDelete

All suggestions are welcome