Getting Informative SQL Agent Notifications from Failed SSIS Packages

• Harold Buckner

  SSCarpal Tunnel

  Points: 4175

  More actions

  November 23, 2020 at 1:42 pm

  #3813031

  error

  • This reply was modified 4 years, 6 months ago by  Harold Buckner.

• siriu

  Newbie

  Points: 1

  More actions

  November 24, 2020 at 8:48 am

  #3813375

  I try the script but with this part of code I have an error : "Error converting data type nvarchar to varbinary."

  select@Job_id = Job_id
  --@Job_name = [name] --AS job_name
  fromsys.dm_exec_sessions AS ions
  inner join msdb.dbo.sysjobs AS jobs on jobs.job_id
  = Cast(Convert(binary(16), SUBSTRING(ions.[program_name],CHARINDEX('(Job 0x',ions.[program_name],1) + 5 , 34),1) as uniqueidentifier)

   

   

• Steve Rezhener

  SSChasing Mays

  Points: 615

  More actions

  November 25, 2020 at 12:01 am

  #3813699

  shame on my testing skills. please wait for a revised code that is going to include 2 filters

  AND [program_name] is not null

  AND CHARINDEX('(Job 0x',ions.[program_name],1) > 0

• Steve Rezhener

  SSChasing Mays

  Points: 615

  More actions

  November 25, 2020 at 6:27 pm

  #3809321

  Comments posted to this topic are about the item Getting Informative SQL Agent Notifications from Failed SSIS Packages

• Steve Rezhener

  SSChasing Mays

  Points: 615

  More actions

  December 4, 2020 at 8:47 pm

  #3817083

  everything should be working now.