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

How to create a Connection Manager to access directly to a PROGRESS DB Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 2:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 69, Visits: 141
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 [b]SSIS in [b]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.
Post #1374586
Posted Wednesday, October 31, 2012 10:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:41 AM
Points: 53, Visits: 118
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

Post #1379436
Posted Wednesday, October 31, 2012 12:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 1,885, Visits: 18,475
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 !
__________________________________________________________________
Post #1379493
Posted Wednesday, October 31, 2012 4:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 69, Visits: 141
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.







Post #1379559
Posted Wednesday, October 31, 2012 4:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 69, Visits: 141
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..



Post #1379560
Posted Saturday, November 3, 2012 11:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 69, Visits: 141
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.



Post #1380729
Posted Saturday, November 3, 2012 11:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 69, Visits: 141
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.

Post #1380731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse