by Albert Mutangiri
26. April 2010 20:54
Last month I had a complex task working with one of my friend's legacy applications. He's got a bunch of sql data transformation services configured to import data from a bunch of text files. Although data transformation services offers powerfull capabilities when dealing with bulk data imports, They are pretty complex to debug as well. So we needed a way to track the number of records that are imported and errors if any for each DTS as well as notify the administrator with this data.
So well I figured out that with Data Transformation Services, you can configure it to log it's activities in msdb system database - by default it creates logs in sysdtspackagelog table. We needed to send a view of this package log in html format - via email to the configured Administrator after each dts completes running.
With the help of the system procedure msdb.dbo.sp_send_dbmail, I've created a custom mail sender in sql which we could execute by passing parameters to it and run it in the context of DTS running as a custom sql script, it loops through the column names of the specified database object and generates html. Well this worked like a charm 
create proc [dbo].[send_sql_table_as_html]
@source_db sysname, --
where the @object_name
is residing
@
schema sysname, --
Schema name eg.. dbo.
@object_name sysname, --
Table or view to email
@emailAddres
varchar(75), --Mail Address
@name nvarchar(15), --Package Name
@order_clause nvarchar(
max) --The
order by clause eg. x, y, z
asbegin declare @subject nvarchar(
max),@body nvarchar(
max)
--Get columns for table headers..
exec( '
declare col_cur cursor for
select name
from ' + @source_db + '.sys.columns
where object_id = object_id( ''' + @source_db + '.' + @schema + '.' + @object_name + ''')
order by column_id
' )
open col_cur
declare @col_name sysname
declare @col_list nvarchar(max)
fetch next from col_cur into @col_name
set @body = N'<table border=1 cellpadding=1 cellspacing=1><tr>'
while @@fetch_status = 0
begin
set @body = cast( @body as nvarchar(max) )
+ N'<th>' + @col_name + '</th>'
set @col_list = coalesce( @col_list + ',', '' ) + ' td = ' + cast( @col_name as nvarchar(max) ) + ', '''''
fetch next from col_cur into @col_name
end
deallocate col_cur
set @body = cast( @body as nvarchar(max) )
+ '</tr>'
declare @query_result nvarchar(max)
declare @nsql nvarchar(max)
--Form the query, use XML PATH to get the HTML
set @nsql = '
select @qr =
cast( ( select ' + cast( @col_list as nvarchar(max) )+ '
from ' + @source_db + '.' + @schema + '.' + '.' + @object_name +'
order by ' + @order_clause + '
for xml path( ''tr'' ),type
) as nvarchar(max) )'
exec sp_executesql @nsql, N'@qr nvarchar(max) output', @query_result output
set @body = cast( @body as nvarchar(max) ) + @query_result
--Send notification
set @subject = 'Please find the upload details for ' + @name + ' file '
set @body = @body + cast( '</table>' as nvarchar(max) )
set @body = '<p>File upload Details for -------' + @name + ' file'
+ ' </p>'
+ cast( @body as nvarchar(max) )
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test', --Profile configured in sql, this will be used by sp_send_dbmail to send mail
@recipients = @emailAddres,
@body = @body,
@body_format = 'HTML',
@subject = @subject
end
Subscribe