April 21, 2008 at 8:07 pm
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.
April 21, 2008 at 9:34 pm
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.
April 23, 2008 at 5:37 pm
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
April 24, 2008 at 11:17 pm
thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply