Monday, July 25, 2011

SQL Server: Extracting Data from OpenEdge Progress without OLE DB Driver

Importing data from OpenEdge’s Progress® data base is simple if you have OLE DB data provider. But if you don’t have OLE DB driver or unable to configure it properly then there are few simple steps to migrate data from OpenEdge’s Progress® to SQL Server.
(Worst thing in Progress ® database I found that you can only restore your database from backup file with only version with which this backup files was created i.e. you can’t restore Progress 9.1 backup file on Progress 10 or newer version)
We will achieve our data migration goal through two step, first to import table structures and import data from flat files to newly created tables.
Copying Table Structuer:
Once you have restored your required backup file, you can access it from “Data Administrator” by connecting your database file.
 Progress normally keep its databases with extension “db” at installation drive\Progress\WRK\YourDatabaseName.db
Once you are connected to your desired database, click on “ProgressDB to M SQL Server” through given path.
Provide necessary information for ProgressDB to ODBC Conversion. Type any name of your choice for “Name of schema holder database” and correct ODBC data source name.

On pressing OK button, it will create “.sql” file on “installation drive\Progress\WRK\”. This sql file contains create table query for all database tables. Open this .sql file in SQL Server and create tables.


Extracting Data
In next step we will extract table data to CSV files.
Select table of your choice.
 Provide file name with target folder path. Select “All (Max 255)” fields to export. You can provide WHERE clause to filter output rows. Press OK button to proced
 Provide any record start string. It will add given string at start of each row. Which you can remove, once data is imported in SQL Server.
 Now you have tables structure and data in text format. Execute simple Data Import process to import your desired data from text files to already created tables.

2 comments:

  1. My name is Matt and I work for Dell. Thank you so much for the information of SQL Server: Extracting Data from OpenEdge Progress without OLE DB Driver. I really liked your way of explanation through the images.

    ReplyDelete
  2. I'm using Progress 9.1D on Sun Solaris and need to extract the data using a where clause, but I can't seem to get it correct. I'm a MS SQL Server person not Progress. In the where clause I'm entering year(eff_date) = 2011. I've also tried where year(eff_date) = 2011 and get the error message "Re-check the syntax you entered." Can you provide some feedback on what I should be entering?

    ReplyDelete

All suggestions are welcome