Asynchronously calling an SSIS package from ASP.Net

The following stored procedure allows you to invoke an Intergration Services package asynchronously and, additionally, send in parameters (variable values or any configurable property). I found it here, and have made some updates to it. In short, it creates a SQL Server job to run the package. Be sure to read the details in the original article.

Changes:

1. I now invoke the package using a job step of type SSIS, rather than calling DTSEXEC. (DTSEXEC was not found in the specified folder on my installation. Presumably this should be more stable).

2. Because of this, the syntax for sending in values to the package has changed.

I am also following Jeff Atwood's modest proposal to assign a guid to the code for future copy-paste reuse.

Enjoy!

-- codesnippet:5852E73F-5CE7-44CC-A5CE-D6420FF16B98

declare @package varchar(128)

set @package = '\File System\MyProject\MyPackage'

-- Initialize command

declare @cmd varchar(4000)

set @cmd = N'/DTS "' + @package + N'" /SERVER "." /CHECKPOINTING OFF /SET "\Package.Variables[User::MyVariable].Properties[Value]";"' + @variablevalue + '" /REPORTING E'

-- ALLOW CONCURRENT DTS EXECUTIONS

declare @jname varchar(128)

set @jname = cast(newid() as char(36))

-- Create job

declare @jid uniqueidentifier

exec msdb.dbo.sp_add_job

@job_name = @jname,

@enabled = 1,

@category_name = '',

@delete_level = 1,

@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver

@job_id = @jid,

@server_name = '(local)'

exec msdb.dbo.sp_add_jobstep

@job_id = @jid,

@step_name = N'Execute Package',

@step_id = 1,

@cmdexec_success_code = 0,

@on_success_action = 1,

@on_success_step_id = 0,

@on_fail_action = 2,

@on_fail_step_id = 0,

@retry_attempts = 0,

@retry_interval = 0,

@os_run_priority = 0,

@subsystem = N'SSIS',

@command = @cmd,

@database_name = N'master',

@flags = 0

-- Start job

exec msdb.dbo.sp_start_job

@job_id = @jid