Saturday, February 25, 2012

Oracle Connection Problems

Hello,
I'm currently in the process of deploying reports transfered from
Crystal Reports to a Reporting Services server. All of the reports
have been deployed with the exception of about a third of the reports
that make use of Oracle. I originally designed the shared data source
to use the Oracle provider rather than the OLE DB provider. However,
when I recreate the shared data source on the Reporting Services
server (with Oracle 8.1.7 installed), attempting to view any of the
Oracle reports gives me the following error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'internal'.
(rsErrorOpeningConnection)
ORA-12640: Authentication adapter initialization failed
Clicking on the online help links generated by the error message gives
me the following information: "Cannot create a connection to data
source". I loaded up SQL Plus on the Reporting Services server to
ensure that a connection could be made to the Oracle server. Sure
enough, I had no problem retrieving data from the Oracle server.
After trying a few of the more simple troubleshooting suggestions
available on the web without success, I switched my provider from
Oracle to OLE DB using the following connection string:
Provider=MSDAORA;Data Source=NameOfTheServer
For two thirds of the reports, this switch works perfectly fine. But
for the other third, the following error is generated when attempting
to view report data:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DataSet1'.
(rsErrorExecutingCommand)
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The number of parameters in the report matches the number of
parameters in the stored procedure. Likewise, the data types used in
the report (mainly String) match the data types used in the stored
procedure (mainly VARCHAR). I've tried recreating the shared data
source on my machine and viewing the same reports that are plagued by
those errors: the same error is generated on my machine. However, if I
go to the data tab of the report on my machine, select the dataset
that causes the problem, hit run, and fill in the same data as I did
for the parameters in the report, the data is returned without a
single problem.
I've searched this newsgroup and the web for a solution without
finding one and, at this point, I'm a bit lost. Does anyone have any
suggestions?
Regards,
BryanRegarding ORA-12640:
* Did you enable tracing and perform some further investigations on the
Oracle side to determine the exact error within the Oracle Authentication
Adapter?
* Did the ORA-12640 error happen for all users trying to execute reports
(i.e. users which are members of the Administrator group on the report
server machine vs. non-admins)? If it only happens for non-admin users, then
it is a file system security permission issue. Just as a test, you might
want to try giving explicit Read&Execute permissions to all files and
subdirectories in the \oracle\ora81 installation directory.
Note: SQL*Plus uses a different mode to connect to the Oracle server and
therefore would most likely not run into the same authentication problem.
Regarding OleDB error when using shared datasources:
* you might want to use two shared datasources in the report project, one is
based on the managed Oracle provider, and one that is based on the OLE DB
provider
* design the report with the shared data source for the managed Oracle
provider
* when everything works, go to the "Data" view, open the data source dialog
and just switch from the Oracle shared data source to the OleDB shared data
source; click OK
* After this step make sure that you do _not_ execute the stored procedure
from the "Data" view or hit the green "Refresh Fields" icon (this will cause
a change in the RDL structure which will later cause the OleDB problem).
* You can still change the layout, run the report in preview and publish it
on the server
Regarding OleDB error when using embedded datasources:
You could do similar steps as above, or if the report is already published
using the managed Oracle provider you would need these steps:
* EDIT the report properties in report manager for the report:
- Go to "Data sources" and replace "Oracle" with "OLE DB"
- Replace the connection string with a valid OLE DB connection string
(e.g. Provider=MSDAORA; Data Source=NameOfTheServer).
* make sure to hit the "APPLY" button on the data source page
You could also manually edit the RDL file and just change the contents of
the <ConnectionProperties> element.
Always make sure the report works fine based on the managed Oracle provider
in report designer, before switching to OleDB. Let me know if the steps
above work for you. We plan to address the underlying issue for the OleDB
error in RS SP2.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryan" <bscriven@.inland.lehighcement.com> wrote in message
news:7b5f5ea2.0407121311.710f2d65@.posting.google.com...
> Hello,
> I'm currently in the process of deploying reports transfered from
> Crystal Reports to a Reporting Services server. All of the reports
> have been deployed with the exception of about a third of the reports
> that make use of Oracle. I originally designed the shared data source
> to use the Oracle provider rather than the OLE DB provider. However,
> when I recreate the shared data source on the Reporting Services
> server (with Oracle 8.1.7 installed), attempting to view any of the
> Oracle reports gives me the following error:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'internal'.
> (rsErrorOpeningConnection)
> ORA-12640: Authentication adapter initialization failed
> Clicking on the online help links generated by the error message gives
> me the following information: "Cannot create a connection to data
> source". I loaded up SQL Plus on the Reporting Services server to
> ensure that a connection could be made to the Oracle server. Sure
> enough, I had no problem retrieving data from the Oracle server.
> After trying a few of the more simple troubleshooting suggestions
> available on the web without success, I switched my provider from
> Oracle to OLE DB using the following connection string:
> Provider=MSDAORA;Data Source=NameOfTheServer
> For two thirds of the reports, this switch works perfectly fine. But
> for the other third, the following error is generated when attempting
> to view report data:
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'DataSet1'.
> (rsErrorExecutingCommand)
> ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
> arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> The number of parameters in the report matches the number of
> parameters in the stored procedure. Likewise, the data types used in
> the report (mainly String) match the data types used in the stored
> procedure (mainly VARCHAR). I've tried recreating the shared data
> source on my machine and viewing the same reports that are plagued by
> those errors: the same error is generated on my machine. However, if I
> go to the data tab of the report on my machine, select the dataset
> that causes the problem, hit run, and fill in the same data as I did
> for the parameters in the report, the data is returned without a
> single problem.
> I've searched this newsgroup and the web for a solution without
> finding one and, at this point, I'm a bit lost. Does anyone have any
> suggestions?
> Regards,
> Bryan|||Hi,
Thanks for the response. I'm just going to reply to each individual
point.
> * Did you enable tracing and perform some further investigations on the
> Oracle side to determine the exact error within the Oracle Authentication
> Adapter?
Not yet. That's something I'd have to pass along to the Oracle person
here - if it comes to that, I'll post the results.
> * Did the ORA-12640 error happen for all users trying to execute reports
> (i.e. users which are members of the Administrator group on the report
> server machine vs. non-admins)? If it only happens for non-admin users, then
> it is a file system security permission issue. Just as a test, you might
> want to try giving explicit Read&Execute permissions to all files and
> subdirectories in the \oracle\ora81 installation directory.
> Note: SQL*Plus uses a different mode to connect to the Oracle server and
> therefore would most likely not run into the same authentication problem.
It happens for all users. Just to be sure, I tried giving everyone
read & execute permissions to the Oracle directory - no luck.
> Regarding OleDB error when using shared datasources:
> * you might want to use two shared datasources in the report project, one is
> based on the managed Oracle provider, and one that is based on the OLE DB
> provider
> * design the report with the shared data source for the managed Oracle
> provider
> * when everything works, go to the "Data" view, open the data source dialog
> and just switch from the Oracle shared data source to the OleDB shared data
> source; click OK
> * After this step make sure that you do _not_ execute the stored procedure
> from the "Data" view or hit the green "Refresh Fields" icon (this will cause
> a change in the RDL structure which will later cause the OleDB problem).
> * You can still change the layout, run the report in preview and publish it
> on the server
I'm not having any luck with that either.
> Always make sure the report works fine based on the managed Oracle provider
> in report designer, before switching to OleDB. Let me know if the steps
> above work for you. We plan to address the underlying issue for the OleDB
> error in RS SP2.
The reports were all designed on my own machine using the Oracle
provider. When I couldn't get the Oracle provider working on the
Reporting Services server, I changed the properties of the shared data
source to use OLE DB.
What's confusing me is that most of the reports work just fine with
OLE DB, parameterized and all. I managed to get one of the Oracle
reports that I was having a problem with (PLS-00306) working. I
changed the two datetime parameters in the report to strings, then I
type cast the parameter values to datetime and then back to string. I
changed the stored procedure parameters to accept varchar2 values and
then type cast the values back to date later in the procedure.
Strangely enough, the report works as intended now. I'll be attempting
the same with the remaining reports when I have time - the stored
procedures used are much more complicated than the one I fixed, so it
could take a while.
Regards,
Bryan|||Thanks for the response. I did reboot the machine after installing the
Oracle client, but I didn't grant Everyone read & execute permissions
on the Oracle folder. I tried it but unfortunately had no luck.
Regards,
Bryan
> 2 things. After you installed the Oracle client on the report server did you reboot? Also, you need to make sure that are permissions on the oracle folder. Make sure that "everyone" has read and execute permission.
> Mark
> "Bryan" wrote:
> > Hello,
> >
> > I'm currently in the process of deploying reports transfered from
> > Crystal Reports to a Reporting Services server. All of the reports
> > have been deployed with the exception of about a third of the reports
> > that make use of Oracle. I originally designed the shared data source
> > to use the Oracle provider rather than the OLE DB provider. However,
> > when I recreate the shared data source on the Reporting Services
> > server (with Oracle 8.1.7 installed), attempting to view any of the
> > Oracle reports gives me the following error:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Cannot create a connection to data source 'internal'.
> > (rsErrorOpeningConnection)
> > ORA-12640: Authentication adapter initialization failed
> >
> > Clicking on the online help links generated by the error message gives
> > me the following information: "Cannot create a connection to data
> > source". I loaded up SQL Plus on the Reporting Services server to
> > ensure that a connection could be made to the Oracle server. Sure
> > enough, I had no problem retrieving data from the Oracle server.
> >
> > After trying a few of the more simple troubleshooting suggestions
> > available on the web without success, I switched my provider from
> > Oracle to OLE DB using the following connection string:
> > Provider=MSDAORA;Data Source=NameOfTheServer
> > For two thirds of the reports, this switch works perfectly fine. But
> > for the other third, the following error is generated when attempting
> > to view report data:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'DataSet1'.
> > (rsErrorExecutingCommand)
> > ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
> > arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> >
> > The number of parameters in the report matches the number of
> > parameters in the stored procedure. Likewise, the data types used in
> > the report (mainly String) match the data types used in the stored
> > procedure (mainly VARCHAR). I've tried recreating the shared data
> > source on my machine and viewing the same reports that are plagued by
> > those errors: the same error is generated on my machine. However, if I
> > go to the data tab of the report on my machine, select the dataset
> > that causes the problem, hit run, and fill in the same data as I did
> > for the parameters in the report, the data is returned without a
> > single problem.
> >
> > I've searched this newsgroup and the web for a solution without
> > finding one and, at this point, I'm a bit lost. Does anyone have any
> > suggestions?
> >
> > Regards,
> >
> > Bryan
> >|||SP1 contains a fix the PLS-00306 issue when using the managed Oracle
provider in report designer. However, it can still happen with the OleDB
provider, when you execute the SP from Data view or refresh fields.
Do you have SP1 installed?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryan" <bscriven@.inland.lehighcement.com> wrote in message
news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> Hi,
> Thanks for the response. I'm just going to reply to each individual
> point.
> > * Did you enable tracing and perform some further investigations on the
> > Oracle side to determine the exact error within the Oracle
Authentication
> > Adapter?
> Not yet. That's something I'd have to pass along to the Oracle person
> here - if it comes to that, I'll post the results.
> > * Did the ORA-12640 error happen for all users trying to execute reports
> > (i.e. users which are members of the Administrator group on the report
> > server machine vs. non-admins)? If it only happens for non-admin users,
then
> > it is a file system security permission issue. Just as a test, you might
> > want to try giving explicit Read&Execute permissions to all files and
> > subdirectories in the \oracle\ora81 installation directory.
> > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > therefore would most likely not run into the same authentication
problem.
> It happens for all users. Just to be sure, I tried giving everyone
> read & execute permissions to the Oracle directory - no luck.
> > Regarding OleDB error when using shared datasources:
> > * you might want to use two shared datasources in the report project,
one is
> > based on the managed Oracle provider, and one that is based on the OLE
DB
> > provider
> > * design the report with the shared data source for the managed Oracle
> > provider
> > * when everything works, go to the "Data" view, open the data source
dialog
> > and just switch from the Oracle shared data source to the OleDB shared
data
> > source; click OK
> > * After this step make sure that you do _not_ execute the stored
procedure
> > from the "Data" view or hit the green "Refresh Fields" icon (this will
cause
> > a change in the RDL structure which will later cause the OleDB problem).
> > * You can still change the layout, run the report in preview and publish
it
> > on the server
> I'm not having any luck with that either.
> > Always make sure the report works fine based on the managed Oracle
provider
> > in report designer, before switching to OleDB. Let me know if the steps
> > above work for you. We plan to address the underlying issue for the
OleDB
> > error in RS SP2.
> The reports were all designed on my own machine using the Oracle
> provider. When I couldn't get the Oracle provider working on the
> Reporting Services server, I changed the properties of the shared data
> source to use OLE DB.
> What's confusing me is that most of the reports work just fine with
> OLE DB, parameterized and all. I managed to get one of the Oracle
> reports that I was having a problem with (PLS-00306) working. I
> changed the two datetime parameters in the report to strings, then I
> type cast the parameter values to datetime and then back to string. I
> changed the stored procedure parameters to accept varchar2 values and
> then type cast the values back to date later in the procedure.
> Strangely enough, the report works as intended now. I'll be attempting
> the same with the remaining reports when I have time - the stored
> procedures used are much more complicated than the one I fixed, so it
> could take a while.
> Regards,
> Bryan|||SP1 is installed.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uUiw0TcaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> SP1 contains a fix the PLS-00306 issue when using the managed Oracle
> provider in report designer. However, it can still happen with the OleDB
> provider, when you execute the SP from Data view or refresh fields.
> Do you have SP1 installed?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bryan" <bscriven@.inland.lehighcement.com> wrote in message
> news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> > Hi,
> >
> > Thanks for the response. I'm just going to reply to each individual
> > point.
> >
> > > * Did you enable tracing and perform some further investigations on the
> > > Oracle side to determine the exact error within the Oracle
> Authentication
> > > Adapter?
> >
> > Not yet. That's something I'd have to pass along to the Oracle person
> > here - if it comes to that, I'll post the results.
> >
> > > * Did the ORA-12640 error happen for all users trying to execute reports
> > > (i.e. users which are members of the Administrator group on the report
> > > server machine vs. non-admins)? If it only happens for non-admin users,
> then
> > > it is a file system security permission issue. Just as a test, you might
> > > want to try giving explicit Read&Execute permissions to all files and
> > > subdirectories in the \oracle\ora81 installation directory.
> > > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > > therefore would most likely not run into the same authentication
> problem.
> >
> > It happens for all users. Just to be sure, I tried giving everyone
> > read & execute permissions to the Oracle directory - no luck.
> >
> > > Regarding OleDB error when using shared datasources:
> > > * you might want to use two shared datasources in the report project,
> one is
> > > based on the managed Oracle provider, and one that is based on the OLE
> DB
> > > provider
> > > * design the report with the shared data source for the managed Oracle
> > > provider
> > > * when everything works, go to the "Data" view, open the data source
> dialog
> > > and just switch from the Oracle shared data source to the OleDB shared
> data
> > > source; click OK
> > > * After this step make sure that you do _not_ execute the stored
> procedure
> > > from the "Data" view or hit the green "Refresh Fields" icon (this will
> cause
> > > a change in the RDL structure which will later cause the OleDB problem).
> > > * You can still change the layout, run the report in preview and publish
> it
> > > on the server
> >
> > I'm not having any luck with that either.
> >
> > > Always make sure the report works fine based on the managed Oracle
> provider
> > > in report designer, before switching to OleDB. Let me know if the steps
> > > above work for you. We plan to address the underlying issue for the
> OleDB
> > > error in RS SP2.
> >
> > The reports were all designed on my own machine using the Oracle
> > provider. When I couldn't get the Oracle provider working on the
> > Reporting Services server, I changed the properties of the shared data
> > source to use OLE DB.
> >
> > What's confusing me is that most of the reports work just fine with
> > OLE DB, parameterized and all. I managed to get one of the Oracle
> > reports that I was having a problem with (PLS-00306) working. I
> > changed the two datetime parameters in the report to strings, then I
> > type cast the parameter values to datetime and then back to string. I
> > changed the stored procedure parameters to accept varchar2 values and
> > then type cast the values back to date later in the procedure.
> > Strangely enough, the report works as intended now. I'll be attempting
> > the same with the remaining reports when I have time - the stored
> > procedures used are much more complicated than the one I fixed, so it
> > could take a while.
> >
> > Regards,
> >
> > Bryan|||I just wanted to leave an update.
I managed to get my remaining Oracle reports working. In all cases,
the PLS-00306 error was caused by the date parameters. I used the
following steps to get the reports working (some of which may not be
necessary - I haven't tested the procedure thoroughly)
1) Some of the stored procedures were old and were not quite written
to Microsoft specs. Being sure to declare which parameters were going
in and which were going out, as well as moving the cursor to the last
in the list, seemed to help.
2) Some of the "date" values were being brought in as varchar and were
being converted to date in the stored procedure. I changed the
datatype on the relevant parameters to date and got rid of the to_date
calls.
3) I left the report parameters as string datatypes.
4) I changed the provider from Oracle to OLE DB and refreshed the
dataset.
5) Since refreshing the dataset erased the parameter list, I entered
all parameters manually. Under the value fields I used the CDate
function to type cast the string start date and end date values to
date values.
Additionally, the Reporting Services server I'm using seems to become
confused and switches mm/dd/yyyy dates to dd/mm/yyyy for ALL of my
Oracle reports. Type casting dates from string to date also seems to
solve that problem.
bscriven@.inland.lehighcement.com (Bryan) wrote in message news:<7b5f5ea2.0407150623.7d7dc0fa@.posting.google.com>...
> SP1 is installed.
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uUiw0TcaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> > SP1 contains a fix the PLS-00306 issue when using the managed Oracle
> > provider in report designer. However, it can still happen with the OleDB
> > provider, when you execute the SP from Data view or refresh fields.
> > Do you have SP1 installed?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Bryan" <bscriven@.inland.lehighcement.com> wrote in message
> > news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> > > Hi,
> > >
> > > Thanks for the response. I'm just going to reply to each individual
> > > point.
> > >
> > > > * Did you enable tracing and perform some further investigations on the
> > > > Oracle side to determine the exact error within the Oracle
> Authentication
> > > > Adapter?
> > >
> > > Not yet. That's something I'd have to pass along to the Oracle person
> > > here - if it comes to that, I'll post the results.
> > >
> > > > * Did the ORA-12640 error happen for all users trying to execute reports
> > > > (i.e. users which are members of the Administrator group on the report
> > > > server machine vs. non-admins)? If it only happens for non-admin users,
> then
> > > > it is a file system security permission issue. Just as a test, you might
> > > > want to try giving explicit Read&Execute permissions to all files and
> > > > subdirectories in the \oracle\ora81 installation directory.
> > > > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > > > therefore would most likely not run into the same authentication
> problem.
> > >
> > > It happens for all users. Just to be sure, I tried giving everyone
> > > read & execute permissions to the Oracle directory - no luck.
> > >
> > > > Regarding OleDB error when using shared datasources:
> > > > * you might want to use two shared datasources in the report project,
> one is
> > > > based on the managed Oracle provider, and one that is based on the OLE
> DB
> > > > provider
> > > > * design the report with the shared data source for the managed Oracle
> > > > provider
> > > > * when everything works, go to the "Data" view, open the data source
> dialog
> > > > and just switch from the Oracle shared data source to the OleDB shared
> data
> > > > source; click OK
> > > > * After this step make sure that you do _not_ execute the stored
> procedure
> > > > from the "Data" view or hit the green "Refresh Fields" icon (this will
> cause
> > > > a change in the RDL structure which will later cause the OleDB problem).
> > > > * You can still change the layout, run the report in preview and publish
> it
> > > > on the server
> > >
> > > I'm not having any luck with that either.
> > >
> > > > Always make sure the report works fine based on the managed Oracle
> provider
> > > > in report designer, before switching to OleDB. Let me know if the steps
> > > > above work for you. We plan to address the underlying issue for the
> OleDB
> > > > error in RS SP2.
> > >
> > > The reports were all designed on my own machine using the Oracle
> > > provider. When I couldn't get the Oracle provider working on the
> > > Reporting Services server, I changed the properties of the shared data
> > > source to use OLE DB.
> > >
> > > What's confusing me is that most of the reports work just fine with
> > > OLE DB, parameterized and all. I managed to get one of the Oracle
> > > reports that I was having a problem with (PLS-00306) working. I
> > > changed the two datetime parameters in the report to strings, then I
> > > type cast the parameter values to datetime and then back to string. I
> > > changed the stored procedure parameters to accept varchar2 values and
> > > then type cast the values back to date later in the procedure.
> > > Strangely enough, the report works as intended now. I'll be attempting
> > > the same with the remaining reports when I have time - the stored
> > > procedures used are much more complicated than the one I fixed, so it
> > > could take a while.
> > >
> > > Regards,
> > >
> > > Bryan

