October 18, 2012 at 2:26 pm
Good evening to all.
I am new of this forum.
I ha ve a quite good experience in SSIS and SSAS 2008 R2.
My question is about to SSIS in SQL SERVER 2008 R2 and especially is that:
I am not able to add a connection manager in a SSIS.
Clearly i am not able to add a connection manager that connects directly a PROGRESS DB.
In our SQL SERVER server machine, i installed a driver ODBC PROGRESS.
I created also a DSN and this DNS connectioon , was tested okay.
But i cannot understand where to create and how in SSIS...
I have to create an ADO.NET or OLEDB CONNECTION type ???
Has anyone ever used a direct connection to PROGRESS DB ?
I tried in any way but i reached error....
Is it possibile that PROGRESS DB Vendor not compatible with SSIS ???
I tried to connect with my DSN in EXCEL POWERPIVOT or Reporting Services , and I WAS ABLE TO.
I WAS able also to connect with linked server using my DSN,,,,,
(But linked server is little slow and have some limitations to query some features of PROGRESS DB..(extent , name fields with minus and so on).
Why cannot i to create a right connection in SSIS ???
What is it worng with me ???
If anyone can help me with also examples , i will be very happy.
i DO NOT KNOW what kind of connection i ha ve to choose and how to configure.
Many thanks to all.
Mauro.
October 31, 2012 at 10:44 am
If you are using an ODBC connection then it will be an ADO.NET Connection Manager and ADO.NET Source in SSIS. I am assuming you were not able to located a suitable OLE DB driver for this particular database type.
I just did a quick google for this platform and it appears they do have one. If this is the same product you are working with I would suggest using that since OLE DB is more native less buggy in SSIS and SRSS.
http://www.datadirect.com/products/other/ole-db-provider/index.html
Hope this helps!
Eric
October 31, 2012 at 12:59 pm
I connect to a progress database (10.1c version)
We installed the odbc driver and connect to Progress via SSIS to import data into SQL
this is how I use it in SQL 2005
in a ssis package connection manager pane > right click> new connection
in "Add SSIS Connection manager" window select type "ODBC"
in "Configure ODBC connection Manager" > select New
in "Connection Manager" select your installed ODBC driver enter the login/pwd info >test connection
Alternatively in the "connection manager" window you can "use connection string"
this will be something like this
Dsn="WhatyoucalledtheDSN"={Progress OpenEdge 10.1C Driver};uid=administrator;host="yourservername";db="yourprogressdbname";port="yourProgressportnumber"
hopefully this may help you
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 31, 2012 at 4:37 pm
Ciao.
Many thanks for your reply.
I did what you wrote 2 weeks ago.
But i encountered this error.
TEST CONNECTION FAILED BECAUSE OF AN ERROR IN INITIALIZING PROVIDER
ERROR IM14 MICROSOFT ODBC MANAGER ...The specified DSN CONTAINS an architecture mismatch between the dirver and the application....
I think it is a problem of 32 OR 64 BIT driver versus application,
Someone explained me that SSIS is a 32 bit application even if the S.O windows server is 64 bit operating system Windows Server 2008.
I REALLY do not know if who prepared server...., installed 64 or 32 bit odbc driver.
How can i discover that ???
With linked server the same odbc it works and i did some SSIS (with some problems i wrote in another topic..but it works)..
But with a ODBC direct connection i encountered this error...IM14
I installed also a DATADIRECT ODBC Driver 64 bit TRIAL version , and it worked too....
Only with Open Edge Driver not,,so at the momenti i decided to use LINKED SERVER even if i understand it is a slower approach to pull data...
Besides both using SSIS with DATADIRECT 64 bit driver ODBC and LINKED SERVER...READING a table of 6.000.000 of rows i redirect rows for 54.000 records.
The error in this case , is an error very strange wtih code -1071607685This error means NO STATUS AVAILABLE...and it not easy to solve it .....
Did you encountered any similar problems pulling data from OPEND EDGE DB Progress ???
Many thanks if you should want to reply...
Ciao Mauro.:-)
October 31, 2012 at 4:42 pm
CIAO.
Many thanks for your kind reply.
Yes i did that a week ago.
Yes i downloaded a TRIAL version of DATADIRECT ODBC Driver.
I tested and results were okay even if i encountered a problem.
Reading 6.000.000 of rows , 54.000 rows i redirect in a table errors.
These rows were redirect because error -1071607685
But reading about this error, i discovered it means NO STATUS Available...
With a descriotion like this , i cannot imagine what is the trouble...
Regards in advance if you have any useful idea.
Ciao and thanks again..
🙂
November 3, 2012 at 11:21 am
Hi many thanks.
I did a lot of test..and i did that.
I WAS ABLE to connect Progress DB in SSIS 2008 r2 using a trial version of PROGRESS DATA DIRECT odbc driver.(We have a SERVER 64 BIT with a Window Server system 2008)
I WAS NOT ABLE with Progress OpenEdge Driver ODBC that our System Administrator installed on our server.
But i have license only of second type of driver and not for the first...so i decide to work with LINKED SERVER Object.In this case, also with LINKED SERVER i was able to connect.
The strange thing, it is that also LINKED SERVER needs of an Odbc driver,,,
And i use OpenEdge Driver ODBC.
So i really think it is a mix of problems of APPLICATION that i should want use (SSIS or LINKED SERVER) and version 32 bit.
Any idea ???
What kind of Driver Odbc do you use ????
Regards in advance,
PS. The name of dll installed of OpenEdge Driver is called pgoe1024.dll.
I read that clicking ABOUT on DSN Confgiuration in Administrator Data Source.
Ciao and thanks for all.
Have a good week-end.:-)
November 3, 2012 at 11:24 am
Hi many thanks.
I did a lot of test..and i did that.
I WAS ABLE to connect Progress DB in SSIS 2008 r2 using a trial version of PROGRESS DATA DIRECT odbc driver.(We have a SERVER 64 BIT with a Window Server system 2008)
I WAS NOT ABLE with Progress OpenEdge Driver ODBC that our System Administrator installed on our server.
But i have license only of second type of driver and not for the first...so i decide to work with LINKED SERVER Object.In this case, also with LINKED SERVER i was able to connect.
The strange thing, it is that also LINKED SERVER needs of an Odbc driver,,,
And i use OpenEdge Driver ODBC.
So i really think it is a mix of problems of APPLICATION that i should want use (SSIS or LINKED SERVER) and version 32 bit.
Any idea ???
What kind of Driver Odbc do you use ????
Regards in advance,
PS. The name of dll installed of OpenEdge Driver is called pgoe1024.dll.
I read that clicking ABOUT on DSN Confgiuration in Administrator Data Source.
Ciao and thanks for all.
Have a good week-end.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply