SQL Server Bi step by step SSIS 5-send query results by email

Source: Internet
Author: User
Tags xsl file email account mailmessage smtpclient ssis

After a while, we finally have time to complete this series. The official SQL Server 2008 version has been released. The subsequent series will be developed based on SQL Server 2008 + vs.net 2008.

Introduction
In a B2B project, the boss wants to see all the new order information every day. The boss is very lazy and does not want to log on to the system background, instead, you can view the email. Of course there are many implementation methods. Here we will introduce how to use the SSIS package to implement such a function. Use SQL XML query to query XML result data, convert XML data to HTML using the XSL template, and then send an email to the HTML content.

Create an SSIS package

1. Create an integration service project in the same way as vs.net 20005, and change the package name to sendmailpackage.
2. Create a new directory on your local hard disk, such as F: \ ssis_example \ Sendmail, to store query statements and XSL files.
3. Create a New queryorder. SQL file under the directory just now, fill in the following content, create a new file connection in the Connection Manager, and select the new SQL file:

Hide row number copy code? Queryorder. SQL
  1. /*    
  2. Replace @ v_currentdate with the current date in actual use
  3. */    
  4.     
  5. declare    @v_CurrentDate    datetime    
  6. set    @v_CurrentDate =    '2003-07-17'    
  7.  /*set    @v_CurrentDate  = GETDATE()*/    
  8.     
  9. if    exists    (select 1    
  10.         from    Sales.SalesOrderHeader    
  11.         where    OrderDate =    @v_CurrentDate)    
  12.     
  13. begin    
  14.     
  15.     select    top 10 oh.OrderDate,     
  16.         (select    round(sum(TotalDue), 2)    
  17.         from    Sales.SalesOrderHeader    
  18.         where    OrderDate =    @v_CurrentDate) as DayTotal,    
  19.         p.ProductID, p.Name,     
  20.         round(sum(oh.TotalDue), 2) as ProductSubtotal    
  21.     from    AdventureWorks.Sales.SalesOrderHeader    oh    
  22.     join    AdventureWorks.Sales.SalesOrderDetail    od    
  23.     on    od.SalesOrderID =    oh.SalesOrderID    
  24.     join    AdventureWorks.Production.Product    p    
  25.     on    p.ProductID =        od.ProductID    
  26.     where    oh.OrderDate =    @v_CurrentDate        
  27.     group     
  28.     by    oh.OrderDate, p.ProductID, p.Name    
  29.     order        
  30.     by    5 desc, p.ProductID asc     
  31.     for xml auto, elements, type, root('Order')    
  32.     
  33. end    
  34.     
  35. else    select cast('<NoRecords>No sales records available for this date.</NoRecords>' as xml)    
  36.     
  37.     
  38.     
  39.         
  40.     

Note: here we use a fixed date. In practice, you can use the system method getdate () or datediff () to query the date you need.

4. Create an orders. XSL file under the directory. Because there are many contents, please download the file in the attachment directly. The specific content of XSL is not described here. Create a file connection.
5.create an orders.htm file in the directory to store the generated HTML content and create a file connection.
6. Create a New ado.net connection to your adventureworks database. Now your connection manager has the following connections:


7. Create the following variables with the scope of the current package sendmailpackage.

Name Data Type Value
Varsalessummaryxml String  
Varsalessummaryhtml String  
Varmailto String Your email address

8. Add an SQL Execution task in your control flow, set the SQL type to file, and select our file connection:

9. Switch to the result set and click Add. The result name is 0. Select varsalessummaryxml for the variable.

10. Add an XML task in the control flow and direct the Green Arrow of the SQL task to the XML task. Set the attributes as follows:

In order to test the results, we set the input results to save the file. First, run the package to test whether the HTML content generated using XML + XSLT is correct:
 

Note: now we will change the output result to the variable method, set the destinationtype attribute to the variable method, and select varsalessummaryhtml

11. add a script task under the XML task to execute the mail sending task. The reason why SSIS is not used directly is that the mail sending task currently does not support HTML content. set the attributes of the script task:

 

 

 

 

 

As you can see, the setting of a script task has changed significantly compared with version 2005, and the script language finally began to support C #.PrecompilescriptintobinarycodeIf the attribute does not exist, all scripts must be pre-compiled. In addition, the setting of readonlyvariables and readwritevariables variables can be directly selected in the subsequent selection dialog box. These improvements are much more convenient. After the settings are complete, you can click the compile script to use the familiar C # to complete the mail sending operation.
Note that we need to introduce system. net. then, use smtpclient to send emails through the specified email server. You can specify the email account and password, while the SSIS mail task does not support non-Windows authentication.

Hide row number copy code? Queryorder. SQL
  1. public void Main()    
  2. {    
  3. Smtpclient = new smtpclient ("email server address", 25 );
  4.       string body =  Dts.Variables["varSalesSummaryHTML"].Value.ToString();    
  5.       string address =  Dts.Variables["varMailTo"].Value.ToString();    
  6. Mailmessage Mm = new mailmessage ("Sending address", address, "Order detail", body );
  7.       mm.IsBodyHtml = true;    
  8. Smtpclient. Credentials = new system. net. networkcredential ("Account", "password ");
  9.       smtpClient.Send(mm);    
  10.     
  11.     Dts.TaskResult = (int)ScriptResults.Success;    
  12. }    

12. now that the function has been completed, you can directly receive the order list by directly executing the package. However, to regularly execute this package, you need to deploy this SSIS package, then define and execute this package in the Database Job. right-click the current project and set the createdeploymentutility attribute to true to automatically generate the deployment file.

 

Re-compile this project. In the deployment folder under the bin directory, double-click the file named ssisdeploymentmanifest below to start the package installation wizard. Here we will not detail it here. go directly to the next step until it is completed.

13. open your SSMs, check that the SQL Server proxy has been started, create a job, set the execution of the SSIS package in the step, and set the schedule to the desired time, I will not detail it here.

Reference
Http://www.sqlservercentral.com/articles/Integration+Services+ (SSIS)/62678/
Project download
Http://files.cnblogs.com/lonely7345/SSISMailExample.rar

Author: lone knight (like a year of water)
Source: http://lonely7345.cnblogs.com/
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.