February 15, 2008 at 10:14 am
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
February 15, 2008 at 3:00 pm
I don't think you can alter a running job. What you can do is just add the second step before starting the job like this (note adding on success and on failure actions to the first step):
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd,
@on_success_action = 3, --goto next step
@on_fail_action = 2 -- quit with failure
declare @cmd2 varchar(4000)
SET @cmd2 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Files\Package2.dtsx" '
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS2',
@subsystem = 'CMDEXEC',
@command = @cmd2
-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid,
@step_name = 'Execute DTS' -- first step
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 16, 2008 at 3:00 am
Thank You Very Much for the help!:)
February 18, 2008 at 5:46 am
By defualt, only members of the sysadmin fixed server role can create CmdExec job steps.
I have given sysadmin server role to my user. And so everything works fine for me.
But assume that i have a normal user with no admin privileges,
still i need to give provision for him to execute "CmdExec" job steps.
So for this,What server roles needed to be granted if i create a brand new user?
Please advice me.
Thanks!
February 19, 2008 at 3:49 am
If you want a non-sysadmin user to run Agent jobs you will need to create a Agent Proxy.
Setting up a Agent Proxy is a bit complex. There are a number of commands to run, and you need to consider the security implications of the Windows and SQL accounts you use in the Proxy.
My best advice is download the SQL Server Finebuild reference manual. This has a step-by-step guide on creating Agent Proxies, and recommendations on how to encapsulate the security for them.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 19, 2008 at 11:28 pm
Thank You so much.
February 29, 2008 at 8:24 am
Hi,
I have 3 job steps which has to run 3 SSIS packages in a sequence. But i dont know why it is skipping the 2nd step and executing the 3rd step.
Can anyone please help me out.
Here is my stored procedure
CREATE PROCEDURE [dbo].[spExecDTSPackage]
as
declare @jid uniqueidentifier
declare @cmd1 varchar(4000)
declare @cmd2 varchar(4000)
declare @cmd3 varchar(4000)
SET @cmd1 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Repository\Package1.dtsx" '
SET @cmd2 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Repository\Package2.dtsx" '
SET @cmd3 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Repository\Package3.dtsx" '
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
exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name= '(local)'
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name= N'Step_1',
@subsystem= N'CMDEXEC',
@proxy_name= N'Proxy1',
@command= @cmd1,
@on_success_action = 3,
@on_fail_action = 2
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = N'Step_2',
@subsystem = N'CMDEXEC',
@proxy_name= N'Proxy1',
@command= @cmd2,
@on_success_action = 3,
@on_fail_action = 2
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Step_3',
@subsystem = 'CMDEXEC',
@proxy_name= 'Proxy1',
@command= @cmd3
-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid,
@step_name= N'Step_1'
February 29, 2008 at 9:02 am
I would venture to say that just because you are creating the steps in that order, SQL Server is not sequencing that way. You can provide the parameter @step_id that set the sequence.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 1, 2008 at 7:43 am
How to specify the step_id?
Can i give the step name ?
March 1, 2008 at 7:49 am
Ever heard of Books on Line. You look there for sp_add_jobstep and it gives you all the parameters that go with and their usage.
The parameter is @step_id and you specify the execution order, 1, 2, 3, etc...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 1, 2008 at 7:49 am
Hi Jack,
Sorry if am bothering you. Basically i am new to this sql server.
What are list of system table from which we can get the job id's?
Thanks in Advance.
March 1, 2008 at 7:54 am
Hi Jack,
Sorry if am bothering too much.
Basically i am new to sql server.
I am from racle background.
Thanks for the help!
I will go the Books online.
March 1, 2008 at 8:00 am
It's not that you are bothering me, but you need to use the resources you have and BOL is a good resource. That should be the 1st place you look whenever you have a problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 1, 2008 at 8:01 am
Jack,
I got it buddy.
Awesome.
Thank you very much!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply