October 21, 2012 at 6:48 am
Hi to all.
I currently use SSIS of SQL SERVER 2008 R2 to create data flow to import data from several data sources.
Now i have a problem to create a SSIS and a connection manager .
My connection manager should be able to connect to a OPENDEDGE PROGRESS Database.
I installed OPENEDGE database Driver successfully.
I did test connection inside ODBC DATA SOURCES Administrator...successfully.
My server operating system is 64 bit...also ODBC PROGRESS DRIVER is 64 bit ...
Inside my SSIS i create Connection Manager selecting .NET Providers - ODBC DATA DRIVER
connecting to a DSN Name previously adn successfully created.
When i confirm this Connection Manager i receive that error.
Test connection failed because of an error in initializing provider. ERROR [IM14] [Microsoft] [Driver Manager Odbc] ...
The specified DSN contains an architecture mismatch between the Driver and Application."
I called Progress Support but they could not help me.
Is is possibile that SSIS 2008 R2 cannot 'understand' certain ODBC drivers vendors like OPENEDGE Progress ????
If i try ad example to connect to a DSN OpenEdge Driver from POwerpPivot or Reporting Services...I CAN....very strange according my opinion...
Can anyone help me ????
Thanks in advance.
October 21, 2012 at 1:49 pm
The specified DSN contains an architecture mismatch between the Driver and Application.
BIDS/VisualStudio is a 32-bit app. You'll also want to install the 32-bit OPENEDGE driver and create a DSN using the 32-bit Data Sources dialog. When you deploy to a 64-bit server if SSIS is run under a 64-bit context it will look for a 64-bit DSN and use the 64-bit driver and your SSIS package should not mind nor need to change when running under either bitness.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 21, 2012 at 3:43 pm
Ciao.
First of all many thanks for your kind reply.
So if i understood good , i have to deploy my dtsx even if it shwo me that error.
So i shoud have test that in 64 bit environment connecting with SQL SERVER MANAGEMENT STUDIO and run my package in 64 bit environmente...
Did i understand good ????
I will try...as soon as possible...i wrote my post today,,,and i an at home...
If i could not understand good , please reply if it possibile.
Ciao and many thanks ..again.:-)
October 21, 2012 at 4:28 pm
Ciao
Now i understood better.
I HAVE to install both OPENEDGE PROGRESS ODBC Driver for 32 and 64 bit ...on my 64 bit windows server 2008.
When i am writing SSIS on Visual Studio i have to use 32 bit version, otherwise i will continue the error i posted.
When i deploy my dtsx , it will be run in 64 environment and it should be work the same because i will have 64 bit OPEN EDGE PROGRESS 64 BIT driver installed.
Is it correct ???
But i have some doubts.
How can i distinguish 2 different versions 32 and 64 bit in ODBC DATA SOURCE Connections of windows server 2008 , when i am in BIDS to write my dtsx ???
IF i have other 3 dtsx built with Linked server that they point to opendedge driver to 64 bit...
They work fine ...Why ??? Because Linked server is an object of SQL SERVER of 64 bit version ???
I will try.
OTHERWISE i can use LINKED SERVER.
i DID tests with LINKED SERVER and these test were successfully !!!.
i AM TRYING to not use LINKED SERVER because with it i have a problem that i was not able to solve.
In PROGRESS DB i have to pull data with some tables with name fields that contains a simbol MINUS (-)
AND this symbol LINKED SERVER is refusing ...
I did not find how to solve that...
the name of field pub.prezzi.pre-anno is truncated because MINUS character is invalid...
In BOLD font the problem that convinced me to try with OPEND EDGE directly connection...
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.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 <> '''' ')
MANY THANKS ...in advance.
October 21, 2012 at 5:56 pm
You need the 32-bit driver installed when:
- you use BIDS for development and debugging
- you want to run a package by calling the 32-bit version of dtexec.exe directly
You need the 64-bit driver installed when:
- you need to create a Linked Server in a 64-bit version of SQL Server
- you want to run a package using the 64-bit version of dtexec.exe (the default for SQL Agent job steps using the SSIS step type)
The different ODBC managers can be found here on your 64-bit server:
- The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
- The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
There may be some crossover when viewing User DSNs: http://support.microsoft.com/kb/942976
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 22, 2012 at 1:16 am
Many thanks !!!!
I will test all what you wrote.
I will inform you.
Thanks.
March 6, 2013 at 8:27 am
I have just run into the same problem :angry:
Minor bit of speen venting. Why on earth woudl anyone want to use Progress for modern applications! the database technology is about 10 years behind SLQ and Oracle (it just about supports SQL92 and has no query optimiser, covering indexes, optimistic locking, database usage statisics or any of the other cool stuff in modern databases). Granted the 4GL language is easy to read, but it is really hard to write OOP type code in P4GL. Even on OpenEdge10b the ODBC drivers seem to be really flaky and using them for a linked server to run direct queries always seems to be a non-starter and you have to resort to openquery()
The number of new programmers taking up the sport is low and the user community is almost non-existent. There is no Express version for new programmers to cut their teeth on so you do not get 'hobbyist' developers upgrading to employees using it.
The visual tools in ADM2 are OK, but they look and behave like Visual Studio 6 widgets.
Please Please Please for the love of God - if you are thinking of deploying a new application don't buy one running on Progress
Rant over
March 12, 2013 at 4:40 pm
Hi after a long time , i have same doubts about ODBC OPEN EDGE driver PROGRESS.
I understood to install both version 32bit and 64 bit of OpenEdge Driver.
You kindly explained me where are stored in system drive.
But after i install...how can i distinguish them to build 2 different DSN with 2 different names ?
And why 32 bit and 64 bit have the same name odbcad32.exe ???
I expected they have odbcad32.exe and odbcad64.exe ...
I ask you because soon i have to install SSAS 2012 STANDARD EDITION in a new server always 64 bit, and i ha ve built some SSIS using LINKED SERVER and OPEN EDGE driver..but still i ' d like to be able to built SSIS without LINKED SERVER , sign your instruction of October 2012...
Regards in advance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply