Progress To Sql Server

  • Hi,

    I am using SSIS to transfer a Database from progress to Sql server.

    I am using OpenEdge 10.1B ODBC driver to connect to progress.

    Here Progress is Source and a Sqlserver is the destination

    I have created a Linked server from my Destination Sqlserver to the Progress Database.

    Now how can i Transfer the whole Progress Database to Sql server using SSIS.

    Can i user Transfer Database Task to import Progress DB to SQL Server.

    Please let me know how i can use the Linked server as my source ??

    Regards

    Ranjeeta

  • Hi my name is Mauro and i am new of SSIS sql server 2008 r2.

    I am writing to you because i read you are the only programmer that did a SSIS to transfer data From Openedge Progress DB to a SQL SERVER Database.

    Me too i have installed OpendEdge Odbc Driver for Progress ,,,,10.2b and not 10.1b as you,in you example of three years ago...

    My problem that i received an error when i create a connection manager to connect to DB PROGRESS.

    The error is that...

    Test connection failed because of an error in initializing provider error [IM14] [Microsoft] [Driver Manager ODBC] Errors found! The specified DSN contains an architecture mismatch between the Driver and Application."

    If i try to connect to my OPENEDGE Progress DB from Powerpivot of Excel or with Reporting Services...i can do it...

    I also copied and paste all data connection to connection manager connection string,,,but nothing..

    I selected -.NET Providers /Odbc data Provider

    Then i selected DSN to my OpenEdge Progress that i create previuosly.

    SORRY i wrote ....

    I hope you can give me any suggestions.

    Ciao Mauro.

  • Maybe you have a mismatch between 32-bits and 64-bits? If I remember correctly, the Progress ODBC driver is 32-bits only, so trying to connect from a 64-bits environment does not always work.

  • Ciao many thanks for your kind reply.

    For this , me too i have the same doubt.

    I used also LINKED SERVER but with it , i encountered a syntax problem i was not able to solve.

    The problem is written below.

    I have a query with a field name with MINUS character and this it does not like to LINKED SERVER.

    wHAT CAN I DO ????

    Do you ha ve any suggest to override the MINUS problem ???

    For this reason, i am trying to solve problem with reading directly PROGRESS OPENEDGE

    In any way i write below my query in LINKED SERVER too, in BOLD font the name field with MINUS...

    The query give me the error written below after the query...

    It seems LINKED SERVER treat character '-' as a keyword or reserved word.

    In any way...i am blocked about these problems...

    T H A N K S in advance if you have any suggest.

    Ciao Mauro.

    ======================

    SELECT 1 AS CodAzienda,

    codpro AS CodProdotto,

    codcema AS CodMagazzino ,

    scmin AS Scmin , scmax AS Scmax, priord AS PuntoRiordino, leco AS Lotto, locali1 AS Locali1 , locali2 AS Locali2, locali3 AS Locali3, locali4 AS Locali4, locali5 AS Locali5, locali6 AS Locali6, abcpick AS ABCpick , abcsco AS ABCsco , abcfatt AS ABCFatt , tipvend AS Tipvend, indrot AS Indrot, coefsic AS CoeffSicurezza, tiprior AS TipoRiordino ,leadtime AS LeadTime, settor AS SettOrd, dommed AS DomMed , devsta AS Devsta, incmed AS Incmed , scsic AS Scosic, dotmin AS Dotmin, limmax AS Limmax, stprod AS Stprod, settorf AS SetOrdFor , cuaz AS CuAz , cmcaz AS CmcAz , Cufil AS CuFil, cmcfil AS CmcFil, dtultve AS UltimaDataVendita, codfil AS Codfiliale , CONVERT(DECIMAL(18,0),REPLACE(SUBSTRING(quantita,1,CHARINDEX(';',quantita)),';','') ) AS QuantitaGiacenzaAttuale,

    CONVERT(DECIMAL(18,2), REPLACE ( SUBSTRING(importo,1,CHARINDEX(';',importo)),';','') ) AS ValoreGiacenzaAttuale

    FROM openquery

    ( COMETA_BASE01P ,

    'select parmag.codpro, parmag.codcema , parmag.scmin, parmag.scmax, parmag.priord, parmag.leco, parmag.locali1, parmag.locali2,parmag.locali3,parmag.locali4,parmag.locali5,parmag.locali6, parmag.abcpick,parmag.abcsco,parmag.abcfatt,parmag.tipvend,parmag.indrot, parmag.coefsic,parmag.tiprior,

    parmag.leadtime, parmag.settor,parmag.dommed, parmag.devsta,parmag.incmed, parmag.scsic, parmag.dotmin, parmag.limmax,parmag.stprod, parmag.settorf, prezzi.cu AS Cuaz , prezzi.cmc AS CmcAz, ypremag.cu AS CuFil, ypremag.cmc AS CmcFil, parmag.dtultve , yfilmag.codfil , magsin.quantita , magsin.importo

    from pub.parmag

    left outer join pub.magsin on pub.magsin.codpro = pub.parmag.codpro

    and pub.magsin.codcema = pub.parmag.codcema

    left outer join pub.prezzi on pub.prezzi.codpro = pub.parmag.codpro

    AND PUB.PREZZI.pre-anno = 2012

    left outer join pub.ypremag on pub.ypremag.codpro = pub.parmag.codpro AND pub.ypremag.codcema = pub.parmag.codcema

    left outer join pub.yfilmag on pub.yfilmag.codcema = pub.parmag.codcema

    where parmag.codpro <> '''' ')

    =======================

    ========E R R O R===========

    OLE DB provider "MSDASQL" for linked server "COMETA_BASE01P" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column "PUB.PREZZI.PRE" cannot be found or is not specified for query. (13865)".

    Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing the query "select parmag.codpro, parmag.codcema , parmag.scmin, parmag.scmax, parmag.priord, parmag.leco, parmag.locali1, parmag.locali2,parmag.locali3,parmag.locali4,parmag.locali5,parmag.locali6, parmag.abcpick,parmag.abcsco,parmag.abcfatt,parmag.tipvend,parmag.indrot, parmag.coefsic,parmag.tiprior,

    parmag.leadtime, parmag.settor,parmag.dommed, parmag.devsta,parmag.incmed, parmag.scsic, parmag.dotmin, parmag.limmax,parmag.stprod, parmag.settorf, prezzi.cu AS Cuaz , prezzi.cmc AS CmcAz, ypremag.cu AS CuFil, ypremag.cmc AS CmcFil, parmag.dtultve , yfilmag.codfil , magsin.quantita , magsin.importo

    from pub.parmag

    =========================

  • Judging from the error message I would say that the column PREZZI.PRE is missing from the SELECT statement specified for the OPENQUERY.

    Also the "AND PUB.PREZZI.pre-anno = 2012" will indeed give errors: you have to quote the column name when reserved characters are used, like so:

    AND PUB.PREZZI."pre-anno" = 2012

  • Thanks too much ....

    Quoting name of field as you suggested , i solved my problem.

    I have to write a .docx to remeber all particular syntax querying data from an OPENEDGE database.

    Besides this problem i encountered a problem with 3 problems :

    SQL WIDTH of field different from format of the field.

    (I solved this using DBTOOL utility of PROGRESS RDMS)

    field as EXTENT (similar to array) i found only in OPEND EDGE PROGRESS DB.

    (I solved this , treating these kind of fields,

    Finally this problem to query name of field with MINUS CHARACTER....

    T H A N K S a lot.

    PS. Do you suggest to write all problems i encontered pull data to an OPEND EDGE PROGRESS using LINKED SERVER ?????????

    Regards and many thanks AGAIN !!!!!

    i FEEL better...:-)

  • Can't comment on DBTOOL etc., I know nothing about Progress DB administration. I've used a linked server as an experiment, but I chose to approach the database directly from my C# application with the Progress ODBC driver. I didn't see any added value of the linked server for my use case. If I remember correctly, you still have to use Progress/ODBC SQL syntax even if you use a linked server.

  • Hi thanks.

    Okay i did as you want.

    I confim that you have to use SQL-ODBC PROGRESS EDGE in LINKED SERVER OPEN QUERY statement.

    If i understood good, a day i had time , i could create a SSIS and try to connect opend edge progress db , USING a SCRIPT TASK object and write a script to connect via ADO.NET

    I will try.

    ...

    Ciao many thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply