In the SQL server integration service, you can automatically obtain charts of the SharePoint workbook report and generate PDF files and send emails!

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.