Sending scheduled SQL query results via HTML e-mail using SSIS

Question

Sending scheduled SQL query results via HTML e-mail using SSIS

Viewing 15 posts - 31 through 45 (of 84 total)

You must be logged in to reply to this topic. Login to reply

Geoffrey Doebler SSC Veteran Points: 278 More actions · Answer 1

Michael Jenck (6/4/2008)
I had a similar problem. We tried to use a network account to run the SQL server agent. At first it crashed the server. Once we set the account as a a member of the correct local group - it worked fine. Our case required two sets of permissions local and network.
my 2 cents.

When you say local group, do you mean on the server where the db is? I imagine it's some sort of permissions thing. The package runs with my credentials. I have another package, that doesn't send email, that runs just fine using my creds. Funny thing is both packages have file connections to the same directory on the network. However, the package that doesn't run, goes one folder deeper.

Michael Jenck SSC Veteran Points: 271 More actions · Answer 2

I tried to get a screen capture - but I don't have permissions. What I remember is you need to go into user accounts and groups and add the "SQL Server Agent Service Account" into the local Windows group for that service. See http://kbalertz.com/932881/unwanted-access-Server-operating-system-administrator-difficult.aspx

I believe the local Windows group is called SQLServer2005AgentUser.

Mike

Geoffrey Doebler SSC Veteran Points: 278 More actions · Answer 3

I have an update...I tried running my package from the sql server through SSMS. When I do, it errors out at the first step where I execute my stored procedure that contains the sql to generate my XML data. Here's the error I get:

Error Executing Query "EXEC operations.usp_getMustShipList" failed with the following error: "Required white space was missing." Possibel failure reasons: Problem with the Query: "Result set" property not set correctly, parameters not set correctly, or connection not established correctly.

This is wierd since I can run the package from my machine through SSMS just fine with no errors.

Any thoughts?

Thanks.

Geoffrey Doebler SSC Veteran Points: 278 More actions · Answer 4

I figured it out. Nothing to do with permissions...I forgot that I changed my ADO.NET connection to a OLE DB connection. Once I changed it back to the ADO.NET, everything works great!

Open Minded SSCommitted Points: 1872 More actions · Answer 5

http://msdn.microsoft.com/en-us/library/system.net.mail.attachment.aspx

thank you for the idea on how to send email from script object. because i use file attachments, above is the idea on how to do it.

in my vb code:

Dim varAttachment As Object

varAttachment = New Attachment(Dts.Variables("gv_FileName").Value.ToString)

varMail.Attachments.Add(varAttachment)

so i added lines to dispose of the objects creating the file and sending mail in the script objects because i encountered an error - it was because i stopped the package midway to correct something. which reminded me of clean up even if we are using vb.net

varMail.Dispose

Open Minded SSCommitted Points: 1872 More actions · Answer 6

just to wrap up my post above, although it is already a subtopic...

due to corporate rules, file attachments may have to be password-protected. doing so in excel also automatically encrypts it (just let excel do it underneath).

as of now, i reaally dont know if it was ClickOnce or the package ProtectionLevel, but i am just so glad (understatement) i finally got it running.

see post below where i joined a discussion then polished off on how to send password-protected excel file as attachment

Re: Password protect Excel output

http://forums.microsoft.com/msdn/ShowPost.aspx?siteid=1&postid=3527605

Bernd Griener Newbie Points: 3 More actions · Answer 7

Great idea!

I just implemented my first HTML formatted email report and it works fine.

At the end of the article Paul mentions the possibility combining multiple result sets in one mail. That's exactly what I need - but don't know how to do it.

Can anybody give me a hint or a short example?

Thanks in advance!

aveerabadran Ten Centuries Points: 1271 More actions · Answer 8

Hi Paul,

I am trying to generate a package similar to what you have put it over here. I use the same variable , same name for all tasks too. But have one exception that- since i dont have adventure works DB, i have used my own query (i have put that below). But have problems in making it success.

the "Get Daily Totals" (execute sql task) works perfectly , but i have problems in "Test output to HTML file" (XML Task). It fails with the error message :

"[XML Task] Error: An error occurred with the following error message: "XSLT compile error.". "

I dont know what is the cause for the error.

***************************************************

The following is the code present in "SQL Query for Order Totals.sql"

/*

This script © 360Data 2008

*/

declare@v_CurrentDatedatetime

set@v_CurrentDate ='2008-08-22'

if exists (SELECT 1 FROM

(select CONVERT(CHAR,PROC_DT,101) PROC_DT,IBD_ID,COUNT(*) CNT from GEAT_GTDE_A

where PROC_DT =@v_CurrentDate GROUP BY CONVERT(CHAR,PROC_DT,101),IBD_ID )MAIN

GROUP BY MAIN.PROC_DT,MAIN.IBD_ID )

begin

SELECT MAIN.PROC_DT,MAIN.IBD_ID,SUM(MAIN.CNT) TRADE FROM

(select

CONVERT(CHAR,PROC_DT,101) PROC_DT,IBD_ID,COUNT(*) CNT

from

GEAT_GTDE_A

where

PROC_DT = @v_CurrentDate

GROUP BY

CONVERT(CHAR,PROC_DT,101),IBD_ID

)MAIN GROUP BY MAIN.PROC_DT,MAIN.IBD_ID order by 1 desc

for xml auto, elements, type, root('Order')

end

elseselect cast(' ' as xml)

***************************************************

The following is the code present in "Orders.xsl"

body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}

h1{color: #f00;}

h2{size: 70%; color: #00f;}

td{padding-right: 10px;}

body {margin-left: 20px; font-family: tahoma, sans-serif;}

No sales records were found for the specified date.

***************************************************

Can u help in resolving this issue

vdaroju Newbie Points: 3 More actions · Answer 9

Hi ,

i am new to SSIS,

i opened a SSIS package, but i didn't see any 'Connection Managers pane'.

could you please let me know how to get it.

Rodan SSC Veteran Points: 220 More actions · Answer 10

I took a different way out a while back - I have a job that just runs a series of emails - this one is hard coded going to me & my boss, but you get the idea. Others I select all possible emails from the DB and email the specific info to the users... (so where it is jymm@beer.com I have a variable in the sql) - been working pretty cool for a while. Easier for the non visual folks to understand 😉

DECLARE @tableHTML NVARCHAR(MAX) ;

select Hdr.OrderNumber, hdr.shiptoname, hdr.createdate, hdr.requiredshipdate

from Ordrhdr as hdr

where hdr.shiptocountry = 'Canada' and hdr.orderfilled = 'O'

if @@RowCount > 0

Begin

SET @tableHTML =

N' ' +

CAST ( ( SELECT td = Hdr.OrderNumber, '',

td = Hdr.ShipToName, '',

td = Cast(Hdr.CreateDate as CHar(12)), '',

td = Cast(Hdr.RequiredShipDate as CHar(12)), ''

from Ordrhdr as hdr

where hdr.shiptocountry = 'Canada' and hdr.orderfilled = 'O'

order by Hdr.RequiredShipDate

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N' ' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='jymm@beer.com; rob@beer.com',

@subject = 'New Canadian Order needs doc ',

@body = @tableHTML,

@body_format = 'HTML' ;

End

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 11

Great Article. Interesting approach. Thank you for the contribution.

-Mike

Eric M Flamm Say Hey Kid Points: 661 More actions · Answer 12

UPDATE2: Doh! Just a typo in one of the variable names.

Still, is there any way to put the script in a script task into debug mode when executing the package, so that you can step through the script with live data?

UPDATE: I went back and deleted the script task and built a new one. This time, I kept all of the infrastructure code that SSIS inserts;

I added the Imports for System.Net.Mail, and the script built correctly. However, when I run the package, I get the following error:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->

Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from

a collection on a container during execution of the package and the element is not there.

I can't tell from the stack trace what the problem is, and I don't know how to invoke a debugger that will let me step through a script and see what the variable values are.

Any suggestions?

-Eric

Hi,

I tried to build the example using SQL Server 2008/BIDS 2008, and I could not get the Script to build. The error reported is:

Error1Name 'Dts' is not declared.c1efb0bd34654788ba529d6d6268ff47\ScriptMain.vb2523st_4cf65c5d211547a78eb10be428868eb0

There are 4 build errors - one for each reference to Dts.Variables. I have the Imports Microsoft.SqlServer.Dts.Runtime

statement. Was there any change to the namespace/nomenclature in 2008? I suppose I could have a bad install - I haven't done much with SSIS since I installed SQL Server 2008.

[font="Tahoma"]Eric Flamm, Flamm Consulting[/font]

jhasbufuidh Ten Centuries Points: 1002 More actions · Answer 13

Hi, Did anyone tried to send this type of report to a blackberry? I have mixed success with this, it works very well with a PDA but not so well with Berries.

BI Guy

edmund chan Say Hey Kid Points: 689 More actions · Answer 14

Excellent article. The way that I have been doing this is to create a report on SSRS and then send the report out in SSIS by calling the Subscription on SSRS. SSRS provides much more reporting functions and it is easy to format the report.

Other than using SSIS for ETL, I also am using SSIS as a Emailer Services for the company. I have a Email_Queue table that has fields like to, cc, bcc, Subject, body, etc. The SSIS Emailer Services read the table every minute and send the email out and save the record into Email_Log or Email_Error_Log. For all the emails in each application, all they need to do is to insert data into the Email_Queue table and then the rest will be handled by SSIS.

There is no doubt that SSIS can do much more other than just ETL.

Ed

Paul Clancy SSC Eights! Points: 858 More actions · Answer 15

Thanks very much for all the appreciative and useful comments. I'm afraid I don't have time at present to answer all the questions that have been asked - this article was submitted and first published almost a year ago when time was more plentiful. I'll try to get back to this at some stage but if anyone else can help in the meantime you won't hear me complaining!

Several people have pointed out that SSIS isn't perhaps the most logical choice for mailing data. I completely agree, but the point of the article wasn't to build a mailing solution, but to show how flexible SSIS is and how you can put it to uses that extend far beyond simple ETL. Anyway, thanks again to everyone who responded.