Execute SSIS from Asp.net Application

Question

I'm trying to run a simple SSIS package (copies data from an outside folder into a SQL 2005 table). I want to run this package from an Asp.net 2.0 application. Any suggestions?

I have searched many different blogs and websites, but all of these methods lead to failure (usually because of security issues)

dtexec /FILE "name of package" etc.

EXEC master..xp_cmdshell @cmd (supposedly a very bad idea)

sp_start_job

app.LoadPackage(@"\servername\sharename\Package1.dtsx", null)

Thanks in advance for any help you can give me.

Answer 1

Well, there, Jack, what you've listed is a hodge-podge of half-right answers.

There are a lot of ways to start an SSIS package. However, the best way is probably sp_start_job. Now, this implies that you've created a job that will run your SSIS package.

A lot of times, this puppy fails because of credentials issues. Namely, you're trying to run the bloody thing as the SQL Server Agent account (aka LOCAL SYSTEM), which doesn't bode too well for execution abilities. So, you'll need to create a Credential, and then a Proxy for the job to run as. Therefore, what you need to do is thusly:

CREATE CREDENTIAL MyCred WITH IDENTITY 'CORP\MyUser', SECRET = '<PassGoesHere>'
GO
sp_add_proxy @proxy_name='MyProxy', @enabled = 1, @credential = 'MyCred'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_id = 3
GO
sp_add_job @job_name = 'MyJob', @enabled = 1
GO
sp_add_jobstep 
    @job_name = 'MyJob', 
    @step_name = 'Run SSIS Package', 
    @subsystem = 'CMDEXEC', 
    @command = 'dtexec /F C:\Path\To\Package.dtsx', 
    @proxy_name = 'MyProxy'

Meaningful docs:

1. CREATE CREDENTIAL
2. sp_add_proxy
3. sp_grant_proxy_to_subsystem
4. sp_add_job
5. sp_add_jobstep