Q:

Switching context in SSIS data sources

The requirement:
I have an SSIS project that will run on data in SQL Server, possibly logging in to the SQL Server using integrated authentication.
That project includes one section that allows end users to add a select query as a source for one dataflow. (The product allows the user to add data categorization and filtering type rules in configuration files).
I want those queries supplied by the user to be run in a restricted context with read-only access. It must not be possible for someone to trash the database by adding a DELETEor DROPto their query.  
First pass solution:
Create a read-only user on the database and use EXECUTE AS ... WITH NO REVERTto restrict the context of the query to the read-only user. Unfortunately this does not fully work. SSIS uses prepared statements in the data source components, and WITH NO REVERT is not permitted by SQL Server in prepared statements.  
Questions:
Is there a way to force SSIS to use ad-hoc queries in place of prepared statements in the OLEDB data source component?
or ... Is there another way to force SSIS to change execution context for a data source component?  
I have one other solution up my sleeve: Use a script component source. The script component can set the user context in any number of ways.

A:

The final solution I settled on for this is to have a script source component that wraps the user-supplied SELECT with EXECUTE AS user WITH NO REVERT. I'm convinced that using the permissions structure to prevent unwnated action is the better than using transactions.
I created two connection managers. One (called connection_RO) is an ADO.NET connection manager that is only ever used by the component that executes the user-supplied queries. The other (connection_RW) is used for everything else, including transforming the data from the user-supplied queries and writing it back.
There is one script source component that executes the user-supplied query. It prefixes the query with the EXECUTE AS ... clause to lock the user into the read-only context.
The next problem to solve was that all connections are pooled by default, and when you pick up a connection that was used previously in a pool the framework sends a sp_reset_connection. That throws an exception because SQL Server sees it as an attempt to escalate privilege. Therefore the connection_RO connection manager had to be configured with no pooling.
The final result is that I can run the user-supplied queries with confidence that the user can do no damage, either maliciously or by accident.

