Monday, December 29, 2008

Calling SSIS Package from Stored Procedure in SQL Server 2008

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]
(
@FileLocation NVARCHAR(500)
)
AS

BEGIN

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
EXEC msdb.dbo.sp_add_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
EXEC msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'

--Add the step to the job that invokes the ssis package
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd

-- Start job
EXEC msdb.dbo.sp_start_job @job_id = @jid

END

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.

2 comments:

  1. Got this:

    Microsoft (R) SQL Server Execute Package Utility
    Version.

    Started: 3:52:38 PM
    Could not load package (DeploymentPath) because of error 0xC00160AA.
    Description: Connect to SSIS Service on machine (ServerName) failed:
    The RPC server is unavailable.
    .
    Source:
    Started: 3:52:38 PM
    Finished: 3:52:41 PM
    Elapsed: 2.605 sec

    Can you email at mrchhr@yahoo.com

    ReplyDelete
  2. This is very cool; however what level of security access does the user need who will be executing this stored procedure?

    Do they only need to be granted execute permission on the LaunchFileLoadProcess stored procedure or are there more to it?

    ReplyDelete