• You need to tell the user the status of a job (sql job name is known) that was kicked off at a known date and time. Unless there's something else special going on, I don't think you need SSIS to get this done. (does your SSIS keep auto-refreshing your application page or something?)

    I think you're better off using sysjobhistory and run_status to check job status...

    The code below (turn it into a USP) will show you job/step details for a named job, run within the last n seconds (where n is set via @WithinTheLastNseconds). You could probably grab the exact time when the user kicked off the job so you could tighten this part up. Call the USP when the user clicks on a "refresh status" button or similar. You may want more or less detail; that's your call.

    I used a variation of this when I had a 30-40 ETL scripts (as separate sql jobs) to run. They had to run without overlapping, so I created a sql job that would run each job-- and at 30 second intervals, check to see if that job had completed before going on to the next.

    DECLARE @jname varchar(200);SET @jname = 'PUT YOUR SQL JOB NAME HERE'-- faked parm

    DECLARE @WithinTheLastNseconds int;SET @WithinTheLastNseconds = 6000-- restrict to only jobs within this many seconds; recommended parm

    DECLARE

    @yyyymmddint int,

    @hhmmssch char(8),

    @hhmmssint int

    SELECT @yyyymmddint = CONVERT(varchar(20),getdate(),112),@hhmmssch = CONVERT(char(8),DATEADD(second,-@WithinTheLastNseconds,getdate()),8)

    SET @hhmmssint = SUBSTRING(@hhmmssch,1,2) + SUBSTRING(@hhmmssch,4,2) + SUBSTRING(@hhmmssch,7,2)

    select sj.name,

    jh.instance_id,

    jh.run_date,

    jh.run_time,

    jh.step_id,

    jh.step_name,

    jh.message,

    jh.run_status,

    CASE jh.run_status

    WHEN 0 THEN 'Failed'

    WHEN 1 THEN 'Succeeded'

    WHEN 2 THEN 'Retry'

    WHEN 3 THEN 'Canceled'

    WHEN 4 THEN 'In progress'

    ELSE '-abnormal-'

    END as run_status_desc

    ,jh.*

    from msdb.dbo.sysjobhistory jh

    inner join msdb.dbo.sysjobs sj ON jh.Job_ID = sj.Job_ID

    where

    jh.run_date = @yyyymmddint

    and jh.run_time >= @hhmmssint

    and sj.name=@jname

    order by

    sj.name,

    jh.instance_id


    Cursors are useful if you don't know SQL