June 18, 2012 at 10:53 am
I've been searching for hours through the various websites and blogs.
People give part of a solution and then I am not seeing how they complete the task. It may be my not understanding as this is the first time trying to send mail in SSIS.
I have a table (myTable) with the columns
myDB varchar(15)
myStep varchar(20)
myMSG varchar(100)
I need to email the results of this to some users.
I have found a lot of partial answers. The closest one says to use XML transform, but it also tells me I need to use a xsl file. I have no idea how to create a xsl file.
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/
Does any one have a clear idea of how to send a query resultset as a html in an email using SSIS?
Creating the control flow and the data flow was the easy part. Creating the last part which sends the results in email is taking forever.
Thanks for any help and/or pointing me in the right direction.
Thanks.
Things will work out. Get back up, change some parameters and recode.
June 18, 2012 at 11:49 am
this may give you some ideas:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<head>' +
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
N'<h2><font color="#0000ff" size="4">Work Order Report</font></h2>' +
N'</head>' +
N'<body>' +
N' <hr> ' +
N' ' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th>
</tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks2008R2.Production.WorkOrder as wo
JOIN AdventureWorks2008R2.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2006-04-30'
AND DATEDIFF(dd, '2006-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='abc@xyz.co.uk',
@profile_name = 'SQl',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 18, 2012 at 12:15 pm
Well thank you for responding. But like a lot of the things I am seeing, there is one or two things that aren't explained.
I ran the script, but it failed on @profilename = 'SQL'.
Could you please tell me how the profile name is determined?
I assume this is being placed in an ExecuteSQL Task?
Thank you.
Things will work out. Get back up, change some parameters and recode.
June 18, 2012 at 12:21 pm
WebTechie38 (6/18/2012)
Well thank you for responding. But like a lot of the things I am seeing, there is one or two things that aren't explained.I ran the script, but it failed on @profilename = 'SQL'.
Could you please tell me how the profile name is determined?
I assume this is being placed in an ExecuteSQL Task?
Thank you.
try google for "sql server dbmail profile"...you will learn far more than I have time to explain or understand 😀
yes..it is used within ExecuteSQL
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 18, 2012 at 12:35 pm
I've already done that.
Most of the sites are saying go into SSMS and create a profile.
I've done that.
I've created a public profile in database mail in SSMS and set the default to Yes.
However, when I run the SSIS project, I get no public profile created.
Things will work out. Get back up, change some parameters and recode.
June 18, 2012 at 12:40 pm
What name did you give that public profile? Is that the name you are using in the msdb.dbo.sp_send_dbmail command?
June 18, 2012 at 12:52 pm
Oh My Goodness!!!!!!!!!!!!!
Finally success.
I had to create the profile name on the server that I am using with the connection manager. I had previously created a profile on the server that I use to create my applications. That failed.
When I thought to create a profile on the data server where my ExecuteSQL tasks are querying, that worked.
Yeah.
Thank you all for your help.
Things will work out. Get back up, change some parameters and recode.
June 18, 2012 at 1:01 pm
WebTechie38 (6/18/2012)
Oh My Goodness!!!!!!!!!!!!!Finally success.
I had to create the profile name on the server that I am using with the connection manager. I had previously created a profile on the server that I use to create my applications. That failed.
When I thought to create a profile on the data server where my ExecuteSQL tasks are querying, that worked.
Yeah.
Thank you all for your help.
pleased to see you have solved your "problem"
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply