| Author |
Topic |
|
AgaK
Yak Posting Veteran
86 Posts |
 Posted - 2011-01-07 : 18:46:32
|
| HiI have 2 servers: Server A: SQL Engine with SQL Agent Server B: SSISI am trying to schedule a SSIS package that is on Server B from Server A ( SQL Agent Job). The SSIS package connects to an Oracle server. I am getting the following error message:Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2011-01-07 15:30:56.12 Code: 0xC00291EC Source: Set Run Status Execute SQL Task Description: Failed to acquire connection "EDW". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:55 PM Finished: 3:30:56 PM Elapsed: 0.951 seconds. The package execution failed. The step failed.Do I need to install the Oracle client tools on server B as well?Thank you kindlyAK |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-07 : 19:19:43
|
The error is pretty clear, the package can't establish the connection to Oralce.1. Double check the security level of the package, and that --if password protected--the password is stored in the package or that the user id (Server agent service account) has priveleges on the oracle server (if set to user protection).
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2011-01-07 : 19:47:32
|
| Thank you for your quick reply.The job runs fine when schedule on Server B. The sql agent runs under the same account on both servers. Eventually we want to have the SQL engine on one server only. Any ideas? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-07 : 22:46:25
|
Are the servers linked? YOu can execute from server A using exec [serverB].msdb.dbo.sp_start_job 'job on server B name'
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2011-01-10 : 13:30:40
|
| The servers aren’t linked. Eventually I won’t have SQL Engine and SQL Agent on the SSIS server (server B) so I would like to schedule with execution of SSIS from another server (server A). In that scenario, do I need oracle client on server A? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-10 : 14:08:27
|
| Yes |
|
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2011-01-10 : 18:17:06
|
| Just to confirm. In the following scenario:Server A: SQL Engine with SQL AgentServer B: SSISWe need the oracle client on Server A and B or only on Server A? Will the SSIS execute on server A or B?Thank you again! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-10 : 19:18:38
|
| You need it on the server that will execute the package -- in your case, server A |
|
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2011-01-12 : 15:55:27
|
| thank you kindly, that fixed the problem. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-12 : 15:56:19
|
| You're welcome. Glad you got it worked out! |
|
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2011-01-13 : 14:45:42
|
| It works but it take forever. Is there a way to specify that the package is executed on Server B (only scheduled from Server A). If not I will either have to install SQL Engine on server B or schedule the package with windows scheduler on Server B. Any other ideas?thanks again! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 14:50:06
|
In that case, you may want to run it as a windows task.Don't think I'd pony up for an MSSQL license just to run one SSIS package  |
|
|
|
|
scheduling a SSIS package from another server