Order headers and lines

Hi,

I am trying to design a data mart that will server as a data source for an Analysis cube. I would like to know the best practice for handling order headers and order lines. Do I need 2 fact tables in the data mart, and does there need to be a foreign key relationship between them.

The header table will contain information like whether a delivery was made on time, which depot delivered the order, how many times the delivery was attempted, total distribution charge, whereas the line table will contain product related information with quantities ordered / delivered, and the cost per line

Thanks

Depends on the analysis that needs to be supported, both by the data mart and the cube. For example, the FactInternetSales and FactResellerSales tables in the sample Adventure Works DW database are at the line level. But from your description, it sounde like there are relevant measures at both the header and line item level, so 2 fact tables (with 2 mesure groups) may be preferable. The detail table would then have a foreign key for the parent header table.

In terms of dimensions, those which directly relate to the detail measure group can be configured with a "many-many" relation to the header measure group (using the detail measure group and header fact dimension as intermediates). And those dimensions which directly relate to the header measure group can be configured as "referenced" (via the header fact dimension) for the detail measure group.

This article argues for a single detail-level fact table, but the AS 2005 modelling features may mitigate some of these issues:

Managing Your Parents

Be mindful of reporting needs when designing parent and child fact tables

By Ralph Kimball

...

Oralce Distination

Hello Everyone,

I’m doing a type 2 SCD, Flat File as my source and oracle table as my destination but there is a problem in SCD component. I'm getting error.

is SSIS accessible to oracle as destination if yes please do inform me how do i resolve this issue.

Thank you

What is the error message? Please post that as without it we cannot help you.

An Oracle destination using OLE drivers will work just fine -- for the most part.

|||

The Error Message

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Warning at {CF5DCB64-279E-45A4-A9A8-FF2FBB130980} [Insert Destination [1972]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

ADDITIONAL INFORMATION:

Errors were encountered while generating the wizard results:
Error at Data Flow Task [OLE DB Command [1996]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command [1996]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

i had try to resolve the problem thru adding the derive parameter information.

but still i'm gettting an data conversation problem.

my source is a flat file and source column datatype is string but when i added the parameters its taking as unicode datatype can you help me in this.

thank you

Oracle with Reporting Services

I am trying to connect to an Oracle database using Reporting Services.
I create a new Shared Data Source, give it a name, then hit the edit
button. Set the provider to Microsoft OLE DB Provider for Oracle, on
connection tab I enter
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID = ServiceName)))
I enter the username and password, hit the test and everything tests
out ok. I ok out, go to create a new report and when I go to use the
DataSource I get an error of
A connection cannot be made to the database.
Set and test the connection string.
System.Data.OracleClient requires Oracle client software version 8.1.7
or greater.
Any help would be appreciated. Thanks.OK, are you using RS 2000. Here is how it works in RS 2000. When creating
the query it uses OLE DB if you are using the graphical editor (4 panes). If
you use the generic it uses the dotnet provider. The generic is 2 panes
(there is a button to switch to generic to the right of the ...). When
running the report it uses the dotnet provider. The reason for this was that
VS 2003 query designer did not have support for the dotnet provider so they
did this workaround. The dotnet provider requires 8.1.7 client to be
installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jdcamp" <camsam50@.hotmail.com> wrote in message
news:1145550738.077059.160290@.t31g2000cwb.googlegroups.com...
>I am trying to connect to an Oracle database using Reporting Services.
> I create a new Shared Data Source, give it a name, then hit the edit
> button. Set the provider to Microsoft OLE DB Provider for Oracle, on
> connection tab I enter
> (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST => server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID => ServiceName)))
> I enter the username and password, hit the test and everything tests
> out ok. I ok out, go to create a new report and when I go to use the
> DataSource I get an error of
> A connection cannot be made to the database.
> Set and test the connection string.
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater.
> Any help would be appreciated. Thanks.
>|||I ran into this exact same problem yesterday...you need to install the
Oracle Client Tools on the report server. w/o the client tools your
windows box doesn't natively understand how to connect to the oracle
db.
After you install the Oracle Client on the report server you will need
to update your tnsnames.ora and sqlnet.ora files with the correct host
settings. Your Oracle dba's should have these but here's some examples
to get you going in case they dont...
If you use the default install of the client tools you will find these
files in the following location (C:\oracle\ora90\network\ADMIN)
tnsnames.ora example
================================<Server Alias> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.somewhere.com)(PORT =9999))
)
(CONNECT_DATA = (SERVICE_NAME = <ServiceName>)
)
)
sqlnet.ora example
================================SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
Hope this helps!
Cheers
--
Ben Sullins

Oracle View returns ORA-01403: no data found

I am trying to get data from an Oracle view using an OLE DB data source and a "SQL Command". When I "preview" the data it looks fine, but when I execute the package I get the following error:

Error: 0xC0202009 at Data Flow Task, CEDAR View [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CEDAR View" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

hi,

are you totally sure that such destionation owns data?

|||I was using the "Oracle" version of the OLE DB driver. When I switched to the Microsoft OLE DB provider for Oracle it worked (with warnings). I guess not all OLE DB providers will work with SSIS. It's just very strange the way it worked in "preview" mode, but failed during execution

oracle support - Report Model

If my understanding is correct, Oracle is not yet supported as a data source
for report models in SSRS 2005 (and thus not available for report builder).
Is there a timeframe estimate to when this might be available?I would also like to know. Also if there are any APIs to develop one. Can we
develop own application which can use Oracle database to build report model?
Thank you - Ashok
"chicagoclone" wrote:
> If my understanding is correct, Oracle is not yet supported as a data source
> for report models in SSRS 2005 (and thus not available for report builder).
> Is there a timeframe estimate to when this might be available?|||I burned a MSDN Developer Incident on this one. Basically I was able to ceate
a Report Model by using a Linked Server and creating a view on the linked
server. From there I used the Linked View in the report Modeler, it is a wee
it slow (30-45 seconds for 18,000 rows over a T-1) however until they support
Oracle it's how I am going to apporach it. Hope this helps...
"Ashok" wrote:
> I would also like to know. Also if there are any APIs to develop one. Can we
> develop own application which can use Oracle database to build report model?
> Thank you - Ashok
> "chicagoclone" wrote:
> > If my understanding is correct, Oracle is not yet supported as a data source
> > for report models in SSRS 2005 (and thus not available for report builder).
> > Is there a timeframe estimate to when this might be available?|||Oracle support is scheduled to be added in SP2.
"chicagoclone" wrote:
> If my understanding is correct, Oracle is not yet supported as a data source
> for report models in SSRS 2005 (and thus not available for report builder).
> Is there a timeframe estimate to when this might be available?|||Thank you Bob! This is great news.
"Bob Meyers - MSFT" wrote:
> Oracle support is scheduled to be added in SP2.
> "chicagoclone" wrote:
> > If my understanding is correct, Oracle is not yet supported as a data source
> > for report models in SSRS 2005 (and thus not available for report builder).
> > Is there a timeframe estimate to when this might be available?

Oracle Stored Procedure Syntax

I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > Thanks!
>

Oracle Source Connection Error

Hi,

I was trying to connect to Oracle Source in SSIS

1. Err: Test connection failed because of an error in initializing provider. Oracle Client and networking components were not found

Sol: I installeed the Oracle Client tools

2. Err: ORA -12154: TNS: Could not resolve teh connect identifier specified

Sol: Then I point out the TNS file

3. Now I coonect the Oracle db . I go to Connection Manager . Specifies the provide as: Native OLE DB\ Oracle Provide for OLE DB, Select my DB, provide teh user name and password. and check if the connection passed.

Then in my data Flow Task when I add this source and check the Colmns its gives me an error./ sort of warning

The component reported the following warnings:

Warning at {C947B.......} [Ole DB Source[1]]: Cannot retrieve the column code page info from the OLE DB provider. if the components supports teh "DefaultCodePage" property, the code page from that property will be used.....

Will this warning has an significance. i dont see any error in my output data. but is there something I m doing wrong.

4. When I set my ProtectionLevel under Security in package property to "Dont Save Sensitive" in order to easily configure the package I m getting this error: failed Validation. The default one was EncryptSensitiveWithUserKey then its running smoothly.

Can somebody advice me on this?

I addded the all the errors and solution for 1 and 2 becuase in that way this will be helpful in future.

One more thing...

What should be my Package property Protection layer in oreder to smoothly transfer and run the package on other computer.

P.S. I' m using Oracle Source where in I have to give the user name and password in order to connect it.

|||

To use Oracle for development you have to install the correct Oracle client for the version of Oracle you are connecting to and you need a TNS.ORA file with your connection info and permissions in your development folder because your error says your permissions could not be resolved. One more thing if your database is in 9i make sure your client is for 9i and not 10g. Hope this helps.

|||What should be my protection Level in Package in order to make work anywhere on any computer. I am using user name; and password in order to connect to server|||

Microsoft support have covered the protection level and related issues in link one and two covers how to use the Agent to run your packages.

http://support.microsoft.com/kb/918760/

http://support.microsoft.com/kb/912911

Oracle Provider of OLEDB - Unable to process cubes

Hi all,

Our data mart resides in Oracle 9i.

I can connect the data source views fine, and explore the data in those views.

Even in the cubes themselves, I can explore the data.

But when I go to process the cubes, I get the error ...

"Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'RTM Data Mart', Name of 'RTM Data Mart'.

Anyone know why?

The provider is the Native OLE DB provider from Oracle.

Thanks,

--Phil

Check your impersonation settings, it should be 'use the service account'|||

That did the trick - thanks.

Interesting that the Data Source Views are happy enough with a fixed identity, but processing cubes wants the service account.

But I am happy to have made progress - thanks again.

Oracle Problems...

I get:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ODBC'. (rsErrorOpeningConnection) Get Online Help ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified

when trying to view a report that runs in Visual Studio .NET. After I deploy the report to the Reporting Services server I get the error. There must be a problem in the server's configuration, right? What can I do?

I seem to have gotten this to work. I found a post somewhere on the Internet about a guy DELETING the NLS registry key. This key can be found by searching the registry on the server for home0. I tried it once without restarting the server and it didn't work. Then I restored the key and continued to look for more information. After not find anything, I decided to delete the key again and bounce the server. PRESTO!

Oracle parameters

I am creating a report using a query where the data source is an Oracle 8i database.

I'm selecting data that is between 2 dates:

"Select * from table where mydate is between :startdate and :stopdate".

To get all of the data I need to use :stopdate + 1 to add a day for oracle SSRS uses dateadd to get the date that oracle requires. SSRS does not like the (+1) and Oracle does not recognize the dateadd function.

How do I solve this problem?

P.S. This is my first SSRS report.

The bottom of this posting contains a simple report that shows how to do this based on a SQL Server data source.

Overall there are several options:

* you can keep your current query commandtext and perform the calculation in the report parameter as shown in the RS 2005 sample below, where the second report parameter is marked as "hidden" and its value is determined based on the first report parameter using an expression: =CDate(Parameters!StartDate.Value).AddDays(1)

* perform the datetime calculation in the commandtext. However in that case you *must* use the Oracle date functions to perform the calculation (lookup your Oracle SQL documentation)

* write a Oracle stored procedure (e.g. in PL/SQL) and call the stored procedure from Reporting Services. The stored procedure has to return the data through one OUT REF cursor (see also this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1)

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>95e33366-62c0-4eea-87f0-94d2534377bd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
<Prompt>StartDate</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(Parameters!StartDate.Value).AddDays(1)</Value>
</Values>
</DefaultValue>
<Prompt>EndDate</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox2">
<Left>0.25in</Left>
<Top>0.5in</Top>
<ZIndex>1</ZIndex>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!EndDate.Value</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.25in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!StartDate.Value</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>e15b81e3-ab03-4e68-a6b8-0de6d6880ab5</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from orders where OrderDate between @.StartDate and @.EndDate</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
<Field Name="CustomerID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CustomerID</DataField>
</Field>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="RequiredDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>RequiredDate</DataField>
</Field>
<Field Name="ShippedDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>ShippedDate</DataField>
</Field>
<Field Name="ShipVia">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ShipVia</DataField>
</Field>
<Field Name="Freight">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Freight</DataField>
</Field>
<Field Name="ShipName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipName</DataField>
</Field>
<Field Name="ShipAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipAddress</DataField>
</Field>
<Field Name="ShipCity">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCity</DataField>
</Field>
<Field Name="ShipRegion">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipRegion</DataField>
</Field>
<Field Name="ShipPostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipPostalCode</DataField>
</Field>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Robert,

Thanks for your response. Options 1 & 2 are feasible since we can only 'read' from the Oracle database.

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 OLEDB drivers problem with Numbers

Hi All:

I am using oracle oledb drivers to write to a oledb destination.

if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source

Source Target

50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50

I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.

Thanks,

Vipul

There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||

Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.

Is there some problem in SSIS for this or this is oracle oledb driver problem?

|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||

ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.

Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.

The version of software on both mahcine are:

-

SQL Server sp2 9.0.3042

Oracle 10g

Let me know your thoughts on this..

|||Is your destination SQL Server?

Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||

Destination is Oracle.

And i have checked all the datatypes as per ur suggestion but still the problem exists.

|||I'm going to have to bow out as I don't have an Oracle instance to test with.

Oracle Data Source Problem: Cannot connect during deployment. Help!

I am developing an SSAS project which looks at an Oracle database but I cannot for the life of me get it to process.

This is one of those awful Oracle connectivity problems!

Now, in the DataSource, I can edit and the test connection works perfectly. Every other application can connect to the Oracel server correctly. The problem comes when I try to deploy my project. I get errors left right and centre. They even change between attempts! Most common is TNS name resolution error:

Error 1 OLE DB error: OLE DB or ODBC error: ORA-12154: TNS:could not resolve the connect identifier specified. 0 0

Yet, as I have said, when I test the connection everything is OK. And I can use the same connection details in every other application. What is so different about the deployment operation that means it cannot find the connection configuration?

I have tried all the providers, impersonation etc. FYI: the main method for getting the Oracle connection details is through LDAP.

OK.. I found a work around.. I put the connection details directly into the tnsnames.ora file and everything went smoothly again!

So, why does the deployment processing not look at sqlnet.ora when everything else does, including the UI/designer? I am just worried things might get difficult in a production environment.

The other interesting thing is that this has worked from my other machine, so their must be some Oracle config somewhere? Any Oracle connectivity experts out there?

|||

If you running 64 bit be aware of this thread as well --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1

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 9i OLE DB data source connection

help - I’ve been stuck on this for days.

My software vendor limits the sessions to one (1) per log-in to Oracle 9i and I’m having tons of problems trying to extract data through an SSIS package. Everytime I run the package SSIS already logged in and the OLE DB source fails because of the sessions exceeded error.

Is there any way to limit the connection to just when I need the data to pump across on the OLE DB source?

Thank you very much for any help.

Regads,

Nestor

Moving to the "SQL Server Integration Services" forum.