oracle connection manager configuration help

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? ...

Take a look at http://www.oracle.com/technology/tech/windows/odpnet/beta_doc/featADO20.htm

the <configuration> ... </configuration> in the Connection String builder section.

|||owkey the oracle client wasn't installed on the server where the packages were deployed and where ssis server is running. BUT the admin is not planning on installing them :s

So 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 http://msdn2.microsoft.com/en-us/library/ms137789.aspx We also followed the tips on these sites http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1 and http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx 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|||

You need SSIS to be installed on the server upon which the package is executing. You will also need the Oracle provider on that machine. If calling the package from SQL Agent on Server 2 then you need that stuff on Server 2. You obviously have SSIS on Server 2 already to have got as far as you did. If your plan was to off load SSIS work onto Server 1, then it needs to execute there.

Why not install SQL on Server 1, and just use the DB for logging and to support SQL Agent. You have licensed both Servers for full SQL Server, so why not use it. You may not wish to use the DB for more than that, but having a strict rule of no DB engine on a server means more hassle that it is worth. If you use a backup tool, then you probably already have a DB instance in one form or another! DB Instances don;t have to be resource hogs if you treat them right, and don't ask them to do large workloads.

Oracle connection information in SSIS package.

Hi,

I want to make a SSIS package with Oracle and deploy it in no of oracle databases, for it every time I have to open package and change connection information.

