dts package - stored procedure

I created a dts package and I can execute it.

I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell.

I have sql integration services and mssql 2005 services running under a domain account.

I have saved the package as a FILE System stored package.

I just can't find a reason why it won't execute from stored procedure.....

Ron

Do you get any errors?

Thanks

|||

Hi,

An alternative would be to use a JOB to fire the package from a stored procedure or a web page.

Something like the example bellow:

Regards,

Philippe

ALTER PROCEDURE [Users].[up_Backlog2Days]

@.Day1 varchar(15)

, @.Day2 varchar(15)

as

begin

-- call the procedure like that from within an excel pivot

-- Exec sm.users.up_Backlog2Days @.Day1 = '14-Dec-2006', @.Day2 = '15-Dec-2006'

set nocount on

Declare @.Cmd as Varchar(500)

Declare @.ReturnCode as int

set @.Cmd = '/DTS "\Deployed Packages\Backlog to compare 2 days" /SERVER "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\package.variables[Day1].Value";"' + @.Day1 + '" /SET "\package.variables[Day2].Value";"'+ @.Day2 + '"'

EXEC msdb.dbo.sp_update_jobstep @.job_name=N'Backlog 2 days compare', @.step_id=1 ,

@.command= @.Cmd

exec msdb.dbo.sp_start_job @.job_name = N'Backlog 2 days compare'

While (SELECT Count(Status) AS Status

FROM OnGlobals.dbo.tb_Isready

WHERE (Name = 'Backlog_2_Days') and Status = 'Ready') != 1

begin

WaitFor Delay '00:01:00'

-- nothing

end

Select * from staging.dbo.tb_backlog_2_Days

End

|||

no, no errors

none that I can find

no errors in the event viewer

no errors in the sql log

it's like it just passes over the part of the procedure with the dts package

Ron

|||Can you post the code you're using to execute the DTS? Does the SQL Account running the SP have permission to remotely/locally run the package?