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

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

2. /*    

3. Replace @ v_currentdate with the current date in actual use

4. */    

6. declare    @v_CurrentDate    datetime    

7. set    @v_CurrentDate =    '2003-07-17'    

8.  /*set    @v_CurrentDate  = GETDATE()*/    

10. if    exists    (select 1    

11.         from    Sales.SalesOrderHeader    

12.         where    OrderDate =    @v_CurrentDate)    

14. begin    

16.     select    top 10 oh.OrderDate,     

17.         (select    round(sum(TotalDue), 2)    

18.         from    Sales.SalesOrderHeader    

19.         where    OrderDate =    @v_CurrentDate) as DayTotal,    

20.         p.ProductID, p.Name,     

21.         round(sum(oh.TotalDue), 2) as ProductSubtotal    

22.     from    AdventureWorks.Sales.SalesOrderHeader    oh    

23.     join    AdventureWorks.Sales.SalesOrderDetail    od    

24.     on    od.SalesOrderID =    oh.SalesOrderID    

25.     join    AdventureWorks.Production.Product    p    

26.     on    p.ProductID =        od.ProductID    

27.     where    oh.OrderDate =    @v_CurrentDate        

28.     group     

29.     by    oh.OrderDate, p.ProductID, p.Name    

30.     order        

31.     by    5 desc, p.ProductID asc     

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

34. end    

36. else    select cast('<NoRecords>No sales records available for this date.</NoRecords>' as xml)    

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;    

9. Smtpclient. Credentials = new system. net. networkcredential ("Account", "password ");

10.       smtpClient.Send(mm);    

12.     Dts.TaskResult = (int)ScriptResults.Success;    

13. }    

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.