dilluns, 27 de març de 2017

Extreure l'script dels jobs a SQL Server

SQL Server Management Studio té la utilitat de poder extreure scipts d'objectes.

Avui veurem com poder extreure aquests scripts per codi i així poder-los reaprofitar.

La manera d'extreure el codi no és a través de t-sql sinó a través de programació amb la SDK d'SQL Server. En l'exemple he utilitzar codi C#, però també es pot fer en VB.net o Powershell. Ho he fet en C# per poder-ho integrar més fàcilment a en una Script Task d'SSIS.

En l'exemple veurem com extreure el codi de jobs de l'agent d'SQL Server. Per a la resta d'objectes el funcionament bàsic és el mateix, només canvia sobre quin objecte s'invoca la funció Script().

Abans de començar amb el codi repasarem les dependències. Les dll estan a C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies (pot canviar segons la versió d'SQL Server instal·lada, en el meu cas és la 2014).
Les dll utilitzades són:
    - Microsoft.SqlServer.ConnectionInfo.dll
    - Microsoft.SQLServer.ManagedDTS.dll
    - Microsoft.SqlServer.Management.Sdk.Sfc.dll
    - Microsoft.SqlServer.Management.UtilityEnum.dll
    - Microsoft.SqlServer.Smo.dll

   
Els namespaces que necessitem són:
     using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Sdk.Sfc;  
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo.Agent;
    using System.Collections.Generic;
    using System.Collections.Specialized;


Ja entrant a dins del codi,primer ens connectarem al nostre servidor:
 ServerConnection sc = new ServerConnection();
 sc.ConnectionString = "Data Source=.;Initial Catalog=msdb;Integrated Security=SSPI;";
 Server srv = new Server(sc);


i recuperarem els jobs:
JobCollection jobs = srv.JobServer.Jobs;

Un cop tenim els jobs podem optar per recorre'ls tots:
foreach (Job job in jobs){
//TODO insert code
}

O bé seleccionar un dels jobs per nom:
Job job = jobs[jobName]; 

Ara crearem un objecte ScriptingOptions per dir les característiques que volem a l'script. (https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
         ScriptingOptions so = new ScriptingOptions();
    //so.ScriptDrops = true;
    so.FileName = "c:\\Files\\job"+jobName+"_sp.txt"; 


En aquest cas hem triat que ens mostri la sentència de creació (scriptDrops per defecte) i que deixi el resultat en un fitxer amb el nom FileName.
En aquest moment ja podem invocar la funció Script per extreure el codi. També es pot invocar la funció Script sense ScriptingOptions i t'executa amb les opcions per defecte.
StringCollection stc = job.Script(so);

La variable stc contindrà l'script i, a més, també tindrem l'script al fitxer que hem assignat a so.FileName.
foreach (String st in stc)
{
    System.IO.File.WriteAllText(@"C:\Files\job" + jobName + ".txt", st);

}

En teoria el fitxer jom<jobName>_sp.txt i job<jobName>.txt hauríen de contenir el mateix, però hi ha 1 diferència important: el fitxer generat per la funció Script acaba en GO, el generat a partir dels Strings no acaba en GO.
 Aquesta diferència és molt important si després volem executar el codi a través del procedure sp_executesql ja que aquest procedure no accepta el GO i ens donaria error.

Això és tot per avui. Us copio tot el codi sencer per poder-lo utilitzar dins d'una Script Task al SSDT.



#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;   
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Agent;
using System.Collections.Generic;
using System.Collections.Specialized;

#endregion

namespace ST_c8347870d5494946bafacf702fb334f7
{
    /// 
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


  /// 
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// 
  public void Main()
  {
            try
            {
                String jobName = (String)Dts.Variables["name"].Value;
                ServerConnection sc = new ServerConnection();
                sc.ConnectionString = "Data Source=.;Initial Catalog=msdb;Integrated Security=SSPI;";
                // Connect to the local, default instance of SQL Server.   
                Server srv = new Server(sc);

                JobCollection jobs = srv.JobServer.Jobs;
                Job job = jobs[jobName];
                ScriptingOptions so = new ScriptingOptions();
                
                so.Default = true;
                //so.ScriptDrops = true;
                so.FileName = "c:\\Files\\job"+jobName+"_sp.txt";
                StringCollection stc = job.Script(so);
                foreach (String st in stc)
                {
                    String[] lines = new String[2];
                    String final= st.Substring(0, st.Length );
                    lines.SetValue("IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'"+jobName+"') EXEC msdb.dbo.sp_delete_job @job_name=N'" + jobName + "', @delete_unused_schedule=1", 0);
                    lines.SetValue(final, 1);

                    System.IO.File.WriteAllLines(@"C:\Files\job" + jobName + ".txt", lines);
                    MessageBox.Show(final);
                }
                /*foreach (Job job in jobs)
                {
                    MessageBox.Show(job.Name);
                }*/
         }
            catch(Exception e){
                MessageBox.Show(e.Message);
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// 
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// 
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}



dilluns, 13 de març de 2017

Business Objects schedule reports RESTFul API

Avui canviarem del tot de tema i farem una mica de Java i Business Objects.
Business Objects ha canviat la manera d'accedir a la metadata i operacions a través d'API. Abans es feia a través d'una SDK i ara a traves de RESTFul web services.
Una de les debilitats que he trobat a Business Objects és l'execució d'informes automàticament quan hi ha un event (ex. acaba una càrrega). Hi ha l'opció dels events de BO, però m'he trobat a vegades problemes que no els detecta o has de vigilar de posar a l'schedule que les hores d'execució estiguin dins de l'interval d'execució del report.
També he trobat a faltar poder programar un report amb paràmetres dinàmics (ex que tregui les dades d'ahir). Fins ara creava un report que m'extreia les dades del dia anterior per programar-lo i un altre report idèntic on podia escollir el dia com a paràmetre.
Avui veurem com executar un report amb paràmetres a partir de l'API.

Tenim un report creat que es diu RepParam amb un paràmetre que és l'any-mes en que volem extreure les dades.

El primer que necessitem saber és l'ID del report per després poder-lo invocar per programar-lo. L'id el podem treure amb botó dret al nom del report i seleccionar Properties.

L'id del report és el 6551.

Un dels problemes que té la programació de reports a través de la RESTful API és que el pas de paràmetres no és molt intuïtiu, per això també he creat una funció que t'extreu com s'han de passar els paràmetres. Anem a veure el codi.


El primer que hem de fer és logar-nos al nostre CMS
String logonToken = "\"" + login(server, username, password, authentication) + "\"";



La URL a invocar és: URL url = new URL("http://" + server + ":6405/biprws/logon/long/");
Un cop logats obtenim un token que ens permetrà executar les funcions de l'API.
Sobretot hem de recordar de fer un logoff quan acabem, sinó anirem deixant sessions actives cada cop que programem un report.
URL url = new URL("http://" + server + ":6405/biprws/logoff");

Per a la funció de veure el format dels paràmetres invocarem la següent URL:
URL url = new URL("http://" + server + ":6405/biprws/raylight/v1/documents/" + docid + "/parameters");
Aquí ja necessitem l'id del report, i el paràmetre es passa per GET. Hem de recordar sempre de passar el token que ens ha retornat la funció login per que ens deixi accedir:
conn.setRequestProperty("X-SAP-LogonToken", logonToken);

El que retorna la funció amb el nostre report és:
Aquest text l'hem de copiar en un fitxer UTF-8 (important! sinó no funciona) sense la fila <?xml .... i afegint els valors que necessitem.

<parameters>
    <parameter dpId="DP0" type="prompt" optional="false">
        <id>0</id>
        <technicalName>Flight Year-Month(YYYYMM):</technicalName>
        <name>Flight Year-Month(YYYYMM):</name>
        <answer type="Text" constrained="false">
            <info keepLastValues="false" cardinality="Multiple"/>
            <values> 
                <value>201701</value> 
            </values> 

        </answer>
    </parameter>
</parameters>


Un cop ja tenim el nostre fitxer de paràmetres hem de crear un fitxer UTF-8 amb les dades de mail (id del report, format, nom del fitxer, from, to, cc, bcc, subject i cos del mail).
Per programar el report s'ha d'invocar a la URL URL url = new URL("http://" + server + ":6405/biprws/raylight/v1/documents/" + docid + "/schedules");
 Sobretot, molt important, invocar per POST.
conn.setRequestMethod("POST"); 
S'ha de vigilar molt amb el format dels paràmetres passats, sobretot pel que fa als salts de línia.
String message = "<schedule>\n" + "     <name>Schedule From Restful</name>\n" + "     <format type=\""
                + fileFormat + "\"/>\n" + "     <destination>\n" + "          <useSpecificName>" + fileName
                + " </useSpecificName>\n" + "         <mail>\n" + "             <from>" + from + "</from>\n"
                + "             <to>" + to + "</to>\n" + "             <cc>" + cc + "</cc>\n" + "             <bcc>" + bcc
                + "</bcc>\n" + "             <subject> " + subject + " </subject>\n" + "             <message> "
                + textMessage + " </message>\n" + "             <addAttachment>1</addAttachment>\n"
                + "         </mail>\n" + "     </destination>\n" + parameters + " </schedule>\n";


Ara ja estem preparats per a programar el nostre report.
Abans ja hem vist el fitxer de paràmetres (param.txt). El fitxer mail.txt conté la següent informació:
6551
xls
nom_fitxer.xls
noreply@eljordifabi.tech
desti@eljordifabi.tech
email_cc1@eljordifabi.tech,email_cc2@eljordifabi.tech

Subject mail
text text text
text 2

text 3
text 4


Aquesta crida no retorna res, hem d'anar a l'historial del report per veure la programació.
A l'historial podem veure que el report s'ha creat i executat correctament.

Avui hem vist com podem programar reports de BO a través de la RESTful API sense dependre dels Events de BO i podent-li passar paràmetres de forma dinàmica.

Us adjunto el codi.


 package ambi;  
 import java.io.BufferedReader;  
 import java.io.DataOutputStream;  
 import java.io.FileReader;  
 import java.io.InputStreamReader;  
 import java.io.OutputStreamWriter;  
 import java.net.HttpURLConnection;  
 import java.net.URL;  
 import org.json.simple.JSONObject;  
 import org.json.simple.parser.JSONParser;  
 public class BORESTful_schedule {  
      public static void main(String[] args) throws Exception {  
           String usage = "\n\n********************************************************************************************************"  
                     + "\n* Usage:                                                *"  
                     + "\n* BORESTful_schedule 0 : Get Parameters  <reportId>                         *"  
                     + "\n* BORESTful_schedule 1 : Schedule Report <file mail> <file parameters>                *"  
                     + "\n* <file mail> :                                            *"  
                     + "\n*   Line 1 : docid                                          *"  
                     + "\n*   Line 2 : format                                         *"  
                     + "\n*   Line 3 : filename                                        *"  
                     + "\n*   Line 4 : from                                          *"  
                     + "\n*   Line 5 : to                                           *"  
                     + "\n*   Line 6 : cc                                           *"  
                     + "\n*   Line 7 : bcc                                           *"  
                     + "\n*   Line 8 : subject                                         *"  
                     + "\n*   Line 9 : message text                                      *"  
                     + "\n********************************************************************************************************"  
                     + "\n";  
           if (args.length < 2) {  
                System.err.println(usage);  
           } else {  
                BOPropertiesRest prop = new BOPropertiesRest();  
                prop.initPropValues();  
                String server;  
                String username;  
                String password;  
                String authentication;  
                server = prop.getBoCMSName();  
                username = prop.getBouser();  
                password = prop.getBopassword();  
                authentication = prop.getBoAuth();  
                String logonToken = "\"" + login(server, username, password, authentication) + "\"";  
                if (args[0].compareTo("0") == 0) {  
                     viewParameters(logonToken, server, args[1]);  
                } else if (args[0].compareTo("1") == 0) {  
                     String fileFormat = "";  
                     String fileName = "";  
                     String from = "";  
                     String to = "";  
                     String cc = "";  
                     String bcc = "";  
                     String subject = "";  
                     String textMessage = "";  
                     String docid = "";  
                     String parameters = "";  
                     BufferedReader br = new BufferedReader(new FileReader(args[1]));  
                     try {  
                          String line = br.readLine();  
                          int i = 0;  
                          while (line != null) {  
                               switch (i) {  
                               case 0:  
                                    docid = line;  
                                    break;  
                               case 1:  
                                    fileFormat = line;  
                                    break;  
                               case 2:  
                                    fileName = line;  
                                    break;  
                               case 3:  
                                    from = line;  
                                    break;  
                               case 4:  
                                    to = line;  
                                    break;  
                               case 5:  
                                    cc = line;  
                                    break;  
                               case 6:  
                                    bcc = line;  
                                    break;  
                               case 7:  
                                    subject = line;  
                                    break;  
                               default:  
                                    textMessage += "\n" + line;  
                                    break;  
                               }  
                               line = br.readLine();  
                               i++;  
                          }  
                     } finally {  
                          br.close();  
                     }  
                     BufferedReader br2 = new BufferedReader(new FileReader(args[2]));  
                     try {  
                          String line = br2.readLine();  
                          while (line != null) {  
                               parameters += line + "\n";  
                               line = br2.readLine();  
                          }  
                     } finally {  
                          br2.close();  
                     }  
                     scheduleReport(logonToken, server, fileFormat, fileName, from, to, cc, bcc, subject, textMessage, docid,  
                               parameters);  
                }  
                logoff(logonToken, server);  
           }  
      }  
      public static void scheduleReport(String logonToken, String server, String fileFormat, String fileName, String from,  
                String to, String cc, String bcc, String subject, String textMessage, String docid, String parameters)  
                          throws Exception {  
           URL url = new URL("http://" + server + ":6405/biprws/raylight/v1/documents/" + docid + "/schedules");  
           HttpURLConnection conn = (HttpURLConnection) url.openConnection();  
           conn.setRequestMethod("POST");  
           String message = "<schedule>\n" + "   <name>Schedule From Restful</name>\n" + "   <format type=\""  
                     + fileFormat + "\"/>\n" + "   <destination>\n" + "            <useSpecificName>" + fileName  
                     + " </useSpecificName>\n" + "     <mail>\n" + "       <from>" + from + "</from>\n"  
                     + "                <to>" + to + "</to>\n" + "                <cc>" + cc + "</cc>\n" + "                <bcc>" + bcc  
                     + "</bcc>\n" + "                <subject> " + subject + " </subject>\n" + "                <message> "  
                     + textMessage + " </message>\n" + "                <addAttachment>1</addAttachment>\n"  
                     + "     </mail>\n" + "   </destination>\n" + parameters + " </schedule>\n";  
           conn.setRequestProperty("Accept", "application/xml");  
           conn.setRequestProperty("X-SAP-LogonToken", logonToken);  
           conn.setDoOutput(true);  
           conn.setRequestProperty("Content-Type", "application/xml");  
           conn.setRequestProperty("Content-Length", String.valueOf(message.getBytes().length));  
           DataOutputStream out = new DataOutputStream(conn.getOutputStream());  
           out.writeBytes(message);  
           out.flush();  
           out.close();  
           if (conn.getResponseCode() != 200) {  
                throw new RuntimeException(  
                          "Failed : HTTP error code : " + conn.getResponseCode() + " " + conn.getResponseMessage());  
           }  
           conn.disconnect();  
      }  
      public static void viewParameters(String logonToken, String server, String docid) throws Exception {  
           URL url = new URL("http://" + server + ":6405/biprws/raylight/v1/documents/" + docid + "/parameters");  
           HttpURLConnection conn = (HttpURLConnection) url.openConnection();  
           conn.setRequestMethod("GET");  
           conn.setRequestProperty("Accept", "application/xml");  
           conn.setRequestProperty("X-SAP-LogonToken", logonToken);  
           conn.setDoOutput(true);  
           if (conn.getResponseCode() != 200) {  
                throw new RuntimeException(  
                          "Failed : HTTP error code : " + conn.getResponseCode() + " " + conn.getResponseMessage());  
           }  
           BufferedReader br = new BufferedReader(new InputStreamReader((conn.getInputStream())));  
           String output;  
           while ((output = br.readLine()) != null) {  
                System.out.println(output);  
           }  
           conn.disconnect();  
      }  
      public static String login(String server, String user, String password, String auth) throws Exception {  
           String logontoken = null;  
           URL url = new URL("http://" + server + ":6405/biprws/logon/long/");  
           HttpURLConnection conn = (HttpURLConnection) url.openConnection();  
           conn.setRequestMethod("POST");  
           conn.setRequestProperty("Accept", "application/json");  
           conn.setRequestProperty("Content-Type", "application/xml; charset=utf-8");  
           conn.setDoInput(true);  
           conn.setDoOutput(true);  
           String body = "<attrs xmlns=\"http://www.sap.com/rws/bip\">" + "<attr name=\"userName\" type=\"string\">" + user  
                     + "</attr>" + "<attr name=\"password\" type=\"string\">" + password + "</attr>"  
                     + "<attr name=\"auth\" type=\"string\" possibilities=\"secEnterprise,secLDAP,secWinAD\">" + auth  
                     + "</attr>" + "</attrs>";  
           int len = body.length();  
           conn.setRequestProperty("Content-Length", Integer.toString(len));  
           conn.connect();  
           OutputStreamWriter out = new OutputStreamWriter(conn.getOutputStream());  
           out.write(body, 0, len);  
           out.flush();  
           if (conn.getResponseCode() != 200) {  
                throw new RuntimeException("Failed : HTTP error code : " + conn.getResponseCode());  
           }  
           BufferedReader br = new BufferedReader(new InputStreamReader((conn.getInputStream())));  
           String jsontxt = br.readLine();  
           JSONParser parser = new JSONParser();  
           JSONObject json = (JSONObject) parser.parse(jsontxt);  
           logontoken = (String) json.get("logonToken");  
           conn.disconnect();  
           return logontoken;  
      }  
      public static void logoff(String logonToken, String server) throws Exception {  
           URL url = new URL("http://" + server + ":6405/biprws/logoff");  
           HttpURLConnection conn = (HttpURLConnection) url.openConnection();  
           conn.setRequestMethod("POST");  
           conn.setRequestProperty("Accept", "application/xml");  
           conn.setRequestProperty("X-SAP-LogonToken", logonToken);  
           conn.setRequestProperty("Content-Type", "application/xml; charset=utf-8");  
           conn.setDoInput(true);  
           conn.setDoOutput(true);  
           conn.connect();  
           OutputStreamWriter out = new OutputStreamWriter(conn.getOutputStream());  
           out.flush();  
           if (conn.getResponseCode() != 200) {  
                throw new RuntimeException("Failed : HTTP error code : " + conn.getResponseCode());  
           }  
      }  
 }