locked
OCIEnvCreate Failed RRS feed

  • Question

  • I am connecting to an Oracle Instance using SSIS. I modified the tnsnames.ora file and included the tsn entry for this particular server. I am using a SQL Command to select the data. However it is not working and I am not getting a very conclusive error as well. The error reads "OCIEnvCreate failed with return code -1 but error message text was not available".The funny thing is this is the stage environment and this package works absolutely fine in the dev and prod environments. I am using .Net Providers\OracleClient Data Provider. i tried using the .NET Provider for OleDB but get the same result.

    Please advise. Need to close this as soon as possible.

    Please revert for any clarifications.

    Thanks,

    Nimish

    Monday, January 16, 2012 7:51 PM

Answers

All replies

  • Hi Nimish,

    please publish the error message entirely.

    At this point I think there is a difference in driver version/patch or its configuration.


    Arthur My Blog
    Monday, January 16, 2012 8:10 PM
  • Hi Arthur,

    There is no accurate message, I am pasting the message details though ( I get this when i try to preview the resultant data set )

    ===================================

    There was an error displaying the preview. (Microsoft Visual Studio)

    ===================================

    OCIEnvCreate failed with return code -1 but error message text was not available. (Microsoft.SqlServer.ManagedDTS)

    ------------------------------
    Program Location:

       at System.Data.OracleClient.OciHandle..ctor(OciHandle parentHandle, HTYPE handleType, MODE ocimode, HANDLEFLAG handleflags)
       at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)
       at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
       at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
      

    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OracleClient.OracleConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OracleClient.OracleConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OracleClient.OracleConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
       at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns)
       at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)

     

    Hope this helps.

     

    Tuesday, January 17, 2012 9:09 AM
  • see this:

    http://oraclepoint.com/oralife/2010/08/19/oracle-odbc-connection-issue-of-system-exception-ocienvcreate-failed-with-return-code-1-on-windows-with-asp/


    Senior BI Consultant & PM @ Bicentrix
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com Follow Me
    Tuesday, January 17, 2012 12:07 PM
  • Hi Onur,

    Thanks for the reply.

    Now i am getting another error.

    ===================================
    
    There was an error displaying the preview. (Microsoft Visual Studio)
    
    ===================================
    
    ORA-12557: TNS:protocol adapter not loadable
     (Microsoft.SqlServer.ManagedDTS)
    
    ------------------------------
    Program Location:
    
       at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)
       at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
       at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OracleClient.OracleConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OracleClient.OracleConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
       at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns)
       at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)
    
    

     

    Regards,

    Nimish

    Wednesday, January 18, 2012 10:09 AM
  • Hi Nimish,

    Here is the similar thread, please refer to TarMAc's reply:

    http://social.msdn.microsoft.com/Forums/en/netfx64bit/thread/3f00bf30-2d81-4e6c-9bcc-16e06ebf46eb

    For more information about the issue, please refer to the article as below:
    Oracle ORA-12557: TNS:protocol adapter not loadable: http://faizsulaiman.com/oracle-ora-12557-tnsprotocol-adapter-not-loadable/

    Thanks,
    Eileen

    • Marked as answer by Eileen Zhao Tuesday, January 24, 2012 1:49 AM
    Wednesday, January 18, 2012 4:10 PM