How can I make oracle connection information as variable value so that when I deploy my package on Oracle database it will pick all oracle connection information(User Id, Pwd, Server Name) automatically.

Please let me know about this.

Thanks

Hi Anurag,

Experts / MVPs have already addressed this issues about dynamically assigning the Connection details. Follow the steps below

1) Create a table in one of your Oracle database with following fields:

ConnectionDetails(UserID, PWD, ServerName)

2) Insert details of all servers you want to deploy

3) Open the New SSIS Package, Drag and drop an execute sql task, write the query (Select * from ConnectionDetails) to retrieve Conectiondetails into a ResultSet Variable say User::ResultSet of an Object type.

4) Drag and drop the For Each Loop container after the Execute SQL Task and configure details to retrieve each row

5) Store output column values in Package Variables say "v_UserID", "v_passwd" , "v_serverName"

6) Drag and Drop a DataFlow Task into the ForEach Loop Container, determine the source, destination and transformation mappings required.

7) Now assign these variables to connection manager whose detail should change dynamically via expression builder Say

ServerName = @.[User::v_serverName]

UserName=@.[User::v_UserID]

Thanks

Subhash Subramanyam

|||

Hi Subash,

Slew of thanks for speedy reply.

I need a little bit change in first 3 steps, instead of making a table in oracle database, i want to keep oracle server and service name information in Text file and reading from this file.

Thanks

|||

If you want to read from a file, Instead of first two steps you can place a script task that can read the data from the text file to populate a resultset variable which you can use in for each loop.

Monday, February 20, 2012

Oracle connection from a SQL2005 Ent. Edt. X64 cluster

Hi
Does anybody know if there is a problem/issue with retrieving data from
Oracle on a SQL2005 Cluster? My problem is that I don't seems to have
the MSDAORA provider like I have on my other servers.
I don't recall having ever seen this not being present in a SQL
installation, so I'm wondering if this has something to do with the fact
that it's a Cluster install?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> Hi
> Does anybody know if there is a problem/issue with retrieving data from
> Oracle on a SQL2005 Cluster? My problem is that I don't seems to have the
> MSDAORA provider like I have on my other servers.
> I don't recall having ever seen this not being present in a SQL
> installation, so I'm wondering if this has something to do with the fact
> that it's a Cluster install?
>
There is no 64bit version of the MSDAORA provider. You will need the OleDB
Provider from Oracle. Get version 10.2.0.2.
David|||David Browne wrote:
>
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Does anybody know if there is a problem/issue with retrieving data
>> from Oracle on a SQL2005 Cluster? My problem is that I don't seems to
>> have the MSDAORA provider like I have on my other servers.
>> I don't recall having ever seen this not being present in a SQL
>> installation, so I'm wondering if this has something to do with the
>> fact that it's a Cluster install?
> There is no 64bit version of the MSDAORA provider. You will need the
> OleDB Provider from Oracle. Get version 10.2.0.2.
> David
Hi David,
Thanks for the info. I'll try to get it working with the Oracle OLEDB
provider. I've never used that one, so do you by any change have any
links that tells about how to install/configure it?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> David Browne wrote:
>>
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
>> in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Does anybody know if there is a problem/issue with retrieving data from
>> Oracle on a SQL2005 Cluster? My problem is that I don't seems to have
>> the MSDAORA provider like I have on my other servers.
>> I don't recall having ever seen this not being present in a SQL
>> installation, so I'm wondering if this has something to do with the fact
>> that it's a Cluster install?
>>
>> There is no 64bit version of the MSDAORA provider. You will need the
>> OleDB Provider from Oracle. Get version 10.2.0.2.
>> David
> Hi David,
> Thanks for the info. I'll try to get it working with the Oracle OLEDB
> provider. I've never used that one, so do you by any change have any links
> that tells about how to install/configure it?
>
Oracle Provider for OLE DB
http://www.oracle.com/technology/tech/windows/ole_db/index.html
David|||David Browne wrote:
>
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> David Browne wrote:
>>
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk>
>> wrote in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Does anybody know if there is a problem/issue with retrieving data
>> from Oracle on a SQL2005 Cluster? My problem is that I don't seems
>> to have the MSDAORA provider like I have on my other servers.
>> I don't recall having ever seen this not being present in a SQL
>> installation, so I'm wondering if this has something to do with the
>> fact that it's a Cluster install?
>>
>> There is no 64bit version of the MSDAORA provider. You will need the
>> OleDB Provider from Oracle. Get version 10.2.0.2.
>> David
>> Hi David,
>> Thanks for the info. I'll try to get it working with the Oracle OLEDB
>> provider. I've never used that one, so do you by any change have any
>> links that tells about how to install/configure it?
> Oracle Provider for OLE DB
> http://www.oracle.com/technology/tech/windows/ole_db/index.html
> David
>
Thanks David - I'll give it a try.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Oracle connection from a SQL2005 Ent. Edt. X64 cluster

Hi
Does anybody know if there is a problem/issue with retrieving data from
Oracle on a SQL2005 Cluster? My problem is that I don't seems to have
the MSDAORA provider like I have on my other servers.
I don't recall having ever seen this not being present in a SQL
installation, so I'm wondering if this has something to do with the fact
that it's a Cluster install?
Regards
Steen Schlter Persson
Database Administrator / System Administrator""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> Hi
> Does anybody know if there is a problem/issue with retrieving data from
> Oracle on a SQL2005 Cluster? My problem is that I don't seems to have the
> MSDAORA provider like I have on my other servers.
> I don't recall having ever seen this not being present in a SQL
> installation, so I'm wondering if this has something to do with the fact
> that it's a Cluster install?
>
There is no 64bit version of the MSDAORA provider. You will need the OleDB
Provider from Oracle. Get version 10.2.0.2.
David|||David Browne wrote:
>
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> There is no 64bit version of the MSDAORA provider. You will need the
> OleDB Provider from Oracle. Get version 10.2.0.2.
> David
Hi David,
Thanks for the info. I'll try to get it working with the Oracle OLEDB
provider. I've never used that one, so do you by any change have any
links that tells about how to install/configure it?
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> David Browne wrote:
> Hi David,
> Thanks for the info. I'll try to get it working with the Oracle OLEDB
> provider. I've never used that one, so do you by any change have any links
> that tells about how to install/configure it?
>
Oracle Provider for OLE DB
http://www.oracle.com/technology/te...e_db/index.html
David|||David Browne wrote:
>
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> Oracle Provider for OLE DB
> http://www.oracle.com/technology/te...e_db/index.html
> David
>
Thanks David - I'll give it a try.
--
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.