Testing ODBC connection manager

Hot Topic

Need help to make query
subscriber alternate snapshot location and Web sync - 2005 express
RE-Using passed parameters
Cannot open database "eBayTrader" requested by the login. The login failed.
General Network error
Connectivity from Non Sql Servers
Execute SSIS project from ASP.NET page
Parameters in URL - Problem
Inconsistent package failures
Display Row Data As Cols

SQL Server

How to enable Remote Erros on Report Server 2005?
Database Schemas and "This statement has attempted to access data whose access is restricted by the assembly."
Copying a cube but using a different data source
'Convert' and 'MAX' Functions
Cannot get Report Server to work
SQLServer2005_SSMSEE.msi or SQLServer2005_SSMSEE_x64.msi ?
MSSQL 2005/Native Client problem with IColumnsInfo->GetColumnInfo()
Calling report from other machine (without domain)
connection string based CREATE CUBE supported in AS 2005.
SQLExpress Intermittent Login Errors

Testing ODBC connection manager

I am testing out the ODBC connection manager, as part of my beta testing from my company. I created a new conn manager on the palate (dsn pointing to local  sql server). But I am not sure how this one can be used. I can not use any of the Data flow sources which can use this ODBC connection manager. Using the said conn manager in a Datareader source shows up the error "Can not acquire a managed connection from the run-time connection manager".

How do I test out to see that this is working fine

From another thread, I see that Demi is using Data Reader along with ADO .NET provider for ODBC. So do I think that the one I am trying to use is of no use (New Connection -> ODBC) 

Answer this question

Testing ODBC connection manager

  • Anatoly Rapoport

    The managed DataReader Source doesn't know what to do with the native object that the AcquireConnection method of the ODBC Connection Manager is returning.

    Wenyang wanted you to create a new ADO.NET Connection Manager that uses the .NET Framework Data Provider for ODBC, and to test again using that connection manager. Have you tried those steps

    The ODBC connection manager will work with some sources, but not the managed DataReader source.

    -Doug

  • bshell

     Wenyang Hu wrote:

    ... at the connection tray, right click and choose to "New ADO.NET connection", "New", then from the .Net provider drop down list, choose Odbc data provider, then provide your connection information (either choose from your existing ODBC source, or provide your connection string as DSN=...)

    Then add and edit DataReaderSrc, point the connection to that ADO.Net:ODBC connection manager you just create, give a SQL script for its SQLCommand property, you will then be able to see it works just fine.


    This doesn't work.  The datareader source advanced editor says: "Cannot acquire a managed connection from the runtime manager. 



  • Martin Hueser

    Wenyang Hu wrote:

    You can use a native ODBC connection for example at ExecuteSQLTask in SSIS, but since we don't have a native ODBC Source adapter, you will not be able to pull out data into SSIS pipeline using a native ODBC connection (New connection->ODBC).

    DataReaderSource works on ADO.Net connections only, so, the correct way to use DataReaderSrc to pull out data is, at the connection tray, right click and choose to "New ADO.NET connection", "New", then from the .Net provider drop down list, choose Odbc data provider, then provide your connection information (either choose from your existing ODBC source, or provide your connection string as DSN=...)

    Then add and edit DataReaderSrc, point the connection to that ADO.NetSurpriseDBC connection manager you just create, give a SQL script for its SQLCommand property, you will then be able to see it works just fine.

    Please let us know if you have further concerns.

    Thanks

    Hey thanks - That works a treat.

    However, I do not understand the bit about

    "give a SQL script for its SQLCommand property"

    Can you pls explain this I want to simply import the whole table i have create d aconection to into an SQL Servere Db and the file is coming form an .DBF file

  • nub

    You can use a native ODBC connection for example at ExecuteSQLTask in SSIS, but since we don't have a native ODBC Source adapter, you will not be able to pull out data into SSIS pipeline using a native ODBC connection (New connection->ODBC). 

    DataReaderSource works on ADO.Net connections only, so, the correct way to use DataReaderSrc to pull out data is, at the connection tray, right click and choose to "New ADO.NET connection", "New", then from the .Net provider drop down list, choose Odbc data provider, then provide your connection information (either choose from your existing ODBC source, or provide your connection string as DSN=...)

    Then add and edit DataReaderSrc, point the connection to that ADO.Net:ODBC connection manager you just create, give a SQL script for its SQLCommand property, you will then be able to see it works just fine.

    Please let us know if you have further concerns.

    Thanks



  • ecoloney

    You can use a native ODBC connection for example at ExecuteSQLTask in SSIS, but since we don't have a native ODBC Source adapter, you will not be able to pull out data into SSIS pipeline using a native ODBC connection (New connection->ODBC). 

    DataReaderSource works on ADO.Net connections only, so, the correct way to use DataReaderSrc to pull out data is, at the connection tray, right click and choose to "New ADO.NET connection", "New", then from the .Net provider drop down list, choose Odbc data provider, then provide your connection information (either choose from your existing ODBC source, or provide your connection string as DSN=...)

    Then add and edit DataReaderSrc, point the connection to that ADO.Net:ODBC connection manager you just create, give a SQL script for its SQLCommand property, you will then be able to see it works just fine.

    Please let us know if you have further concerns.

    Thanks

    Thanks! That solved my problem. Brilliant!!!!



  • paul Dierick

    I have the same problem pulling from PostgreSQL using the Import Export wizard.  It only has .NET ODBC provider to connect, and when you get to the step of either copying all tables or specifying a SQL statement, the "copy all tables" option is greyed out.

    From what I can tell so far, I am going to have to continue using SQL 2000's Import wizard for all my PostgreSQL, MySQL, and CView imports becuase of this.  In SQL 2000 you can use the copy all tables option because it can read the metadata using the OLE ODBC driver.

  • JG67

    It sounds like you want to be using the SQL Server Import and Export Wizard. This wizard does a lot of the task and conversion configuration for you.

    -Doug

     

  • Roy Armitage

    What do you mean by "give a SQL script for its SQLCommand property" Basically I want to grab an ODBC data source I have (Pervasive SQL) and transform it into a SQL Server 2005 database. Any hints

  • must_be_a_unique_name

    Please someone can help in this problem. I am running into the same issues. I am trying to pull data over from mysql to sql server. First the import wizard greys out so u have to put in 1 query at a time which is pain. and second it does not even work! it takes me through the end of the wizard for me to click finish and then says oops it does not work. there was an error!

    Anyway i tried going through the ssis router cuz its going to be a nightly job. i used the ado.net odbc connection. It worked but the performance is really not acceptable. it took 5 mins to import 24000 rows where as dts was taking 1 sec to do this. i wish i could use the native mysql odbc 3.51 connector and import. can some one give me step by step instructions on how to do that

    Thanks
  • Testing ODBC connection manager
Answer this question