Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hypo
Starting Member
8 Posts |
 Posted - 2006-04-14 : 11:32:58
|
Hi, I dont know if I should post this question here, or on oracle forums, but here goes ...I made a simple package that performs 2 Excecute SQL Tasks, one task does a select query on a SQL Server DB Table, the other task does a select query on an Oracle DB Table.The package runs fine in BIDS when debugging, but when I deployed the package to a file system and added it to an sql server agent Job, it all went wrong ...I tried the ms oledb dataprovider (ole db) for oracle but also the oracleclient data provider (ado.net) but the package always fails.The error messages in the logfile are these:event OnError computer ComputerName operator NT AUTHORITY\SYSTEM source Execute SQL Task PLSQL sourceid {B29C4FC9-3345-4657-8680-F770B8C2E136} executionid {7312A7EC-2555-4268-BB29-38659507BADD} starttime 14/04/2006 16:40 endtime 14/04/2006 16:40 datacode -1073573396 databytes 0x message Failed to acquire connection "aaa.bbb.oracleClient". Connection may not be configured correctly or you may not have the right permissions on this connection. the OnError logrow for the package itself says almost the same.Now my question is: What do I need to configure to get it all working? I think I need to configure the oracle db no? What should I configure? ... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-14 : 15:17:59
|
| Do you have the oracle client installed on the server?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
hypo
Starting Member
8 Posts |
Posted - 2006-04-16 : 11:25:35
|
| I'll tell you first thing on thuesdayI know we have the client tools installed on one of the servers, where the SSIS and SSRS servers are installed but we deploy the packages to another server. Because we didnt got that setup working, we installed ssis server on the same server we deploy the packages to ... now Im not sure that server has the oracle client tools installed but Ill inform you asap ;) |
|
|
|
hypo
Starting Member
8 Posts |
Posted - 2006-04-18 : 14:04:59
|
| ok you were right, there is no oracle client installed. I did some better error logging and that also told me that there is no oracle client installed on the server where ssis and the packages are. BUT the admin is not planning on installing them :sSo that leaves me with another question ... does anyone ever configured a ssis server on an other server than the server that contains the packages? The only thing that we configured was the ssis xml file called MsDtsSrvr.ini.xml, more information can be found here [url]http://msdn2.microsoft.com/en-us/library/ms137789.aspx[/url] We also followed the tips on these sites [url]http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1[/url] and [url]http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx[/url] but still we were unable to get it to work. The MSDB folder never shows any content even if something got deployed to that server. So what we want to have:Server1 who runs SSRS, SSIS and has oracle client installed.Server2 who runs SQL Server, SQL Server Agent, SSAS, and packages are deployed here.In the config file (of SSIS on Server1) we reference to the MSDB of Server2.What is going wrong? What additional things need to be configured?Some extra info: SP1 isn't installed yet because it is prerelease |
|
|
|
|
|
|
|
|
ssis oracle connection manager configuration help