none
How to verify that the SSIS package ran successfully after running a VBA macro from Excel? RRS feed

  • Question

  • I have an Excel VBA macro which calls a stored procedure. The stored procedure calls a SQL agent, which in turn calls an SSIS package. All I need from the Excel VBA code is to ensure that the SSIS package ran successfully. How do you develop a VBA code to let the user know that the job either failed or succeeded after the user executes the Excel macro? Also, is there a need for a recordset in the VBA code?


    Monday, January 6, 2014 10:14 PM

All replies

  • You can create a stored proc similar like below and pass the agent job name as parameter then the last run outcome can be returned.

    select

    ( CASE 
               WHEN last_run_outcome = 0 THEN 'Failed' 
               WHEN last_run_outcome = 1 THEN 'Succeeded' 
               WHEN last_run_outcome = 3 THEN 'Canceled' 
               WHEN last_run_outcome = 5 THEN 'Unknown' 
             END ) AS last_run_outcome

    FROM   openquery 
    (
    localserver,
    'EXEC MSDB.DBO.sp_HELP_JOB'

    )

    where name=isnull(@job_name,name)


    ps. this openquery won't work in sql server 2012. Need to add with result set clause
    • Edited by rluo Wednesday, January 15, 2014 4:57 AM
    Wednesday, January 15, 2014 4:55 AM