Home > SQL server DBA administration > To extract these details from the server for all the jobs which are associated with the SSIS packages. So we need to extract the extract the

To extract these details from the server for all the jobs which are associated with the SSIS packages. So we need to extract the extract the


Topic : To extract these details from the server for all the jobs which  are associated with the SSIS packages. So we need to extract the extract the error message from package not from job.

 

It is true whenever we have to capture the SSIS package job failure history then job history is not help out . One of primary methods that assist with their resolution involves generating logs, which contain records of events taking place during code execution. All records event entries in the sysssislog table of msdb database (sysdtslog90 used in SQL Server 2005). SQL Server 2008 Integration Services offers a fairly large number of built-in event types. OnError – triggered by a runtime error of a current executable. The corresponding event handler is commonly utilized in order to record data documenting circumstances of the failure, but it is not intended to terminate package execution (this can be accomplished, if desired, by leveraging On Failure precedence constraint or by forcing handler failure with FailPackageOnFailure property set to TRUE).

Regarding job information the below query help to get the exact details, sorce:http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

SQL Server Agent Job Execution Information

SQL Server Agent stores the history of job execution in system tables in msdb database.The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

 

 

SELECT

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    , CASE

        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL

        ELSE CAST(

                CAST([sJOBH].[run_date] AS CHAR(8))

                + ‘ ‘

                + STUFF(

                    STUFF(RIGHT(‘000000’ + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)

                        , 3, 0, ‘:’)

                    , 6, 0, ‘:’)

                AS DATETIME)

      END AS [LastRunDateTime]

    , CASE [sJOBH].[run_status]

        WHEN 0 THEN ‘Failed’

        WHEN 1 THEN ‘Succeeded’

        WHEN 2 THEN ‘Retry’

        WHEN 3 THEN ‘Canceled’

        WHEN 4 THEN ‘Running’ — In Progress

      END AS [LastRunStatus]

    , STUFF(

            STUFF(RIGHT(‘000000’ + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)

                , 3, 0, ‘:’)

            , 6, 0, ‘:’)

        AS [LastRunDuration (HH:MM:SS)]

    , [sJOBH].[message] AS [LastRunStatusMessage]

    , CASE [sJOBSCH].[NextRunDate]

        WHEN 0 THEN NULL

        ELSE CAST(

                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))

                + ‘ ‘

                + STUFF(

                    STUFF(RIGHT(‘000000’ + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)

                        , 3, 0, ‘:’)

                    , 6, 0, ‘:’)

                AS DATETIME)

      END AS [NextRunDateTime]

FROM

    [msdb].[dbo].[sysjobs] AS [sJOB]

    LEFT JOIN (

                SELECT

                    [job_id]

                    , MIN([next_run_date]) AS [NextRunDate]

                    , MIN([next_run_time]) AS [NextRunTime]

                FROM [msdb].[dbo].[sysjobschedules]

                GROUP BY [job_id]

            ) AS [sJOBSCH]

        ON [sJOB].[job_id] = [sJOBSCH].[job_id]

    LEFT JOIN (

                SELECT

                    [job_id]

                    , [run_date]

                    , [run_time]

                    , [run_status]

                    , [run_duration]

                    , [message]

                    , ROW_NUMBER() OVER (

                                            PARTITION BY [job_id]

                                            ORDER BY [run_date] DESC, [run_time] DESC

                      ) AS RowNumber

                FROM [msdb].[dbo].[sysjobhistory]

                WHERE [step_id] = 0

            ) AS [sJOBH]

        ON [sJOB].[job_id] = [sJOBH].[job_id]

        AND [sJOBH].[RowNumber] = 1

ORDER BY [JobName]

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: