Hi

I'm trying to set up a Reporting Services report which consumes data from a
SSIS package.

I have a really simple package which just one data flow task. This task
"transfers" a few records from an OLEDB connection to a DataReader
Destination.

When I try to consume these data in the Report Designer, Visual Studio gives
me this errormessage: "An error occurred while reading data from the query
result set.
The SSIS IDataReader is past the end of the result set."
I click OK and the error keeps coming back (infinite loop), so I have to
terminate VS2005 "the hard way", of course losing all my changes :-(

Possibly related: when I try to execute the dtsx package (directly in SSIS
designer), the package does not fail, but it doesn't succeed either - the
"building blocks" stay yellow, and don't become green.

What am I doing wrong?
I followed the instructions from BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/a5e88a03-b703-400a-87f5-3cdaa8440028.htm

thanks in advance for any feedback !

Nico

Hello Nico,

The colour yellow indicates to me that something has not finished doing what
it is supposed to be doing. I have seen this happen and the package get
stuck but that was rare and was on earlier pre-RTM builds. Have you had
a look in the Output window of the package to see if there is anything in
there that may be causing this? Have you tried a simpler Data Flow task
to consume? This way you may be able to build up the Data Flow and see where
it stops responding for you? If the package itself does not "Work" then I
can see how things may not be seamless with SSRS.

Allan

Hello Nico,

Does the package ever finish (can you cut the number of rows being sent to
the DR destination to test?) or wait a while to see if it does finish

Allan

Hello Allan,

thanks for your reply.

I have been doing some more research and I am able now to pinpoint the
problem more specifically.

First of all: those "yellow blocks" are just a consequence of my impatience:
after 30 secs the package completes; I guess this has to do with the "read
timeout" property of the datareader.

BUT, I still had the problem of the report designer crashing when trying to
consume the datareader.

What I forgot to mention in my previous post, is that my data flow task is
NOT OleDb Source ---> Datareader Destination; it is actually: OleDb Source
---> Conditional Split ---> Datareader Destination.
I use the Conditional Split to "filter" records. When I remove the split,
everything works fine.

The Conditional Split had one (1) custom output ("rowstokeep"), and the
"default output". I only used the custom output, which went to my Datareader
("DRDest")
Then I added an extra Datareader destination ("DRdefault"), for the default
output of the split operator.

Now, the report designer is no longer crashing when trying to retrieve data.
But I have to wait 30secs until the "query" completes, because the DRdefault
datareader is waiting to be consumed.
So I changed the "read timeout" property of DRdefault to 0. Not a good idea,
since this caused the report designer to crash again (although running the
package standalone is no problem)

Finally, changing the "read timeout" property of DRdefault to 1 milliseconds
did the trick for me

I did a lot of experimentation with the SSIS package, but it always boiled
down to this: if I don't "catch" the output of the default output of the
Conditional Split operator to a datareader destination, the report designer
WILL CRASH.
- I tried adding a second and third "custom output" of the split, without
using it --> no problem
- I redirected failing rows to an (unhandled) error output and generated an
error --> no problem

I just wanted to share my experiences...
It's possible that I see things wrong here, and my problem has a totally
plausible explanation - in that case, just let me know!

thanks,
Nico

"Allan Mitchell" wrote:

Hello Nico,

Thanks for sharing this with us. Why not take a trip over to our Wiki, sign
yourself up and write a page on what you have learnt. it would be really
great for others who do not frequent this group to learn from your expereinces.

wiki.SQLIS.com

Allan

I realize this post is a little old but I am having the exact same issue
except my SSIS package is not having any issues, but my reporting services
report is having the exact same issue as described by Nico.

Let me know if anyone has had this issue recently.

Thank you,