Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Логирование пакетов SSIS  [new]
AlexMajic
Member

Откуда: Kiev
Сообщений: 99
Делюсь решением.
Постановка задачи - необходимо иметь каждый день на почте отчет о выполнении определенных пакетов SSIS с детализацией по времени выполнения каждой задачи и /при наличии/ описания ошибок с указанием задачи, на которой они появились.
В решении есть потенциальная проблема превышения количества символов, но пока с ней воочию не сталкивался.

Решение.
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2811.0 (X64)
Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
1. Создана конфигурационная таблица для рассылки отчетов.
CREATE TABLE [dbo].[Pack_Rep](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[package] [nvarchar](100) NULL,
	[user] [nvarchar](50) NULL

где package = имя пакета, user = почтовый адрес пользователя, которому нужно отослать отчет
rem: для одного пользователя в таблице могут быть указаны >=1 пакета
2. В пакетах, которые необходимо логировать и по которым нужно отсылать отчеты настроен SSIS log provider for SQL Server. (В джобе на степе выполнения пакета вкладка logging)
3. Создана таблица для временного хранения переменных рассылаемых отчетов
CREATE TABLE [dbo].[Pkg_Rep_Data](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[user] [nvarchar](50) NULL,
	[body] [nvarchar](max) NULL

user = почтовый адрес пользователя, которому нужно отослать отчет
body = текст отчета (в формате html)
4. Создана хп для заполнения [Pkg_Rep_Data]
+
ALTER procedure [dbo].[spPack_Rep] @date_start datetime2, @date_end datetime2  as

begin
declare @user varchar(50)
declare @pkg varchar(100)
declare @st varchar(50)
declare @et varchar(50)
declare @t_int varchar(50)
declare @ss varchar(400)
declare @msg varchar(max)
declare @exid varchar(50)
declare @bdy varchar(max)
declare @src varchar(400)
set @bdy ='<tr><td align=center><b>Наименование пакета</b></td><td align=center><b>Начало выполнения</b></td><td align=center><b>Окончание выполнения</b></td><td align=center><b>Затраченное время</b></td><td align=center><b>Наименование пакета/задачи</b></td><td align=center><b>Комментарии</b></td></tr>'

delete from [dbo].[Pkg_Rep_Data]
declare cfu cursor for (select distinct [user] from [dbo].[Pack_Rep])
	open cfu
	fetch next from cfu into @user
	while @@fetch_status=0
		begin
			declare pkg cursor for (select distinct [package] from [dbo].[Pack_Rep] where [user]=@user)
			open pkg
			fetch next from pkg into @pkg
			while @@fetch_status=0
				begin
					declare pkg_det cursor for (select cccc.exid 
												from	(select top 1000000 ccc.exid 
														from	(select cc.executionid exid, MIN(cc.id) id 
																from	(select s.executionid executionid, s.id id 
																		from [dbo].sysssislog s 
																		where s.source=@pkg and s.starttime>=@date_start 
																		and s.endtime<=@date_end) cc 
																group by cc.executionid
																) ccc	
																order by ccc.id
														) cccc
												)
					open pkg_det
					fetch next from pkg_det into @exid
					while @@FETCH_STATUS=0
						begin
							declare pkg_t_det cursor for (	select bb.s, left(cast(cast(bb.starttime as datetime2) as varchar),19), left(cast(cast(bb.endtime as datetime2) as varchar),19), 
															cast((datediff(second, bb.starttime, bb.endtime)/3600) as varchar)+' h '+
															cast(datediff(second, bb.starttime, bb.endtime)/60-datediff(second, bb.starttime, bb.endtime)/3600*60 as varchar)+
															' min '+cast(datediff(second, bb.starttime, bb.endtime)%60 as varchar)+' sec' as t_int,
															bb.source, 
															case	when cast(bb.s as varchar)!=''  then '' 
																	when (cast(bb.message as varchar)<>'' and cast(bb.message as varchar)<>'End of package execution. ')  then '<font color="red">'+bb.message+'</font>' 
																	else bb.message end as msg
															from	(select top 100000 * 
																	from	(select '' s, step.starttime starttime, step_end.endtime endtime, step.source source, step_end.message message, step.id id1, step_end.id id2
																			from [sysssislog] step, [sysssislog] step_end, [sysssislog] p
																			where p.[event]='PackageStart' and step.executionid=p.[executionid]
																			and step.sourceid<>p.sourceid and p.starttime>=@date_start and p.starttime<=@date_end
																			and step.[event] in ('OnPreExecute')
																			and step.sourceid=step_end.sourceid and step.executionid=step_end.executionid and step.executionid=@exid
																			and step_end.[event] in ('OnPostExecute', 'OnError')
																			union all
																			select p.source, p.starttime starttime, pe.endtime endtime, p.source source, pe.message message, p.id id1, pe.id id2
																			from [sysssislog] p, [sysssislog] pe
																			where p.[event]='PackageStart' and p.starttime>=@date_start and p.starttime<=@date_end and p.executionid=@exid 
																			and p.executionid=pe.executionid and pe.[event]='PackageEnd'
																			) aa 
																			order by aa.id1, aa.id2
																	) bb
															)
					open pkg_t_det
					fetch next from pkg_t_det into @src, @st, @et, @t_int, @ss, @msg
					while @@fetch_status=0
						begin
						set @bdy=@bdy+'<tr>'
						set @bdy=@bdy+'<td width=110>'+@src+'</td><td width=125>'+@st+'</td><td width=125>'+@et+'</td><td width=130>'+@t_int+'</td><td width=400>'+@ss+'</td><td>'+@msg+'</td></tr>'
						fetch next from pkg_t_det into @src, @st, @et, @t_int, @ss, @msg
						end
					close pkg_t_det
					deallocate pkg_t_det
						fetch next from pkg_det into @exid
						end
					close pkg_det
					deallocate pkg_det
				fetch next from pkg into @pkg
				end
			close pkg
			deallocate pkg
		insert into [dbo].[Pkg_Rep_Data] ([user], [body]) values (@user, @bdy)
		set @bdy=''
		fetch next from cfu into @user
		end
	close cfu
	deallocate cfu
end

5. Создан пакет для рассылки логов (см. рис.)
переменные:
vBody - System.Object
vMsg - String
vUser - String
первая задача -- exec [dbo].[spPack_Rep] @date_start=?, @date_end=?
с маппингом пользовательских переменных для хп
вторая --
SELECT [user] as vUser
      ,[body] as vMsg
  FROM [dbo].[Pkg_Rep_Data]

третья - Foreach Loop Container по User::vBody
для каждого User::vUser свое User::vMsg
внутри - сама рассылка (VB2008 + CDO)
ReadOnlyVariables: User::vMsg,User::vUser
+
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum
	
    Public Sub Main()
        '
        Const cdoHigh = 2
        Const cdoPriorityUrgent = 1
        Const cdoSendUsingPort = 2
        Const MailServerName = "192.168.0.31"
        Const cdoAnonymous = 0

        Const cdoImportance = "urn:schemas:httpmail:importance"
        Const cdoPriority = "urn:schemas:httpmail:priority"
        Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
        Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
        Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
        Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"

        Dim iMsg
        Dim iConf
        Dim Flds

        iMsg = CreateObject("CDO.Message")
        iConf = CreateObject("CDO.Configuration")
        Flds = iConf.Fields


        iMsg.BodyPart.Charset = "windows-1251"

        With Flds
            .Item(cdoImportance) = cdoHigh
            .Item(cdoPriority) = cdoPriorityUrgent
            .Item(cdoSendUsingMethod) = cdoSendUsingPort
            .Item(cdoSMTPServer) = MailServerName
            .Item(cdoSMTPConnectionTimeout) = 60
            .Item(cdoSMTPAuthenticate) = cdoAnonymous
            .Update()
        End With

        With iMsg
            .Configuration = iConf
            .From = "name@domain"
            .to = Dts.Variables("User::vUser").Value.ToString
            .Subject = "Отчет по выполнению пакетов " + Now + Dts.Variables("User::vUser").Value.ToString
            .HTMLBody = "<html><head><body><table border=""1""><font size=""2"">" + vbCrLf + Dts.Variables("User::vMsg").Value.ToString + "</font></table></body></head></html>"
            .Send()
        End With

        Flds = Nothing
        iConf = Nothing
        iMsg = Nothing

        '

        Dts.TaskResult = ScriptResults.Success
    End Sub


End Class

6. Ну и сверху всего этого прикручен джоб с запуском сего пакета +
delete from [dbo].[sysssislog]
  where starttime<(getdate()-7)

чтобы табличка не бухла.

Вроде все.

К сообщению приложен файл. Размер - 26Kb
3 авг 12, 13:49    [12958335]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить