SQL Server BI step with step SSIS 5

Busy for a while, finally have time to complete this series. The official version of SQL Server 2008 has been released, and the next series will be developed based on SQL Server 2008+vs.net 2008.

Introduction

One of the things that happens to a business-to-business project is that every day the boss wants to see all the new order information, and the boss is lazy and doesn't want to log in to the system backstage, but rather to look through the mail. Of course there are a lot of implementations, and here's how you can use SSIS packages to implement one of these features. Queries the XML result data using SQL XML query, then uses the XSL template to convert the XML data to HTML, and then sends the HTML content to the message.

Create SSIS Packages

1. In the same way as Vs.net 20005, create a Integration service project, and the name of the package is modified to Sendmailpackage

2. Create a new directory on your local hard drive, such as F:\SSIS_Example\SendMail for query statements and XSL files.

3. Create a new Queryorder.sql file in the directory below, fill in the following, create a new file connection in Connection Manager, and select the new SQL file:

Queryorder.sql

/*
Replace @v_currentdate with the date when it is actually
*/


Declare @v_CurrentDate datetime
Set @v_CurrentDat E = ' 2003-07-17 '
/*set @v_CurrentDate = GETDATE () */


If exists (select 1
from Sales.salesor Derheader
Where OrderDate = @v_CurrentDate)


Begin


Select Top Oh. OrderDate,
(select round, sum (TotalDue), 2)
from Sales.SalesOrderHeader
where OrderDate = @v_Cu Rrentdate) as Daytotal,
P.productid, P.name,
Round (sum (OH). TotalDue), 2) as Productsubtotal
from AdventureWorks.Sales.SalesOrderHeader Oh
Join AdventureWorks.Sales.Sa Lesorderdetail od
on OD. SalesOrderID = Oh. SalesOrderId
Join AdventureWorks.Production.Product p
on p.productid = od. ProductId
where Oh. OrderDate = @v_CurrentDate
Group
by OH. OrderDate, P.productid, p.name
Order
by 5 desc, p.productid ASC
FOR XML Auto, elements, type, Root (' order ')

End

Else is select cast (' <norecords>no sales records available for this date.< ;/norecords> ' as XML '

Note: Here we use a fixed date, in which you can use the System method getdate () or DATEDIFF () to inquire about the date you need.

4. Also create a new orders.xsl file under the directory, because the content is more, please download the file directly in the attachment, here is not a detailed description of the details of the XSL. A new file connection is also created.

5. Create a new orders.htm file in the directory to store the generated HTML content and create a new file connection.

6. Create a new ado.net connection to your AdventureWorks database. Your connection Manager now has the following connection: