Sounds simple and straight forward, right? Well, it can be, depending on what you are doing. For those of you who have experience in this domain, this method does not require you to enable xp_cmdshell. Whew! The approach below uses the SQL Server job agent to dynamically create a job, assign the package as a step, and delete the job when it is done. It also gives the job a unique name, so it can be run concurrently.
CREATE PROCEDURE [dbo].[LaunchFileLoadProcess]
DECLARE @ServerName NVARCHAR(100)
DECLARE @cmd NVARCHAR(4000)
DECLARE @jid UNIQUEIDENTIFIER
DECLARE @jname NVARCHAR(128)
DECLARE @jobName NVARCHAR(128)
-- Create a unique job name
SET @ServerName = CONVERT(sysname, SERVERPROPERTY(N'servername'))
SET @jname = CAST(NEWID() AS CHAR(36))
SET @jobName = @jname
--set the name and location of the ssis package to run. In this case, the name of
--the package is LoadFile and exists in Sql Server
SET @cmd = '"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" '
SET @cmd = @cmd + '/DTS "\MSDB\LoadFile" '
SET @cmd = @cmd + '/SERVER ' + @ServerName + ' '
SET @cmd = @cmd + '/CHECKPOINTING OFF '
-- Specify ssis variable value in the package that represents the location of the input file to load
SET @cmd = @cmd + '/SET "\Package.Variables[User::varInputFile].Value";"' + @FileLocation + '" '
-- Create job
@job_name = @jname,
@enabled = 1,
@category_name = 'MyApp',
--deletes the job when it is done, regardless of whether or not it was successful
@delete_level = 3,
@job_id = @jid OUTPUT
--Add the job to the Sql Server instance
@job_id = @jid,
@server_name = '(local)'
--Add the step to the job that invokes the ssis package
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd
-- Start job
EXEC msdb.dbo.sp_start_job @job_id = @jid
So, the major benefits of calling a package this way are the fact the xp_cmd_shell does not need to be enabled/called, and SSIS does not have to reside on the server of the client application. If you were to try to invoke an SSIS package from a client app using the Dts.Runtime, you would have to install SSIS on the client machine. This is NOT FREE! In earlier versions of SQL Server, you could redistribute the dts runtime, but not anymore with SQL Server 2008.