Run SSIS package from a workstation or through sql

  • I have a SSIS package on the server and I would like our superusers to run this package when they are ready with their daily tasks.

    Users cannot be given access to the server so I need a way for them to run the SSIS package from a workstation (without installing sql tools on the workstation) or call this SSIS package through a sql stored procedure.

    If anyone has any ideas, kindly post. This is for sql 2005.

  • Set up a job to run the package. You can even set the package to run when a value in a tab le is set to a certain number. Allow the users to insert rows in the table, let the job or package check the table for the value and then run.

    Or let them start the job.

  • Hi,

    this is what we use to start a package without having a job:

    create PROCEDURE [dbo].[proc_execute_ssis]

    -- Add the parameters for the stored procedure here

    @date varchar(10) = NULL,

    @path varchar(255) = NULL,

    @filename varchar(250) = NULL

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @cmd_command varchar(2000)

    SET @cmd_command='C:\Progra~2\mi6841~1\90\DTS\Binn\dtexec.exe /SQL \SSISPackageName /SERVER servername /MAXCONCURRENT "100" /CHECKPOINTING OFF'

    IF @date is not NULL SET @cmd_command = @cmd_command + ' /SET \package.variables[overwriteDate].Value;' + @date

    IF @path is not NULL SET @cmd_command = @cmd_command + ' /SET \package.variables[overwritePath].Value;' + @path

    IF @filename is not NULL SET @cmd_command = @cmd_command + ' /SET \package.variables[overwriteFilename].Value;' + @filename

    EXEC xp_cmdshell @cmd_command

    END

    You will need to change the path to dtexec as the dtexec this procedure uses is 32 bit on a 64 bit machine, normally you should use 64 bit if available.

    Best Regards,

    Jan

  • thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply