Megosztás a következőn keresztül:

Don't run SSIS package using SQL/CLR

  • Cikk

A recent commenter suggested running SSIS using SQL/CLR:

Just an idea on how to do this that may be a bit easier than any of the methods covered.  IF you were to write a CLR procedure which accepts a string as its parameter.  The string passed in would be the xml definition of a package (either loaded from a source (file, server, etc)or from an application which is able to create the package definition).  Then using the CLR procecure that I mentioned above (which has references to the DTS runtime), you could use the Microsoft.SqlServer.Dts.Runtime.Package LoadFromXML() method to flesh out an empty package and then the Execute() method to actually run it.

This may look like a good idea, but don't do this. The reason is that SQL Server has very strict requirements on the type of code running inside (this is how it achieves the great reliability, and why by default it only allows "safe" .NET code and only selected system assemblies). It takes a lot of effort to "harden" code to satisfy these requirements. See e.g. this excellent article by Joe Duffy describing one of the issues: https://www.bluebytesoftware.com/blog/2007/08/23/ThreadInterruptsAreAlmostAsEvilAsThreadAborts.aspx. Only a subset of .NET framework has been "hardened" to qualify.

The SSIS uses "unsafe" .NET code, and we've not done enough testing of SSIS and dependencies inside SQL Server to recommend running it this way, so it is unsupported.

You'd be safer if you write a web service application that does it, and the extra benefit - SQL Books Online has sample code, or use any method described in that older post: https://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

P.S. I was asked whether this recomendation has changed for SSIS 2008 and R2. I am no longer a member of SSIS team, so don't consider this an official advice, but my recommendation is still - do not do it. It might be a bit safer to do it now compared to what it used to be 3 years ago, but since the core architecture of SSIS remains the same I would still avoid it. After all SSIS is designed and tested for doing ETL outside of SQL Server. Inside SQL you can just run SQL scripts, SSIS shines outside integrating multiple data sources.