none
ODBC error on ADO.net connection

    Question

  • Hi ,

    My package has three data flow tasks and the source is an Ado.net using ODBC driver i am extracting data.The issue i am having the first data flow task runs successfully and the second one fails.I tried disabling the first one then second data flow runs success and third data flow throws me an error.I don't why it is failing for the second task.The error i am getting is

    [ADO_SRC [1941]] Error: System.Data.Odbc.OdbcException: ERROR [IM005] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
       at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
       at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.Odbc.OdbcConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
       at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

    Please suggest me how to overcome this error.

    Thanks in advance

    Saturday, June 29, 2013 10:58 PM

All replies

  • What kind of data source are you trying to connect to with the ODBC driver?  Someone might be able to help you.  Are there any other errors associated with this or is it just the one?

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, June 30, 2013 1:39 AM
  • Thanks Russel for the reply. Data source is Knowledge base systems.Ya i got some other errors.

    My package extracts data from source and performs look up and load data in to tables.I have 8 data flow task extracts and perform look up and load data in to tables.Out of 8 data flow tasks 4 are running successfully and the rest are failed.

    [ADO_SRC [1941]] Error: System.InvalidOperationException: NO_DATA - unable to allocate an environment handle.
       at System.Data.Odbc.OdbcHandle..ctor(SQL_HANDLE handleType, OdbcHandle parentHandle)
       at System.Data.Odbc.OdbcConnection.CreateStatementHandle()
       at System.Data.Odbc.CMDWrapper.CreateStatementHandle()
       at System.Data.Odbc.OdbcCommand.GetStatementHandle()
       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.PreExecute()
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

    The other error i got


    [ADO_SRC [1941]] Error: System.Data.Odbc.OdbcException: ERROR [IM005] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
       at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
       at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.Odbc.OdbcConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
       at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

    Any ideas or suggestion to resolve the issue is appreciated.

    Sunday, June 30, 2013 2:25 AM
  • What is the source? EXCEL,AS400,Oracle,SQL Server?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 30, 2013 6:28 AM
  • Thanks for the reply.Data source is Knowledge base systems Sql server.Using ODBC driver i am extracting data.
    Sunday, June 30, 2013 6:40 AM
  • If it is SQL Server why not using OLEDB?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 30, 2013 8:17 AM
  • Thanks for the reply.sorry my data source is kb_sql .i can connect to source only using odbc driver.please suggest me how to resolve the issues.
    Sunday, June 30, 2013 3:53 PM