Monthly Archives: July 2011

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

Importing data from OpenEdge’sProgress® data base is simple if you have OLE DB data provider. But if youdon’t have OLE DB driver or unable to configure it properly then there are fewsimple 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 withonly version with which this backup files was created i.e. you can’t restoreProgress 9.1 backup file on Progress 10 or newer version)
We will achieve our data migrationgoal through two step, first to import table structures and import data fromflat files to newly created tables.
Copying Table Structuer:
Once you have restored yourrequired backup file, you can access it from “Data Administrator” by connectingyour database file.
 Progress normally keep itsdatabases with extension “db” at installationdrive\Progress\WRK\YourDatabaseName.db
Once you are connected to yourdesired database, click on “ProgressDB to M SQL Server” through given path.
Provide necessary information forProgressDB to ODBC Conversion. Type any name of your choice for “Name of schemaholder database” and correct ODBC data source name.

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

Extracting Data
In next step we will extract tabledata to CSV files.
Select table of your choice.
 Provide file name with target folderpath. Select “All (Max 255)” fields to export. You can provide WHERE clause tofilter 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 datais imported in SQL Server.
 Now you have tables structure anddata in text format. Execute simple Data Import process to import your desireddata from text files to already created tables.
Advertisements

SQL Server: Import Data from Sybase Advatage (adt Files) using SSIS

In last post, we have learned to free a Sybase Advantagetable (“adt” file) from its data dictionary, so we can import its data to SQLServer.
To extract these “adt” files by using SSIS package first weneed a “New OLE DB Connection”.
Select “Advantage OLE DB Provider” as provider and completefolder address in “Server or file name” section. Use “adssys”  as username and leave password blank. Don’t forget to change server type value to “ads_local_server”, by clicking on “ALL” button on left of connection Manager. In Initial catalog provide “adt” filename and press “Test Connection ” to test newly created connection.

Drag a new OLE DB Source
Double click “OLE DB Source” to open it in editor andprovide OLE DB connection manager and name of required table.
Open properties page for “OLE DB Source” and mark TRUE to“AlwaysUseDefaultCodePage
Now you are ready to import data from OLE DB Source to anytype of destination. You can provide OLE DB Destination to insert data directlyinto a SQL Server database. In this demo we have use Flat File as destination.

SQL Server: How to Import Data from Sybase Advantage (adt Files)

Sybase Advantage database create separate files for eachtable and its indexes. Table files are created with extension “adt”. One canimport data from Sybase Advantage to SQL Server if she has
  • Accessto adt files
  • AdvantageOLE DB Provider
But import process is bit tricky because first you must freetarget “adt” files from its database dictionary, which can be achieved by usingAdvantageData Architect. It can be downloadedfrom http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=16&Product=8&Platform=6. Once you have install Advantage Data Architect, follow these step to freeyour target adt files.

1.       Clickon new connection wizard

2.       Createa connection to a directory of existing tables

3.       ProvideDatabaseName of your choice and thenprovide correct path of folder where adt files are located on your hard driveand press finish.

4.       Onceyou have created connection, it will start showing adt tables BUT still youcan’t open or export these tables as these are bound to directory.

5.       Tofree these tables, click on Tools –> Free Data Dictionary Bound Tables

6.       Provideadt file name with its complete pathand press OK button.

7.       Youradt table is now free. Now you canopen it in Data Architect. You can export or you can close Data Architect andimport this table from SQL Server Import Wizard.

Note: In next post, we will explore a simple method to import adt files by using SQL Server Integration Services Package.

In next post: How to import adt files by using SSIS package