Post reply

Error executing SSIS package connecting to Oracle client

  • December 5, 2013 at 10:56 pm

    #287109

    When executing this package in visual studio it is working fine, but whne scheduling it in SQL & running as a job it gives error :w00t:

    Below is the error info please help

    LogJob History (Transfer_Data)

    Step ID1

    ServerTAMSQL01

    Job NameTransfer_Data

    Step NameSSIS Task

    Duration00:00:08

    Sql Severity0

    Sql Message ID0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Executed as user: TAM\sql.service. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 03:00:01 Error: 2013-12-05 03:00:09.21 Code: 0x00000001 Source: Get Data from Datatransfer Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: OCIEnvCreate failed with return code -1 but error message text was not available. at System.Data.OracleClient.OciHandle..ctor(OciHandle parentHandle, HTYPE handleType, MODE ocimode, HANDLEFLAG handleflags) at System.Data.OracleClient.OciEnvironmentHandle..ctor(MODE environmentMode, Boolean unicode) 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 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ST_de1e5f5fd63b4eb6b9251a2a353d2fbf.csproj.ScriptMain.InsertTarnsferdataIntoTempDatawarehouse(String strQry) at ST_de1e5f5fd63b4eb6b9251a2a353d2fbf.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 03:00:01 Finished: 03:00:09 Elapsed: 7.878 seconds. The package execution failed. The step failed.

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 5, 2013 at 11:10 pm

    #1671372

    Please reply, client need the solution.. i am not able to find it

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 5, 2013 at 11:47 pm

    #1671374

    when you run it in Visual studio, What is the value you set in "Run64bitRunTime"?

  • December 6, 2013 at 12:40 am

    #1671382

    64 Bit runtime setting is false..

    when executing it from visual studio it run success.. but fails at SQL job schedule 🙁

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 6, 2013 at 12:44 am

    #1671385

    set the 32bit option in the to true for the job and run it again.

  • December 6, 2013 at 1:16 am

    #1671393

    I did that. still no success :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 6, 2013 at 3:20 am

    #1671418

    Dear members.. please reply can any one have solution on this 🙁

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 6, 2013 at 3:24 am

    #1671421

    Try to execute the package on the SQLSERVER manually. and if possible check if SSIS is installed on that Machine.

  • December 6, 2013 at 3:26 am

    #1671424

    thanks

  • December 6, 2013 at 3:54 am

    #1671434

    I Did check.. SSIS is installed..

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 6, 2013 at 3:59 am

    #1671437

    what about manually executing the package ?

  • December 6, 2013 at 4:03 am

    #1671441

    I get success only if i run through Visual studio..

    It does not work from sql server.. it fails.. please help..

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 6, 2013 at 4:11 am

    #1671449

    okay, 1st thing 1st how did you checked that if the SSIS is installed on the Server?

    2ndly, create a temporary SSIS package which dump the data from the source to the sql server table see its works or not.

    Note: execution should only be done on that machine where this SQLSERVER is installed.

    Please do and share your results.

  • December 6, 2013 at 5:21 am

    #1671468

    I asked software team, they are able to do it on server but client need this to be execute via SQL job.. which is giving error:w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • December 6, 2013 at 5:24 am

    #1671469

    below error comes in execution

    Message

    Executed as user: TAM\sql.service. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 06:15:53 Error: 2013-12-06 06:16:52.08 Code: 0x00000001 Source: Get Data from Employee Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: OCIEnvCreate failed with return code -1 but error message text was not available. at System.Data.OracleClient.OciHandle..ctor(OciHandle parentHandle, HTYPE handleType, MODE ocimode, HANDLEFLAG handleflags) at System.Data.OracleClient.OciEnvironmentHandle..ctor(MODE environmentMode, Boolean unicode) 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 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ST_de1e5f5fd63b4eb6b9251a2a353d2fbf.csproj.ScriptMain.InsertIEmployeeDataIntoTempDatawarehouse(String strQry) at ST_de1e5f5fd63b4eb6b9251a2a353d2fbf.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 06:15:53 Finished: 06:16:52 Elapsed: 59.015 seconds. The package execution failed. The step failed.

    :w00t::w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy