Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle EM for SQL Server 2000?

Hi,
Can anyone suggest a tool that manages SQL server instances - other than SQL
Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
Manager.
I need something that will give me a view of, and manage, 50+ SQL Server
instances.
Cheers,
James G.
http://www.aspfaq.com/show.asp?id=2442
David Portas
SQL Server MVP
|||Try
DBArtisan
u can get a tial version from the DBartisan website
search in google for the toolls
i do not remember the web site.
from
Doller
|||I believe you can also use OEM, just pay and install agent into sql server
PC.
Tell us how it looks if you try it.
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:8POZe.671$TL4.582@.newsfe4-win.ntli.net...
> Hi,
> Can anyone suggest a tool that manages SQL server instances - other than
> SQL
> Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
> Manager.
> I need something that will give me a view of, and manage, 50+ SQL Server
> instances.
> Cheers,
> James G.
>

Oracle EM for SQL Server 2000?

Hi,
Can anyone suggest a tool that manages SQL server instances - other than SQL
Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
Manager.
I need something that will give me a view of, and manage, 50+ SQL Server
instances.
Cheers,
James G.http://www.aspfaq.com/show.asp?id=2442
--
David Portas
SQL Server MVP
--|||Try
DBArtisan
u can get a tial version from the DBartisan website
search in google for the toolls
i do not remember the web site.
from
Doller|||I believe you can also use OEM, just pay and install agent into sql server
PC.
Tell us how it looks if you try it.
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:8POZe.671$TL4.582@.newsfe4-win.ntli.net...
> Hi,
> Can anyone suggest a tool that manages SQL server instances - other than
> SQL
> Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
> Manager.
> I need something that will give me a view of, and manage, 50+ SQL Server
> instances.
> Cheers,
> James G.
>

Oracle EM for SQL Server 2000?

Hi,
Can anyone suggest a tool that manages SQL server instances - other than SQL
Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
Manager.
I need something that will give me a view of, and manage, 50+ SQL Server
instances.
Cheers,
James G.http://www.aspfaq.com/show.asp?id=2442
David Portas
SQL Server MVP
--|||Try
DBArtisan
u can get a tial version from the DBartisan website
search in google for the toolls
i do not remember the web site.
from
Doller|||I believe you can also use OEM, just pay and install agent into sql server
PC.
Tell us how it looks if you try it.
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:8POZe.671$TL4.582@.newsfe4-win.ntli.net...
> Hi,
> Can anyone suggest a tool that manages SQL server instances - other than
> SQL
> Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
> Manager.
> I need something that will give me a view of, and manage, 50+ SQL Server
> instances.
> Cheers,
> James G.
>

Oracle database in enterprise manager SQL 2000

Can I access an Oracle database in enterprise manager SQL 2000? How do I proceed?

YOu will have to create a linked server in SQL Server 2000 using the appropiate provider. THen you can scroll through the metadata of oracle server. Querying the tables is not possible from the GUI. After creating a linked server you will have to use queries in the syntax of

SELECT * FROM LinkedServername.Database.Schema.ObjectName

(four part naming) to access the oracle Database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Oracle data source connection problem using Report Manager

I have an issue when using Report Manager for viewing reports that use a certain Oracle data source. When viewing the reports in Report Designer the reports display without problems. But when trying to display the reports with Report Manager, after deploying the reports to the Report Server, I now get the following error message:

blablabla....ORA-12705: invalid or unknown NLS parameter value specified

For testing purposes I am running Report Designer, Report Server and Report Manager locally on a PC with Windows XP. I am using Reporting Services 2005, IIS 6, Visual Studio 2005, an Oracle 9.x client and an Oracle 9.x (not 100% sure) database that is hosted on a remote server.

Earlier I got an error message that stated "ORA-12154: TNS: could not resolve the connect identifier specified". Access rights to some Oracle folders for the Network service account did the trick here but then I got the "NLS parameter" error message. What I have tried to do is removing the NLS_LANG register variable and also modified the value of the variable so that it matches the value of the NLS_LANG variable on the server that hosts the Oracle database, but none of them have worked. Any ideas?

Maybe this is an issue that is more appropriate in an Oracle forum but what the heck..the problem could reside in Reporting Services..

Thanks, Stefan

I found the solution to the problem. I was jerking around with the wrong NLS_LANG registry variable. Everywhere I have looked it says that the NLS_LANG variable is found in the Home0 directory in the Windows registry, so naturally I have tested settings with that NLS_LANG variable. Also, the first thing I did was to search the registry for the NLS_LANG variable and the only search result was the one found under the Home0 directory. Out of coincidence, I found another NLS_LANG variable located directly under the Oracle directory in the registry. This variable was set to "NA". I changed it to AMERICAN_AMERICA.WE8ISO8859P15, and boom shakalak!

/Stefan

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 9i Rel2 to SQL 2000 linked Server Problem..

Using Microsoft SQL OLE DB to create the linked server, whether using
Enterprise Manager or Analyzer script... The linked server appears to be
created successfully, and I can see the database tables, but I cannot see an
y
of the data in the tables.
Anyone have an idea of whay I might be missing? I appreciate any input.
ThanksYou can't view data in linked server tables from Enterprise
Manager. Use Query Analyzer and a query instead. The easiest
way is to use a four part name to reference the table:
select YourColumns
from LinkedServerName.Database.Owner.TableName
-Sue
On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
<rkutsy@.comcast.net(donotspam)> wrote:

>Using Microsoft SQL OLE DB to create the linked server, whether using
>Enterprise Manager or Analyzer script... The linked server appears to be
>created successfully, and I can see the database tables, but I cannot see a
ny
>of the data in the tables.
>Anyone have an idea of whay I might be missing? I appreciate any input.
>Thanks|||Oops...just noticed the link is to Oracle so use:
LinkedServerName..Schema.TableName
-Sue
On Sun, 27 Mar 2005 20:07:24 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>You can't view data in linked server tables from Enterprise
>Manager. Use Query Analyzer and a query instead. The easiest
>way is to use a four part name to reference the table:
>select YourColumns
>from LinkedServerName.Database.Owner.TableName
>-Sue
>On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
><rkutsy@.comcast.net(donotspam)> wrote:
>|||Thanks, very much, for responding to this post. Your response confirms our
suspicions. We were able to get the data using the four part name reference
to the table.
thank you again!
Bob.
"Sue Hoegemeier" wrote:

> You can't view data in linked server tables from Enterprise
> Manager. Use Query Analyzer and a query instead. The easiest
> way is to use a four part name to reference the table:
> select YourColumns
> from LinkedServerName.Database.Owner.TableName
> -Sue
> On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
> <rkutsy@.comcast.net(donotspam)> wrote:
>
>

Oracle 9i Rel2 to SQL 2000 linked Server Problem..

Using Microsoft SQL OLE DB to create the linked server, whether using
Enterprise Manager or Analyzer script... The linked server appears to be
created successfully, and I can see the database tables, but I cannot see any
of the data in the tables.
Anyone have an idea of whay I might be missing? I appreciate any input.
Thanks
You can't view data in linked server tables from Enterprise
Manager. Use Query Analyzer and a query instead. The easiest
way is to use a four part name to reference the table:
select YourColumns
from LinkedServerName.Database.Owner.TableName
-Sue
On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
<rkutsy@.comcast.net(donotspam)> wrote:

>Using Microsoft SQL OLE DB to create the linked server, whether using
>Enterprise Manager or Analyzer script... The linked server appears to be
>created successfully, and I can see the database tables, but I cannot see any
>of the data in the tables.
>Anyone have an idea of whay I might be missing? I appreciate any input.
>Thanks
|||Oops...just noticed the link is to Oracle so use:
LinkedServerName..Schema.TableName
-Sue
On Sun, 27 Mar 2005 20:07:24 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>You can't view data in linked server tables from Enterprise
>Manager. Use Query Analyzer and a query instead. The easiest
>way is to use a four part name to reference the table:
>select YourColumns
>from LinkedServerName.Database.Owner.TableName
>-Sue
>On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
><rkutsy@.comcast.net(donotspam)> wrote:
|||Thanks, very much, for responding to this post. Your response confirms our
suspicions. We were able to get the data using the four part name reference
to the table.
thank you again!
Bob.
"Sue Hoegemeier" wrote:

> You can't view data in linked server tables from Enterprise
> Manager. Use Query Analyzer and a query instead. The easiest
> way is to use a four part name to reference the table:
> select YourColumns
> from LinkedServerName.Database.Owner.TableName
> -Sue
> On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
> <rkutsy@.comcast.net(donotspam)> wrote:
>
>