February 16, 2011 at 6:18 pm
We are having a problem running an SSIS package from SQL Agent. The package connects to an Oracle 10 database, runs a view and imports data into a SQL Server table. We just installed Oracle client 11 on the server and that seems to be OK; we can connect through sqlplus and Toad.
When remoting in to the server to run manually it's fine. When attempting to run on a SQL Agent schedule we get the following error:
Date 2/16/2011 4:20:01 PM
Log Job History (RawTARTest)
Step ID 1
Server MTA46DEV
Job Name RawTARTest
Step Name ImportFromOPSP
Duration 00:00:31
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: MTA46DEV\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:20:01 PM Error: 2011-02-16 16:20:31.86 Code: 0xC0047062 Source: Data Flow Task Source - Query [1] Description: System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. at System.Data.OracleClient.OCI.DetermineClientVersion() 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.DbConnectionF... The package execution fa... The step failed.
Sounds like it doesn't know what version of Oracle client we are using. Any help would be appreciated.
February 18, 2011 at 3:37 am
Hi Dale,
i could imagine the cause of your problem is the different user that is running your package. If you do it manually you are connected with a windows user that has rights on the servers, an environment, an system DSN, ... and so on. The user "MTA46DEV\SYSTEM" will have complete different conditions. I don't know where exactly you should change the user, you can run the SQL Agent service with a different user, you can give the job an other owner, you can run each step with his own user, ...
Kind regards
Steffen
February 18, 2011 at 4:39 pm
In googling for info the user issue was the most common problem, and after I left this message I tried changing users\owners to various system or network accounts. At first that didn't seem to help but the next day (yesterday) it began running. I thought I unchanged everything back to original but must have missed something. I haven't had time yet to go back and make a thorough check.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply