none
Problem: SSIS, ODBC and Progress Database

    Question

  • Hi,

    The project I´m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

    But when I tried to create a DataRearder Source pointing to my ODBC Data Source,  and open the next tab I received this error message:

    “Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

    Anyone can help ?

    Thursday, December 15, 2005 4:16 PM

Answers

  • um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

    If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

    Thanks

    Wenyang

     

    Thursday, December 15, 2005 8:26 PM

All replies

  • DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

    Thanks

    Wenyang

    Thursday, December 15, 2005 6:10 PM
  • Wenyang,

    Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

    But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

    “Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

    Appreciate your attencion.

    [ ]´s

    Thursday, December 15, 2005 6:52 PM
  • um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

    If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

    Thanks

    Wenyang

     

    Thursday, December 15, 2005 8:26 PM
  • were you able to slove this problem..

     

    I am also facing similar problems.

    Monday, July 17, 2006 3:39 AM
  •  

    Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

    When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

    "Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

     

    Can any body help me ????

     

    Regards,

    Chetan

    Saturday, July 22, 2006 3:11 PM
  • I had a similar issue, the error I was getting:

    "Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

    I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

    Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

    Monday, August 07, 2006 1:54 PM
  • I had a similar issue, the error I was getting:

    "Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

    I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

    Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

    Monday, August 07, 2006 2:43 PM
  • Hi, I am facing problem to connect to progress db in .net,

    Would you please let me know of the connection string and other settings ,

    I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

    but then i get error when i try set anything else.

    Thanking you.

     

    Thursday, December 14, 2006 10:55 AM
  • Problem solved

     

    Tuesday, June 05, 2007 8:32 AM
  • Hi,

     

    Could you please share your experience with us?

     

    Thanks

     

     

    Tuesday, August 14, 2007 3:17 AM
  • I have a simular issue. I am connecting to Progress 9.1D database. I setup a data source in the solution explorer and then add it to the connection manager to eventually use it in a datareader. Afer completing, I get insufficient information to connect. Odd thing is I have 2 other connections to other Progress db's. When I set them up I had the same issue then. They work now. This problem is repeatable but the solution is not yet found. I beat my head againt the wall for hours then all the sudden it works. I thought I had it figured out that I needed to save the project prior to binding the data reader but I am now adding another connection and it is failing me. I waiting for it to heal itself again. Grrrr. Wierd thing is I can open it with Server explorer and it works there. I can see the tables, data everything. I go back to project and still get the error. So aggravating and depressing Smile   I created another connection in the Data Source view with one of the known good connections. I get the error. When I compare them the appear the same except for the name. I am beginning to think this is a bug with SSIS and VS and the uuid is not bound in the project. Some how I have accidently made this occur on my prior attempts. Does anyone have an idea? Thanks. Joe
     
    Thursday, November 29, 2007 5:16 AM
  • Joe

     

    As for the "insufficient information to connect", make sure your DSN has all of the required parameters such as user id, password, database etc., as shown below.

     

    uid=ABC;Dsn=XYZ;pwd=PQR;

     

    Since we have many Progress databases to connect and fetch data from, we use "dynamic" scripts via Expressions to set the Source Progress DB connection and fetch data from. It seems to be working well. The one issue we still have is with connecting to Double Byte Progress Databases such as Korea, Hungary and Japan. We have this working for 4 years using SQL Server 2000 DTS packages but does not work (for some reason) in SSIS. Logged a ticket with DataDirect, Progress and QAD. Posted in many forums including here and I have not gotten any response yet. Let me know if you have a similar issue and how you went about solving it.

     

    Thanks

     

    Jagannathan Santhanam

     

    Thursday, November 29, 2007 2:32 PM
  • Jag,

    Thanks for the feedback. I do have all the parameter except pwd. Interesting thing, and I not the one who controls it, is no pwd. The best reason I have been explained is to do with Progress users verse OS user configuration. We are running MFGpro and there is concern about breaking it.

    I did not think about submitting a ticket to QAD. I guess felt it was more of a MS problem.I have OE 10 client and Datadirect 9D client installed. And I have both connecting and working in one package I created. At this point, I cannot even add another conection that is pointing to the ODBC connection that works. So same thing, same error. One that I added a week or so back where, I had the same issue. But I did something but I guess I am not sure what I did to make it work, I thought all it I did was save the file. But fails me now. Very strange.

    Joe
    Sunday, December 02, 2007 2:18 AM
  • I got it to work! Although my connection has no password and I placed 1 space in the password box and it now works.

    Finally!!! Thanks for giving me the motivation to try again Jag. If I can help you in any way please let me know. I am connecting to Progress 9D and OE 10 with SSIS.
    Joe
    Sunday, December 02, 2007 2:50 AM
  • Joe

     

    Great to know it works for you now! We use Progress 9.1E on HP Unix and are finding it impossible to connect to a Double Byte Progress database (Korea, Japan). Do you have any such requirements?

     

    Thanks

     

    Jagannathan Santhanam

     

    Sunday, December 02, 2007 10:51 PM
  • We are attempting to access a Progress DB via ODBC. On our client's server, the DSN has been successfully setup (previously, by our client) and the connection tests successfully from within the ODBC administrator. We can also connect to the Progress DB via ODBC and read data from within Crystal Reports as well as from within MS Access. The ODBC administrator describes this ODBC driver as: "DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E".

    The problem comes when trying to access this ODBC DSN from an ASP.NET-based application called Dbnetgrid. I have installed the Microsoft ODBC .NET data provider which supposedly provides ODBC connectivity to the .NET environment.

    Within Dbnetgrid, the connection string looks like the following:

    "Provider=ODBCprovider;Dbmsname=??;DSN=Progress;UID=xxx;PWD=yyy;"

    where the correct DSN name is actually "Progress". But I don't know the correct Dbmsname for the Progress database. I have made many guesses without success. I can connect to other (non-Progress) databases via ODBC with, for example, a connection string such as: "Provider=ODBCProvider; DBMSName=Microsoft SQL Server; DSN=testdb; UID=xxx; PWD=yyy;"

    If I take out the "DBMSName=" attribute, I get an error message that a DBMSName must be supplied.  If I take out the "Provider=" and "DBMSName=" attributes, I get an error message that the "DSN=" keyword is not recognized.  If I try "DBMSName=Progress" I get a message stating that the DBMSName is not recognized. I've also tried ODBC connection strings which specify a Driver and a Database, rather than a DSN, but I still get the message that a DBMSName must be provided.

    Can anyone help by providing the correct DBMSName for a Progress database using the "DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E" ODBC driver? If not, could someone please direct me to another source for help/support on this issue?

    Thank you.

    John Zappe
    infoSpectrum
    South Portland, ME
     
    Thursday, December 20, 2007 1:46 AM
  • Can you use a ADO.NET provider to make use of the ODBC connection to Progress? It works with a NON double-byte Progress 9.1E database. It DOES NOT / WILL NOT work with a double-byte Progress 9.1E database. We are currently spinning our wheels with Microsoft, Progress etc., which is not turning out to be a pleasant experience to say the least!!

     

    Thursday, December 20, 2007 3:17 PM
  • What do you mean by using the ADO.NET provider?   My application is ASP.NET based and requires the "Microsoft ODBC .NET Provider" to connect the .NET environment to ODBC sources.  Maybe this is the same thing as using ADO.NET?   Or are you talking about C# programming?  It would seem that the "32-bit" part of the driver name might imply "double-byte"?

    Thursday, December 20, 2007 6:21 PM
  • On hind sight, you should probably pose this question on a forum appropriate to ASP .NET development. I was talking purely from a SSIS perspective. And, "32-bit" has nothing to with "double-byte". I was referring to the Korean and Japanese Progress databases which are double-byte or unicode compatible.

    Thursday, December 20, 2007 9:52 PM
  • Hi, i have had the same happen to me, and posted my own question her [http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/afd12610-96fd-491a-a3e5-514bcf7614a9], as i didnt see this page in time..

    i have tried the ADO.NET ODBC connection which has partly worked (woohoo) but now i get this error

    TITLE: Microsoft Visual Studio
    ------------------------------
    Error at Data Flow Task [DataReader Source [200]]: System.Data.Odbc.OdbcException: ERROR [22003] [NCR][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value.
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
       at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)
    ------------------------------
    ADDITIONAL INFORMATION:

    ERROR [22003] [NCR][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value.  (TDATA32.DLL)

    any new ideas !!


    Tiny
    Friday, February 20, 2009 11:16 AM
  • Tiny,

    Is this in the inital connection or are you trying to push data straight to a sql server?  I was using a data conversion object, and ran into a lot of problems with Unicode variables coming out of Progress and how SQL server didn't like that very much.  I changed them all to strings and it got rid of the problems.

    hth,
    M@
    Tuesday, March 17, 2009 2:34 PM
  • I got a successful connection but it is not giving me any input columns?  How come these aren't showing up?
    Mike
    Friday, May 08, 2009 2:25 PM