December 5, 2013 at 10:56 pm
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
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
when you run it in Visual studio, What is the value you set in "Run64bitRunTime"?
December 6, 2013 at 12:40 am
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
set the 32bit option in the to true for the job and run it again.
December 6, 2013 at 1:16 am
I did that. still no success :w00t:
************************************
Every Dog has a Tail !!!!! :-D
December 6, 2013 at 3:20 am
Dear members.. please reply can any one have solution on this 🙁
************************************
Every Dog has a Tail !!!!! :-D
December 6, 2013 at 3:24 am
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
thanks
December 6, 2013 at 3:54 am
I Did check.. SSIS is installed..
************************************
Every Dog has a Tail !!!!! :-D
December 6, 2013 at 3:59 am
what about manually executing the package ?
December 6, 2013 at 4:03 am
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
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
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
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