Error using Datareader Destination

Hi guys,
I'm in dire straits here - really hoping someone can help me.

I'm using a datareader destination and consuming it in a C# app. When I fire ExecuteReader on my DTSCommand object to return some data I get the error:

"Could not obtain a datareader object from the specified data flow component"

So we don't have a clue why this isn't working. Although...I'm intrigued by the line about "specified data-flow component". In the C# app you don't actually specify which data-flow component contains the data reader that you want (do you?) so how does it know where to find the datareader? I'm speculating as to whether this is something to do with the error we're getting or not but it'd help to understand anyway.

ANY help at all much appreciated. We're up against it here.

-JamieWell, the CommandText is the NAME of the DataReader Destination in the package, so your app does in fact specify the data flow component...

-Doug
|||The BOL topic on this subject, at least for the 1st Web refresh, is "Loading Data Flow Results into a Client Application." I updated it again just this week with a bit of information on using DtsDataParameter.

Unfortunately the results of attempting to copy and paste a BOL topic are never pretty...but I can try, if you'd like, or simply email you a more recent copy of the SSIS programming hxs file to drop into your BOL folder and give you the latest content (with disclaimer about editing and tech review not completed).

-Doug
|||Hi Doug,
Yeah that's the BOL topic we're using so I fear the problems may be more deep seated than that. We're definately specifying the datareader destination correctly so its not that (I guess by not specifying the data-flow the only requirement is that you don't have 2 datareader destinations with the same name in the same package).

If you could send me an updated version of that doc though that would be fantastic. I think you have my email address don't you?

Very much appreciated.

If anyone out there has some war stories of using the data-flow destination then I'm all ears.

Thanks
Jamie|||Any chance the DataReader is timing out before your app attempts to grab it?

Do you have the hex code or symbolic name (DTS_E_*) for the error that you're seeing?

-Doug
|||

DouglasL wrote:

Any chance the DataReader is timing out before your app attempts to grab it?

Do you have the hex code or symbolic name (DTS_E_*) for the error that you're seeing?

-Doug

I doubt its the timeout cos I set it to 10 seconds (I think) but I'll check it tomorrow.

The error is occurring in the C# app. To all intents and purposes the SSIS package is executing fine so I'm not going to get any DTS_E_* error(s). I'll dig around tomorrow morning though and see what I can find.

-Jamie|||As far as I can determine, the error message that you originally reported is returned ONLY when the DataReader returned to the DtsClient component by the DataReader destination (that is, the instance of System.Data.IDataReader that you expect to contain your data) is null.

At this point, DtsClient has already (1) located the component with the name that you specified in the CommandText and (2) confirmed that it is in fact a DataReader Destination component.

Hope this helps to narrow things down.

-Doug
|||Right,
The package is still failing HOWEVER I have 3 near identical packages which run fine. The calling code is exactly the same, the only difference being the connection string of my DTSConnection object and the name of the datareader.

So, there's obviously something wrong in my package (even though it runs successfully from DTExec) so I delved into the .dtsx file for each package and found the <component>...</component> section for the datareader destination. Apart from the differences in the <inputColumn> elements (cos they're different pipelines) they seem exactly the same so I can't find any problem there.

Hope this makes sense.

So, any further advice on where to search and what to search for?

-Jamie|||Jamie,

I might be shooting in the dark (I don't have access to your code and package).

Here is what I'd like to know:
1. Did that package pass if running in BI development studio? (I'm confused that in the beginning you said it fail but you also said it runs successfully from DTExec.)

2. How do you call ExecuteReader? Something similar to the following?

conn = new DtsConnection() as IDbConnection;

conn.ConnectionString = dtexeCmd; -- '-f C:\pkg.dtsx'

conn.Open();

command = conn.CreateCommand();

command.CommandText = dataReaderName;

/* Get the data reader */

reader = command.ExecuteReader();

Is it possible that you share your package and code?

|||

Runying Mao wrote:

Jamie,

I might be shooting in the dark (I don't have access to your code and package).

Here is what I'd like to know:
1. Did that package pass if running in BI development studio? (I'm confused that in the beginning you said it fail but you also said it runs successfully from DTExec.)

2. How do you call ExecuteReader? Something similar to the following?

conn = new DtsConnection() as IDbConnection;

conn.ConnectionString = dtexeCmd; -- '-f C:\pkg.dtsx'

conn.Open();

command = conn.CreateCommand();

command.CommandText = dataReaderName;

/* Get the data reader */

reader = command.ExecuteReader();

Is it possible that you share your package and code?

Hiya Runying,
Yeah, that's exactly how we do it.

The package runs successfuly in BIDS and from DTExec. The bit that fails is consuming it from elsewhere.

Can you drop me a mail (jamie.thomson[at]conchango.com) and I'll reply with the package and possibly some data (its sensitive data)

-Jamie|||

Jamie Thomson wrote:

Hiya Runying,
Yeah, that's exactly how we do it.

The package runs successfuly in BIDS and from DTExec. The bit that fails is consuming it from elsewhere.

Can you drop me a mail (jamie.thomson[at]conchango.com) and I'll reply with the package and possibly some data (its sensitive data)

-Jamie

Jamie, I've responded you with my email address.|||

Jamie,

I have found out the cause. Data Reader dest sets its property "DataReader" at validation time. But in your package, Data Flow task has "DelayValidation" set to True. This causes "DataReader" to be NULL at execution time.

It appears something we can improve. I will open a bug.

For now, the workaround is to set Data Flow task property "DelayValidation" to False.

Thanks for your package.

|||Sure, I've got the same problem in my package with a DataReader. In fact, I set the DelayValidation to true due to the query constructed at runtime and got a subject error! I'm in a hurry to try the workaround.
Thank you, Runying!

Sergei Vedischev.