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

  • This article was extremely helpful, but I have run into a snag.

    This works great and it sends me the email I need when executing in BIDS, but once I schedule the package as a Job in SQL Server 2008 the email gets sent to me blank.

    Any suggestions would be fantastic.

  • ..probably permissions related. Does the account under which the sql agent service is running have the necessary permissions on the database?

  • Paul

    Thanks a lot for this contribution, this will help me to sent my employee their check balance monthly, extracting data from our ERP.

    Just a little issue cause on the HTML I have images and those are referred over the a www site accesible for anywhere, but when I receive the email, the mail cames without those images, and there is no secure waring or option to download the images.

    What can be the issue?, is this just plain HTML (tables, styles) or can be better?

    Also the mailTo variable, can I update this variable from a SQL Task to extract the value and modify this also the subjetct of the mail?

    Thank

  • Updating a variable from an Execute SQL Task is pretty straightforward; choose your connection, enter your SQL command (in this case, any statement that returns one value, this being the subject line or variable portion thereof), choose the result set type (single row in this instance), then on the Result Set tab select the variable that you're mapping the statement result to from the list and you're set.

    More details here: http://technet.microsoft.com/en-us/library/cc280492.aspx

  • Thanks, this variable set appears to be simple to do, now I have my main concern about the mail, I have all working but the content just send plain text, no HTML I have images on my HTML taht is contruct with the xml passing thru xslt and the images never cames on the mail, what can be the solution, thanks

    Regards

  • PAul

    Ipm sorry I can not get the variable runs: I get the next error:

    Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Result binding by name "TesteMail" is not supported for this connection type. ".

    I set the SQL Task same as you mention, but this error is what I get on Red, when Running on SSI

    Can you please help thanks

    Regards

    Jorge G

  • The first thing I'd do is check the output - see if you're getting valid HTML that includes the images you want to see before this gets attached to or included in your mail. If this looks right then I'm inclined to think that the images are being stripped somewhere along the line by your mail server, or something external to SSIS in any case.

    To boil it down, first make sure whether or not the issue is due to what you're doing in SSIS.

  • Paul,

    I have a question. The Script Task is working fine, except for one thing. It sends the email is all cases if there is data or none. When there is no data returned from the SQL Query, it sends the email with the Table Header only. How can I control sending the email, by telling the script to send the email only in one case, if there is data returned from the SQL Query. Can you help me with that.

    Mac Hanna

  • Test existence of data and set a variable to 1 or 0. Then add an expression to the flow line to evaluate the variable so the mail task runs only if the condition is met.

    BI Guy

  • I'm sorry I didn't get it, can you explain that in detail. I cannot follow where I can do that

Viewing 10 posts - 76 through 84 (of 84 total)

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