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

• maretix

  Hall of Fame

  Points: 3835

  More actions

  October 28, 2012 at 11:23 am

  #283830

  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 http://www.psdn.com )

  (http://communities.progress.com/pcom/message/116419#116419)

  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>)),';','') ) 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.