Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Progress To Sql Server Expand / Collapse
Author
Message
Posted Thursday, May 13, 2010 12:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 13, 2010 4:46 PM
Points: 3, Visits: 7
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
Post #921014
Posted Sunday, October 21, 2012 7:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 78, Visits: 164
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.


Post #1375171
Posted Monday, October 22, 2012 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:41 AM
Points: 32, Visits: 102
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.
Post #1375371
Posted Monday, October 22, 2012 8:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 78, Visits: 164
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




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


Post #1375441
Posted Monday, October 22, 2012 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:41 AM
Points: 32, Visits: 102
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
Post #1375449
Posted Monday, October 22, 2012 8:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 78, Visits: 164
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...
Post #1375481
Posted Tuesday, October 23, 2012 2:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:41 AM
Points: 32, Visits: 102
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.
Post #1375886
Posted Tuesday, October 23, 2012 9:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 78, Visits: 164
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.
Post #1376147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse