I'm using a VS SQL Server project to create a stored procedure. In that
stored procedure, I want to load an SSIS package, set some of the package
variables, and execute it. From the SSIS reference docs, that looks
straightforward.
To be able to access the Microsoft.SqlServer.Dts.Runtime namespace, I need
to add a reference to the Microsoft.SqlServer.ManagedDTS.dll assembly.
However, when I use the normal "Add Reference" mechanism, only a fixed set
of assemblies are displayed (plus a way to select project assemblies). The
"Browse" capability generally there in other types of projects isn't
available. I've seen some docs to the effect that the hosted CLR purposely
restricts the available framework assemblies.
So the short question is, how do I achieve what I'm trying to do? DTS
packages were available from T-SQL stored procs in SQL Server 2000. I've got
to believe this is possible in SQL Server 2005.
On a more lengthy note, I spent a couple of hours trying to figure out how
to add the Microsoft.SqlServer.ManagedDTS.dll assembly to the database where
I want to deploy my stored proc. The set of permissions in a deployed
assembly includes one called "References" which on the surface implied that
it might give me what I want.
Unfortunately, that ended in failure due to the EXTERNAL ACCESS / UNSAFE
assembly issues. I've spent quite a bit of time trying to figure out the
precise, secure steps to correctly: a) prepare the assembly to be deployed
as either external access or unsafe; b) configure a login / db owner with
the appropriate external access or unsafe permissions to deploy such
assemblies; c) mark a database as trustworthy.
The doc at
http://download.microsoft.com/download/c/c/6/cc66b572-a402-4c6a-8233-b9c7d05840c7/ReadmeSQLEXP2005.htm
seems to come closest to specifying what to do, but it's not complete. Does
the "CREATE ASYMMETRIC KEY" command change the dll that's specified? I don't
want to screw with the Microsoft.SqlServer.ManagedDTS.dll that Microsoft
ships. After I create the login with the key and grant it EXTERNAL ACCESS
ASSEMBLY permissions, what do I do with it and the assembly via the CREATE
ASSEMBLY command? The errors displayed when create assembly fails due to
external access issues mention the database owner, not some login created
with the asymmetric key. Beyond that, if I do succeed in adding that
assembly to my database, will that even make it available to reference in
the VS project?
Sorry for being long-winded. I've got to believe that MS knows people want
to do what I'm trying to do. I just wish the hoops I'm forced to jump
through weren't so convoluted and poorly documented.
Thanks,
Donnie
P.S. If cross-posting to these groups is considered bad form, please let me
know.
|