Errors when attempting to use DataReader destination

Hi,
We have an ASP.Net app that consumes data from a SSIS package. I have proved that this works by building a noddy ASP.Net app against it on my local machine.

Unfortunately the same isn't true of our project-wide solution where we get error "Package failed to execute." In our package log I can see that the things that were failing were various property expressions that are in my package. The expressions are all on the SQLStatementSource property of various Exec SQL tasks in my package.

Here's an example of just such an error:
OnError,FR23011958,CT\aeqz,SQL UPDATE StreamHistory,{30BD52FA-8077-4D40-BFA7-248173C34000},{C84EF8C0-4B15-41FC-B205-9FB6B72884AC},26/10/2005 13:46:31,26/10/2005 13:46:31,-1073647612,0x,The result of the expression ""UPDATE dbo.StreamHistory
SET StreamEndDateID = (Year(GETDATE()) * 10000) + (Month(GETDATE()) * 100) + Day(GETDATE())
, StreamEndTimeID = (datepart(hh, GETDATE()) * 100) + datepart(mi, GETDATE())
, StreamEndTMS = GETDATE()
, StatusDetailID = 1
, Duration = DATEDIFF(ss, StreamStartTMS, GETDATE())
WHERE StreamHistoryID = " + TRIM((DT_STR, 4, 1252) @.[Metadata::StreamHistoryID])

" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Hopefully you can see that (in theory) this expression should get evaluated regardless of where the package is executed from. Remember, it is not the SQL query that fails to execute - it is the construction of the SQL statement inside an expression that fails.

So simply, has anyone experienced something similar? i.e. Expressions fail to get evaluated when a package is executed from an ASP.Net app (or elsewhere).

I'm wondering if this is a permissions thing...its damned annoying anyway.

ANY experiences at all of doing something similar would be useful.

Thanks
-Jamie

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?|||

Runying Mao wrote:

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?

Hi Runying,
Well it consumes data via a datareader. I actually wrote that initial post over the space of about an hour. At the beginning of that hour we figured the problem was in the datareader cos of the problems we've had with it lately (which you know all about ), by the end of the hour we'd learnt more about what it was - i.e. problems with expressions. I just forgot to change the thread title that's all.

We've found out more since. We've built a new windows forms app and copied the code from "Running an Existing Package using Visual Basic" in BOL into the app and used it to run our package. It worked fine.

We then copied and pasted that code line for line into a new project in our solution and it didn't work. Same code, same machine, same user credentials...different result. very very strange. So now we're pursuing the assumption that some sort of solution-wide configuration is causing a problem. Hopefully we'll find out more tomorrow but if not....I'l be on here pleading for help!!

-Jamie|||Hi, I'm working with Jamie on this problem and have found a little more about the problem, but am still no nearer a solution!

If I add a new Web site to our solution and copy in the code to call the SSIS package, everything runs correctly.

However, if I then reference one of the other assemblies in our solution the package fails to execute, even though we're not actually calling anything in the referenced assembly.|||Here's what Tim and I eventually found out.

http://blogs.conchango.com/jamiethomson/archive/2005/10/27/2315.aspx

Weird!!

-Jamie