January 8, 2010 at 9:45 am
Hi Specialists.
I am using sql server 2005.
I have a job to run SSIS packages.
I scheduled using Agent.
Sometimes, the job fails if the table at a different server is locked or the communication is disconnected.
I have to execute the job after 15 minutes, if it fails. At this moment, i am doing manually.
I want to make this as part of original job.
How to do it?
Thanks a lot in advance.
Lakshmi
January 8, 2010 at 10:11 am
Hi Lakshmi,
I would have a simple job that runs every 15 mins or so and checks the execution result of the SSIS Package Job and if the last run was failure, start that Job.
OR
In the same Job have step 1 which does the the execution status of the last time and continue to step 2.
Either way, it is not that straight as I say but should not too hard also.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 8, 2010 at 10:24 am
Thanks Medishetty.
yes. I am looking exactly for one of the two solutions you have proposed.
how do i do?
January 8, 2010 at 10:48 am
yes. I could able to reschedule. options are available in job step - advanced.
Thanks for the lead.
January 8, 2010 at 11:02 am
Use the Following script to find your failed job details...
The script displays the records when a particular job fails.. ( You can alternately remove the last line of the code (AND J.[NAME] = 'YOUR JOB NAME') so that it would display all failed jobs...
This is specific to SQL Server 2005 or above..
DECLARE @LastHour DATETIME
SET @LastHour = DATEADD(HH, -1, GETDATE())
DECLARE @LastHourInt VARCHAR(6)
DECLARE @LastHourVarchar VARCHAR(30)
DECLARE @CurrentHourInt VARCHAR(6)
DECLARE @CurrentHourVarchar VARCHAR(30)
SET @LastHourVarchar = CONVERT(VARCHAR(8),@LastHour,108)
SET @LastHourInt = SUBSTRING(@LastHourVarchar,1,2) + SUBSTRING(@LastHourVarchar,4,2) + SUBSTRING(@LastHourVarchar,7,2)
SET @CurrentHourVarchar = CONVERT(VARCHAR(8),GETDATE(),108)
SET @CurrentHourInt = SUBSTRING(@CurrentHourVarchar,1,2) + SUBSTRING(@CurrentHourVarchar,4,2) + SUBSTRING(@CurrentHourVarchar,7,2)
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
DECLARE @CurrentHour DATETIME
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
SELECT J.[NAME] JOB_NAME,JH.RUN_DATE, JH.RUN_TIME,JH.STEP_ID,
JH.[MESSAGE],
JH.[SERVER] SERVER_NAME
FROM MSDB.dbo.sysjobhistory JH
INNER JOIN MSDB.dbo.sysjobs J
ON JH.JOB_ID = J.JOB_ID
INNER JOIN MSDB.dbo.sysjobsteps JS
ON J.JOB_ID = JS.JOB_ID
INNER JOIN MSDB.dbo.sysjobservers JSVRS
ON JH.JOB_ID = JSVRS.JOB_ID
WHERE JH.RUN_STATUS = 0
AND JSVRS.LAST_RUN_OUTCOME = 0
AND JH.RUN_DATE > @FinalDate
AND JH.RUN_DATE = JSVRS.LAST_RUN_DATE
AND JH.RUN_TIME > @LastHourInt
AND J.[NAME] = 'YOUR JOB NAME'
The Following code will start a Job, you can use this if you are following 1st suggestion..
EXEC msdb.dbo.sp_start_job N'YOUR SSIS PACKAGE JOB' ;
GO
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply