SQL Server Job Agent !

Hi Friends,

A small requirement,

I have 2 SSIS Packages and they are executed via stored procedures.
For example consider below 2 stored procedures which dynamically creates a Job and then starts executing the Job.

i,e when i say EXEC spExecDTS_Package1 in SQL Server Management Studio (i.e Query Analyzer),
This stored procedure dynamically creates a job and starts executing the SSIS Package1.
Inside this PAckage1, the last step is an "Execute SQL Task" which will be calling the 2nd stored procedure
responsibele for calling the Package2.

IF u can see the below code, u can clearly understand.

But now my requirement is , i dont want create one more job for calling the 2nd Package. Instead what i need to
do is that, i need to get the jobid of the first package and i need to add one more job step2 to it with the @cmd
written inside the second stored procedure to call the 2nd Package.

Bottom - line, i dont want create a new job instead only add a new job step to existing running job.

How a job step can be added within "Execute SQL Task ".
How would be logic will be implemented.

Any help on this will be greatly appreciated.

Thanks!



create procedure [dbo].[spExecDTS_Package1]
AS
DECLARE @jid uniqueidentifier
declare @cmd varchar(4000)

SET @cmd = '"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /F "D:FilesPackage1.dtsx" '
print @cmd

declare @jname varchar(128)
SET @jname = cast(newid() as char(36))

exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled= 1,
@delete_level= 1,
@job_id = @jid OUTPUT

--insert into trap_jobids select @jid,@jname,'Package1Staging_To_ODS',getdate()

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name= '(local)'

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


go
create procedure [dbo].[spExecDTS_Package2]
AS
declare @jid uniqueidentifier
declare @cmd varchar(4000)

SET @cmd = '"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /F "D:FilesPackage2.dtsx" '
print @cmd

DECLARE @jname varchar(128)
SET @jname = cast(newid() as char(36))

-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled= 1,
@category_name = 'SRM',
@delete_level= 1,
@job_id = @jid OUTPUT

--insert into trap_jobids select @jid,@jname,'Package2',getdate()

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name= '(local)'

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