I recently conducted a small integration application test, which is suitable for users who do not want to view the relevant reports on the website but want to view the reports by email at regular intervals.Relatively static reports(No user interaction required), You can use an integrated operation method described in this article. The main implementation ideas are as follows:
1. First, you can use the Excel Service Web Service of Sharepoint to automatically obtain and download the charts of the SharePoint workbook report to the local Temporary Folder;
2. Generate a local chart PDF using itextsharp of the open-source PDF component;
3. Finally, send the chart PDF to the specified email user through the mail task in SSIs.
This document uses the powerdeskthealthcareaudit.xlsx report as an example to obtain charts in the dashboard sheet. The procedure is as follows:
Download the step-by-step task of point's powerdeskthealthcareaudit.xlsx report.Code, As follows:
/* Microsoft SQL Server integration services script task write scripts using Microsoft Visual C #2008. The scriptmain is the entry point class of the script. */ Using System; Using System. Data; Using Microsoft. sqlserver. DTS. runtime; Using System. Windows. forms; Using System. IO; Using System. net; Namespace St_1c6a297d211c4ba690ec8a0a9bd66306.csproj {[system. addin. Addin ( " Scriptmain " , Version = " 1.0 " , Publisher = "" , Description ="" )] Public Partial Class Scriptmain: Microsoft. sqlserver. DTS. Tasks. scripttask. vstartscriptobjectmodelbase { # Region Vsta generated code Enum Scriptresults {success = Microsoft. sqlserver. DTS. runtime. dtsexecresult. Success, Failure = Microsoft. sqlserver. DTS. runtime. dtsexecresult. Failure }; # Endregion /* The execution engine callthis method when the task executes. to access the object model, use the DTs property. connections, variables, events, and logging features are available as members of the DTs property as shown in the following examples. to reference a variable, call DTs. variables ["mycasesensitivevariablename"]. value; to post a log entry, call DTs. log ("this is my log text", 999, null); to fire an event, call DTs. events. fireinformation (99, "test", "Hit the help message", "", 0, true); to use the connections collection use something like the following: connectionmanager CM = DTs. connections. add ("oledb"); cm. connectionstring = "Data Source = localhost; initial catalog = adventureworks; provider = sqlncli10; Integrated Security = sspi; Auto translate = false;"; before returning from this method, set the value of DTs. taskresult to indicate success or failure. to open help, press F1.*/ Public Void Main (){ // Todo: add your code here Excelservice. excelservice XLS = New St_1c6a297d211c4ba690ec8a0a9bd66306.csproj.excelservice.excelservice (); XLS. url = " Http://portal.contoso.uat/sites/cockpit/_vti_bin/ExcelService.asmx " ; // XLS. Credentials = system. net. credentialcache. defaultnetworkcredentials; System. net. networkcredential NC = New System. net. networkcredential ( " Username " , " Password " , " Contoso. UAT " ); XLS. Credentials =NC; excelservice. status [] status; String Sessionid = XLS. openworkbook ( @" Http://portal.contoso.uat/sites/cockpit/PowerPivot/PowerPivotHealthcareAudit.xlsx " , String . Empty, String . Empty, Out Status ); String Url = XLS. getchartimageurl (sessionid, Null , " Chart 1 " , Out Status); createxlspng (xls, URL, " Chart 1 " ); URL = XLS. getchartimageurl (sessionid, Null , " Chart 2 " , Out Status); createxlspng (xls, URL, " Chart 2 " ); URL = XLS. getchartimageurl (sessionid, Null , " Chart 3 " , Out Status); createxlspng (xls, URL, " Chart 3 " ); URL = XLS. getchartimageurl (sessionid,Null , " Chart 4 " , Out Status); createxlspng (xls, URL, " Chart 4 " ); URL = XLS. getchartimageurl (sessionid, Null , " Chart 13 " , Out Status); createxlspng (xls, URL, " Chart 13 " ); Status = XLS. closeworkbook (sessionid); DTS. taskresult = ( Int ) Scriptresults. success ;} Private Void Createxlspng (excelservice. excelservice es, String URL, String Pngname) {webrequest req = Webrequest. Create (URL); Req. Credentials = System. net. credentialcache. defaultcredentials; Using (Filestream output = file. Create ( " C: \ temp \\ " + Pngname + " . PNG " )) Using (Webresponse response =Req. getresponse ()) Using (Stream Input = Response. getresponsestream ()){ Byte [] Buffer = New Byte [ 1024 ]; Int Read; While (Read = input. Read (buffer, 0 , Buffer. Length)> 0 ) {Output. Write (buffer, 0 , Read );}}}}}
After the script is executed, five charts are generated under c: \ temp on the local machine, for example:
(2). Use the open-source PDF component itextsharp to generate the script task code of the local chart PDF, as shown below:
/* Microsoft SQL Server integration services script task write scripts using Microsoft Visual C #2008. The scriptmain is the entry point class of the script. */ Using System; Using System. Data; Using Microsoft. sqlserver. DTS. runtime; Using System. Windows. forms; Using Itextsharp. text; Using Itextsharp.text.pdf; Using System. IO; Namespace St_8a0e74f918f64cac9fe6e94300fa4ccb.csproj {[system. addin. Addin ( " Scriptmain " , Version = " 1.0 " , Publisher = "" , Description = "" )] Public Partial Class Scriptmain: Microsoft. sqlserver. DTS. Tasks. scripttask. vstartscriptobjectmodelbase { # Region Vsta generated codeEnum Scriptresults {success = Microsoft. sqlserver. DTS. runtime. dtsexecresult. Success, Failure = Microsoft. sqlserver. DTS. runtime. dtsexecresult. Failure }; # Endregion /* The execution engine callthis method when the task executes. to access the object model, use the DTs property. connections, variables, events, and logging features are available as members of the DTs property as shown in the following examples. to reference a variable, call DTs. variables ["mycasesensitivevariablename"]. value; to post a log entry, call DTs. log ("this is my log text", 999, null); to fire an event, call DTs. events. fireinformation (99, "test", "Hit the help message", "", 0, true); to use the connections collection use something like the following: connectionmanager CM = DTs. connections. add ("oledb"); cm. connectionstring = "Data Source = localhost; initial catalog = adventureworks; provider = sqlncli10; Integrated Security = sspi; Auto translate = false;"; before returning from this method, set the value of DTs. taskresult to indicate success or failure. to open help, press F1.*/ Public Void Main (){ // Todo: add your code here Imagedirect (); DTS. taskresult = ( Int ) Scriptresults. success ;} Public Void Imagedirect (){ String ImagePath = @" C: \ temp \ chart 1.png " ; String Filename = String . Empty; filestream fi = System. Io. file. Create ( @" C: \ temp \ powerdeskthealthcareaudit.pdf " ); FI. Close (); filename = @" C: \ temp \ powerdeskthealthcareaudit.pdf " ; Document = New Document (); author writer = Author writer. getinstance (document, New Filestream (filename, filemode. Create); document. open (); itextsharp. Text. Paragraph P = New Itextsharp. Text. Paragraph ( " Powerdeskthealthcareaudit " , New Itextsharp. Text. Font (font. fontfamily. Helvetica, 22f); p. Alignment = Element. align_center; document. Add (p); itextsharp. Text. Image img = Itextsharp. Text. image. getinstance (ImagePath); document. Add (IMG); ImagePath = @" C: \ temp \ chart 2.png " ; Itextsharp. Text. Image img2 = Itextsharp. Text. image. getinstance (ImagePath); document. Add (img2); ImagePath = @" C: \ temp \ chart 3.png " ; Itextsharp. Text. Image img3 = Itextsharp. Text. image. getinstance (ImagePath); document. Add (img3); ImagePath = @" C: \ temp \ chart 4.png " ; Itextsharp. Text. Image img4 = Itextsharp. Text. image. getinstance (ImagePath); document. Add (img4); ImagePath = @" C: \ temp \ chart 13.png " ; Itextsharp. Text. Image img5 = Itextsharp. Text. image. getinstance (ImagePath); document. Add (img5); document. Close ();}}}
After the script is executed, a PDF file powerdeskthealthcareaudit. will be generated under c: \ Temp, as shown in:
(2). Add mail sending tasks in SSIs, for example:
After the mail task is completed, the user will receive a PDF file of the chart, such:
Through the above steps, an integrated service that automatically generates a chart PDF can be further expanded as needed to meet actual needs.
This blog for the original software life, welcome to reprint, reprint please indicate the source: http://www.cnblogs.com/nbpowerboy/archive/2013/05/16/3081599.html. Assume or use it for commercial purposes, but you must keep the signed software life (including links) in this article ). If you have any questions or authorization negotiation, please leave a message for me. |