SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSIS and READ OpenEdge Progress Db To WRITE SQL SERVER 2008 R2

SSIS and READ OpenEdge Progress Db To WRITE SQL SERVER 2008 R2

SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2371 Visits: 239
Ciao to all people reading this very good Forum..

If you use Open Edge Progress Database and want to pull data fro that DB to SQL SERVER 2008 R2, using SSIS my experience was that :

I encontered some problems :

1) Sometimes querying any tables, i got error about 'Precision exceed of table.field'
To solve that i learned about DBTOOL of PROGRESS DBMS.
This utility , running of an table, resize SQL-WIDTH properties of a field of a table in the dictionary.
For example in Progress DB could have a field with FORMAT "X(10)" AND sql-width x(20)
Sometimes happens that in a field like that you can store more than x(20) CHARACTERS so you can encontered that error.
DBTOOL of Progress will solve it.
It is a typical problem with OpenEdge Progress DB.
I advice to run DBTOOL over all tables of an OPEN EDGE DB , if you currently use to run SSIS over OPEND EDGE DB PROGRESS...
I did it, writing a procedure in our ERP , over all tables i query with ODBC...
Here an useful Link of a discussion about DBTOOL utlity, (from www.psdn.com )

2) Sometimes i encontered error generic as 'NO STATUS AVAILABLE' on reading some records.
Error code - 1071607698
For that error i did not find any solution...None was able to help me.
Some very kind people in this blog, suggested me to try with other version of ODBC Drivers...
I used LINKED SERVER either a trail version of DATA DIRECT ODBC either ODBC OPenEdge 10.2.B.
Nothing changed....
In that case i did SSIS without ODBC and with good old flat file.....as source file...it is a pity that i did not understand WHY !!!!!!!! :-)

3) In OPend Edge Progress DB , you could have to read also some strange fields called EXTENTS.
They are array of elements....
To read them (e.g in LINKED SERVER) you have to consider that you have to convert them from a string to a decimal value, using useful string functions of T-SQL...
To clear that...i write here an example...to read element number 2 of a extent of 50 elements...
CONVERT(NUMERIC(18,2), REPLACE ( SUBSTRING(<name of the table filed>,1,CHARINDEX(';',<name of table filed>Wink),';','') ) END ) AS QuantitaGiacenzaAttuale

I hope that i wrote it could be useful to someone.
I hope also that if someone had other experience about this topic, could reply me ...
Surely someone solved better problems i listed here.

Many thanks to all..

Ciao Maretix.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum