SQL Server Job Agent ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL Server Job Agent !

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-02-15 : 12:14:04

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 Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Files\Package1.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 Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Files\Package2.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

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-15 : 12:22:21

Can we get a little background as to why you are running SSIS packages this way? Will you be executing the packages every time this way? Is there a reason that you don't just create the Job yourself and then start it w/ the stored proc when needed? You will end up with a VERY messy SQL Agent if you implement this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources