How can I execute an SSIS package from TSQL without using xp_cmdshell? I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell. I want to do this:-DECLARE @returncode intEXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"' Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)? Thanks

Unfortunately there is no statement, and options such as a CLR procedure are not possible. The only other viable option I know is to use a job and start that (sp_start_job).

Thanks. So how do I make the SSIS package a job?

Go into SQL Server Agent and create a new job there. (Use Management Studio)

hello everyone is there an update to this? I want to be able to call up SSIS package via T-SQL SQL-CLR of SQL2005/SQL2008, via dtexec or equivalent image invoke..... Given that Integration Services component itself is probably written in .NET, why shouldn't SQL-CLR be able to invoke it? Anyone? Can MSFT SQL team chime in? thank you in advance Yuri Budilov Australia

SSIS isn't written in .Net :( I'm not sure why the CLR wouldn't be able to invoke it - I'm not familiar with its limitations. What you can do (for sure) is what Darren answered - create a job, then launch the job via SPs. Talk to me now on

thanks. Using SQL Agent is not an option in my environment due to other 3rd party issues, which has nothing to do with SQL Server. So we really need to run the IS packages directly from T-SQL stored procedures i.e. right now this means using xp_cmdshell which is not very safe, even with command shell proxy account access. What is badly needed here is ability to run SSIS package from T-SQL (i.e. as an ordinary database user, no special SQL or Windows permissions) in a safe and secure way, without having to burst through the command shell. This is the sort of a feature which could even be shipped from Microsoft as part of "adventureworks SQL-CLR samples", I suspect. I recall this feature being asked for back in SQL 2000-DTS days.... Ten years ago. thanks for reading thus far.

Have you tried using the CLR with the SSIS API? I'm no expert in the CLR, so I don't know its limitations, but I'd be interested in knowing if or why you can or can't use the LoadPackage and Execute methods to start a package... Talk to me now on

thanks Todd. Now we are getting somewhere. Reading this forum it looked to me (reading between the lines) as though this approach would *not* work from SQL-CLR - so I wanted to hear from Microsoft SQL Dev team if this *should* work and *would be supported* if it did. If someone with authority says this should work then I would be investing my effort to go that way. Would this require the assembly to me marked as "UNSAFE" or "External"? It seems that this is such a common request that someone somewhere would have written this piece of SQL-CLR by now? Am I missing something obvious here? thanks Yuri

Whoa - don't go taking my words as saying "it should work". I never said that - I just said I know less than you :) I'm assuming that since the CLR is .Net, and you can launch a package from .Net code, you may be able to do so from the CLR. But... I also know that CLR has lots of restrictions on it, and some of those may cause you to be unable to run it from the CLR. One thing I will say - I've never heard of anyone doing this. That's got to tell you something about: Whether it can be done at all... although I think I'd have heard/seen something about how it's not possible. Whether it's a good idea... maybe other routes are less trouble. Talk to me now on

thanks, just found this item dated from 2007 http://blogs.msdn.com/b/michen/archive/2007/08/24/don-t-run-ssis-package-using-sql-clr.aspx Not sure if this is still the same for SQL 2008 or now.... Web service idea may be worth investigating....

It can be done!! We use a third party software supplier who has done just that. However, as a dba, I've had to support it. What a nightmare. Don't do it!

actually, since you resurrected this post - I believe it can now be done without using xp_cmdshell, using T-SQL, but on SQL Server 2012, if I read the new features correctly..... I have not tried it myself but I am sure someone else has by now..... Yuri Budilov