One more question regarding Reporting action

All the reporting action samples I read from AdventureWorks, when pass link and parameter to the report in reporting service, is linked to the report as Analysis Service data source based report.

I am wondering if reporting action can pass parameter to regular relational db based reporting service report not Analysis Service data source based report.

If yes, could you share a simple sample?

I can't see why the data source of a report would matter, when using a reporting action. The Adventure Works examples may all be using an Analysis Services data source just for convenience. Have you tried editing an existing Adventure Works reporting action, to point to a relational report instead?|||

Hi Deepak, thank you so much for your reply! Yes, I did try the following using AW AS and AW sample reports RS projects:

Here is my reporting action:

Name: Sales Order Detail

Target Type: Dimension members

Target object: Internet Sales Order Detail

ReportServer:localhost

ReportPath:ReportServer?/AdventureWorks Sample Reports/Sales Order Detail

Parameters NamealesOrderNumber (Match the one at Sales Order Detail report

Parameter Value: [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Name

And I make sure Sales Order Detail Report is running correctly and parameter is matching the parameter name I defined at the cube reporting action. Then I deploy the cube, however when drop the Sale Order Number dimension and right click, there is no Reporting Action showing up.

Could you give me some pointers?

Thanks

|||

Did you configure the "Caption", under "Additional Properties" - if not, you can refer to the Adventure Works "Sales Reason Comparisons" action, whose Caption is defined as an MDX expression:

"Sales Reason Comparisons for " +

[Product].[Category].CurrentMember.Member_Caption + "..."

|||

Thanks again. Just did that and deployed.

"Sales Reason Comparisons for " + [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Member_Caption + "..."

However reporting action is still not showing. Deepak, anything else I miss?

Thanks

|||Can't think of anything else - are you able to see the standard Reporting action which comes with Adventure Works, ie. "Sales Reason Comparisons"? If so, I would suggest making a test copy of Adventure Works, and progressively modifying this action to incorporate your report in small steps, till it breaks - or maybe it doesn't break!

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lie
Depends on the oad on the reporting server. I would install it on th same
machine with gernerating the cubes in a time where Reports are not often
queried. If the workloads gonna to heavy you even have the possibility to
scale it out.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie
|||Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
>
Whether you use 2 dbs for reporting or one depends on the reporting needs...
The one database for Reporting Purposes , when it is independent like you
have it might be called an ODS (Operational Data Store) . ODS look very much
like their OLTP counterparts, offload OLTP reporting from the OLTP database,
and are a location to aggregate OLTP information from across the many
locations in the enterprise to a single global report location. The data in
these databases is generally for a shorter specific period ( like quarterly
for instance.)
The OLAP database is intended for aggregated reporting, across longer
historical periods. The schema will have been changed to reflect OLAP
sensibilities as well..
hope this helps.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lie
if you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...
if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.
so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose
but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.
also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie
|||Here my considerations
1. Reporting Database:
- To provide operational reports with 10 minutes delay.
- Not Disturbing OLTP Server
- Incrase response time for users who access reports (estimation
max 20 users in the same time)
2. OLAP Database:
- To generate Cubes
- To boost the Cubes generation performance since it won't
affected by users who access reports.
- For analytical purpose
And both of them are not real-time.
Please explain further about garbage in garbage out?
So what do you think of my considerations?
Thanks a lot
Robert Lie
Jj wrote:
> if you want to provide reports based on a copy of your OLTP database, so you
> can create a "read-only" database optimized for reporting purpose.
> you can provide (near)real time reports.
> this usage as an advantage: you don't impat you operationnal server by
> executing complex queries. but.. garbage in garbage out...
> if you want to have a common and cleansed database for both reporting and
> analysis to insure that you'll allways display the same information, use the
> same database for RS + OLAP. but, generally, this database in not real-time
> updated.
> so what is your primary usage for your reports?
> - operationnal purpose
> - analytical purpose
> but you can also provide reports based on the 2 databases regarding the
> performance of your queries.
> if you have some performance problems with your OLTP database (the copy on
> the server B) then the OLAP database and cubes will improove the response
> time.
> also, look at the number of users... 100 users on a 10seconds report vs 100
> users on a 1second report...
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
|||creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)
generating cubes will not lock your database if you do incremental updates
on a optimized cube. (or just a litlle lock)
Partitions will help you for the cube process step.
But your design appear to be good.
garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.
if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here my considerations
> 1. Reporting Database:
> - To provide operational reports with 10 minutes delay.
> - Not Disturbing OLTP Server
> - Incrase response time for users who access reports (estimation
> max 20 users in the same time)
> 2. OLAP Database:
> - To generate Cubes
> - To boost the Cubes generation performance since it won't affected by
> users who access reports.
> - For analytical purpose
> And both of them are not real-time.
> Please explain further about garbage in garbage out?
> So what do you think of my considerations?
> Thanks a lot
> Robert Lie
>
> Jj wrote:
|||Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think?
Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.
Thanks
Jj wrote:
> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
> (how do you plan to synchronize your databases?)
> generating cubes will not lock your database if you do incremental updates
> on a optimized cube. (or just a litlle lock)
> Partitions will help you for the cube process step.
> But your design appear to be good.
> garbage in garbage out = bad data quality in the source = bad quality in
> output (non existant clientID, duplicated product name, duplicated
> records...). so an ETL tool can improove the data quality during the
> process. or if you want to synchronize multiple sources (like reference
> tables and operationnal tables)
> But these steps reduce the loading time by adding data cleansing time.
> if you think next step (which is SQL 2005) you'll have a lot of new features
> to help you.
> for example, you will be able to load directly a partition of your cube
> while you'll load your database, so 1 read and 2 destinations
> (and there is a lot of options)
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...
>
sql

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert LieDepends on the oad on the reporting server. I would install it on th same
machine with gernerating the cubes in a time where Reports are not often
queried. If the workloads gonna to heavy you even have the possibility to
scale it out.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
>
Whether you use 2 dbs for reporting or one depends on the reporting needs...
The one database for Reporting Purposes , when it is independent like you
have it might be called an ODS (Operational Data Store) . ODS look very much
like their OLTP counterparts, offload OLTP reporting from the OLTP database,
and are a location to aggregate OLTP information from across the many
locations in the enterprise to a single global report location. The data in
these databases is generally for a shorter specific period ( like quarterly
for instance.)
The OLAP database is intended for aggregated reporting, across longer
historical periods. The schema will have been changed to reflect OLAP
sensibilities as well..
hope this helps.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert LieDepends on the oad on the reporting server. I would install it on th same
machine with gernerating the cubes in a time where Reports are not often
queried. If the workloads gonna to heavy you even have the possibility to
scale it out.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
>
Whether you use 2 dbs for reporting or one depends on the reporting needs...
The one database for Reporting Purposes , when it is independent like you
have it might be called an ODS (Operational Data Store) . ODS look very much
like their OLTP counterparts, offload OLTP reporting from the OLTP database,
and are a location to aggregate OLTP information from across the many
locations in the enterprise to a single global report location. The data in
these databases is generally for a shorter specific period ( like quarterly
for instance.)
The OLAP database is intended for aggregated reporting, across longer
historical periods. The schema will have been changed to reflect OLAP
sensibilities as well..
hope this helps.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lieif you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...
if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.
so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose
but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.
also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Here my considerations
1. Reporting Database:
- To provide operational reports with 10 minutes delay.
- Not Disturbing OLTP Server
- Incrase response time for users who access reports (estimation
max 20 users in the same time)
2. OLAP Database:
- To generate Cubes
- To boost the Cubes generation performance since it won't
affected by users who access reports.
- For analytical purpose
And both of them are not real-time.
Please explain further about garbage in garbage out?
So what do you think of my considerations?
Thanks a lot
Robert Lie
Jj wrote:
> if you want to provide reports based on a copy of your OLTP database, so y
ou
> can create a "read-only" database optimized for reporting purpose.
> you can provide (near)real time reports.
> this usage as an advantage: you don't impat you operationnal server by
> executing complex queries. but.. garbage in garbage out...
> if you want to have a common and cleansed database for both reporting and
> analysis to insure that you'll allways display the same information, use t
he
> same database for RS + OLAP. but, generally, this database in not real-tim
e
> updated.
> so what is your primary usage for your reports?
> - operationnal purpose
> - analytical purpose
> but you can also provide reports based on the 2 databases regarding the
> performance of your queries.
> if you have some performance problems with your OLTP database (the copy on
> the server B) then the OLAP database and cubes will improove the response
> time.
> also, look at the number of users... 100 users on a 10seconds report vs 10
0
> users on a 1second report...
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
>|||creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)
generating cubes will not lock your database if you do incremental updates
on a optimized cube. (or just a litlle lock)
Partitions will help you for the cube process step.
But your design appear to be good.
garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.
if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here my considerations
> 1. Reporting Database:
> - To provide operational reports with 10 minutes delay.
> - Not Disturbing OLTP Server
> - Incrase response time for users who access reports (estimation
> max 20 users in the same time)
> 2. OLAP Database:
> - To generate Cubes
> - To boost the Cubes generation performance since it won't affected by
> users who access reports.
> - For analytical purpose
> And both of them are not real-time.
> Please explain further about garbage in garbage out?
> So what do you think of my considerations?
> Thanks a lot
> Robert Lie
>
> Jj wrote:|||Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think?
Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.
Thanks
Jj wrote:
> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
> (how do you plan to synchronize your databases?)
> generating cubes will not lock your database if you do incremental updates
> on a optimized cube. (or just a litlle lock)
> Partitions will help you for the cube process step.
> But your design appear to be good.
> garbage in garbage out = bad data quality in the source = bad quality in
> output (non existant clientID, duplicated product name, duplicated
> records...). so an ETL tool can improove the data quality during the
> process. or if you want to synchronize multiple sources (like reference
> tables and operationnal tables)
> But these steps reduce the loading time by adding data cleansing time.
> if you think next step (which is SQL 2005) you'll have a lot of new featur
es
> to help you.
> for example, you will be able to load directly a partition of your cube
> while you'll load your database, so 1 read and 2 destinations
> (and there is a lot of options)
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...
>
>

OLE DB Command Stage: Capturing Rejects

Hello group, I have a question regarding the OLE DB Command Stage. Currently, I am reviewing a Data Flow that runs in production. This Data Flow Inserts to the various dimension tables in our warehouse. For a particular dimension table, the flow is like this:

Read Source records for Product combinations LookUp Product combinations against the current dimProduct table (cached in memory) Rows not found are then subjected to another LookUp on the dimProduct table (not cached). This is to find any rows inserted during the current run Rows not found are then Inserted to dimProduct using a Stored Procedure invoked by an OLE DB Command Successful Inserts then continue on, Rejected Inserts should be captured to a Flat File on our server for review.

Apparently, this last step has never been successful at capturing Rejects. Obviously, we would want to review these records to find the reason for failure. We get an empty file.

Currently, in the Stored Procedure we are using logic like this:

IF @.PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END

Questions:

Is the RAISERROR command going to give us Output? Can we implement the OUTPUT command in our Proc invocation? I have not found any documentation that says the OLE DB Command Stage supports Error logging (Although columns are available to be added in the Input/Output columns tab?) Should we be using another Stage to accomplish this?

Any thoughts are welcome, thanks for your time!

rg

IF @.PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END

In my experience, the error disposition on the OLE DB Command does not work. At least I haven't been able to get it to work. It will ignore RAISERROR statements, yet fail the component on a divide by zero, but never redirect a row. Even if the error redirection did work, you wouldn't be able to get the error description you're trying to raise.

Happily, output parameters DO work (which still surprises me since it isn't documented and isn't really intuitive). I recommend you use an output parameter for the error description, assign it to a column in the data flow, and put a Conditional Split right after the OLE DB Command evaluating the column to roll your own error redirection.

|||

Jay, thanks for the tip. The research was leading me in the direction that the error disposition was less than robust. Thanks for the confirmation, I will pursue using OUTPUT parameters for this data flow.

Thanks for the help! I have much more experience with a different ETL toolset, so even the little things right now are a challenge.