dilluns, 18 de desembre de 2017

Cursors SQL Server

Avui un post senzill. Com declarar i utilitzar un cursor en SQL Server controlant errors, evitant que es quedi memòria assignada, etc.

--Variable estat del cursor
declare @cur_status int

--variables on assignarem els resultats del cursor
declare @a int
-- ..
-- ..

-- Si el cursor no existeix el creem
SELECT @cur_status=CURSOR_STATUS('global','cur')

if @cur_status=-3 begin
    --declarem el cursor
    declare cur cursor for
    -- query SQL
    select 1 as a union all select 5

    --Si el cursor no està obert, l'obrim
    SELECT @cur_status=CURSOR_STATUS('global','cur')
    if @cur_status=-1
        OPEN cur

    -- assignem les variables al cursor
    FETCH cur INTO @a --,@b, ..., @z
    --Mentre hi hagi files al cursor
    WHILE (@@FETCH_STATUS = 0)
    BEGIN   
        -- codi a executar per cada fila del cursor
        print @a
        -- assignem les següents variables al cursor
        FETCH cur INTO @a --,@b, ..., @z
    END --final del bucle

    --Si el cursor està obert, el tanquem
    SELECT @cur_status=CURSOR_STATUS('global','cur')
    if @cur_status>=0
        close cur

    --Si el cursor està assignat, el desassignem per deixar net l'stack de variables
    SELECT @cur_status=CURSOR_STATUS('global','cur')
    if @cur_status<>-3
        deallocate cur

end
else
    print 'El cursor ja existeix '


Amb aquesta estructura d'script podem crear i utilitzar un cursor en SQL Server validant que no existeixi, obrint-lo només si fa falta i assegurant-nos que el tanquem i dessasignem per evitar futurs errors al reobrir-lo i evitant que es quedi memòria reservada que no utilitzem.

dijous, 7 de desembre de 2017

Windows authentication en SQL Server

Avui va un post no estrictament de SQL Server.
M'he trobat vegades que necessito entrar al SQL Server a través de Windows Authentication per que no estan activats, o no es volen activar, els logins de SQL Server, i la màquina des de la que vull entrar no està dins de domini.
Per aquests casos es pot executar la comanda runas de CMD.
Serveix tant per el SSMS, SSDT, Excel per connectar-nos a SSAS o MDS.

Les comandes a executar serien del tipus:
SSMS
runas /netonly /user:domini\usuari "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
SSDT
 runas /netonly /user:domini\usuari "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe"

 
Un cop executada la comanda ens demanarà el password, i ja haurem entrat al programa autenticats amb l'usuari de Windows indicat.
S'ha de tenir en compte que l'usuari que apareix al SSMS segueix sent l'original en el que estem a la màquina, però internament té l'usuari que li hem indicat a la comanda.

 

dimarts, 21 de novembre de 2017

Guardar SP d'SQL Server en una taula

A vegades ens trobem amb Stored Procedures d'SQL Server que ens retornen dades en format taula i ens agradaria poder guardar-los en una taula. La forma d'invocar un procediment i que el guardi en una taula és el següent:
insert into #la_nostra_taula EXEC sp_spaceused 'Taula'

La funció sp_spaceused és molt útil per que ens dóna informació sobre l'espai que ocupa una taula.


Però si volem saber aquesta informació de totes les taules hauríem d'invocar el procediment per cadascuna d'elles per separat. Si ho fem des del SSMS el resultat no és còmode de tractar.


En aquest cas serà útil invocar la funció per cada taula i guardar-lo en una taula final per després consultar-la.

Primer crearem una taula temporal per guardar les dades de la funció sp_spaceused. La funció sp_spaceused retorna varchars, però per nosaltres és més còmode tenir les dades en format nuèric per poder-les aggregar. Aleshores crearem una taula final amb els mateixos valors en format int.


create table #TaulaTemporalSpaceUSed (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
       
create table TaulaFinalMida (
    Name varchar(255),
    [rows] int,
    reservedKb int,
    dataKb int,
    reservedIndexSize int,
    reservedUnused int)

   
   
Per no haver d'executar la funció sp_spaceused per cada taula manualment executarem el procediment sp_MSforeachtable que recorre totes les taules de la BBDD on estem.
   
EXEC sp_MSforeachtable @command1="insert into #TaulaTemporalSpaceUSed
EXEC sp_spaceused '?'"
insert into TaulaFinalMida (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TaulaTemporalSpaceUSed

select * from TaulaFinalMida
order by reservedKb desc

drop table #TaulaTemporalSpaceUSed





Amb aquest script podem extreure de forma eficient i simple l'espai ocupat per les taules d'una BBDD. Si hi afegim el procediment sp_MSforeachdb podem recòrrer totes les BBDD i executant l'script anterior podrem etreure l'espai ocupat per totes les taules de totes les BBDD.

dilluns, 18 de setembre de 2017

MDS: problema models sense accés

Tornem amb MDS.
Recentment m'he trobat amb un problema amb els permisos. Quan es crea un model, per defecte, només té permisos la persona/grup que l'ha creat. Si s'esborra el grup ningú té accés a aquest model i queda penjat.
Hi ha una forma de recuperar-ho per la porta del darrera, a través de la BBDD MDS.
La taula que té els models és: [mdm].[tblModel]. D'aquí extraurem l'id del model que volem recuperar permisos.
Amb la consulta:
    select *
    from [mdm].[tblSecurityRole]
    where name like 'Role for UserAccount %\<<usuari>>'

    obtindrem l'id de l'usuari al que volem donar permisos.
La taula amb els usuaris és: [mdm].[tblUser]
La taula on s'ha de fer insert és: [mdm].[tblSecurityRoleAccess]

L'insert ha de ser del tipus:
    insert into [mdm].[tblSecurityRoleAccess]
    (Role_ID, Privilege_ID, Model_ID, Securable_ID, Object_ID, Description, Status_ID, EnterDTM, EnterUserID, LastChgDTM, LastChgUserID, MUID)
    values
    (<<ROLE_ID>>, 2, <<MODEL_ID>>, <<MODEL_ID>>, 1, <<Nom model>> (Update), 1, getdate(), <<USER_ID>, getdate(),<<USER_UD>>, newid())


Un cop fet aquest insert l'usuari ja tindrà accés al model penjat.
Crec que és un error del sistema el fet que no hi hagi un superusuari que tingui accés a tots els models i que hi hagi la possiblitat que quedin models penjats.
També és un error (subsanable amb permisos de BBDD) que es puguin fer inserts directament a les taules de sistema, sobretot tenint en compte que preten ser un sistema amb auditoría de canvis.
   

dissabte, 22 de juliol de 2017

SQL Server 2017 RC1

Ja la tenim aquí!!
Per fi ha sortit la RC1 d'SQL Server 2017.
Amb un parell de mesos de retard respecte els rumors.

Aquí teniu la notícia oficial:
 https://blogs.technet.microsoft.com/dataplatforminsider/2017/07/17/first-release-candidate-of-sql-server-2017-now-available/

Ara toca probar el rendiment amb servidor Windows Server vs servidor Linux. És probable que ens toqui migrar de servidor de bbdd i de SO a la vegada. Diversió assegurada...

Aquest post és més curt que estic de vacances sense ordinador.

dijous, 29 de juny de 2017

Control de versions SSDT 2013 amb TFS

Avui veurem com configuar una connexió TFS per al SSDT 2013 per a poder tenir versionat del nostre codi.
Per poder utilitzar tfs ens hem de baiar el fitxer vs_teamExplorer.exe
Últimament costa bastant de trobar, adjunto link que espero que no deprequin:
http://download.microsoft.com/download/9/B/4/9B44FFC8-64C2-4091-9028-6E8550E9C96E/vs_teamExplorer.exe
 En SSDT 2015 el plug-in ja ve instal·lat per defecte.

Un cop tenim el plug-in de TFS podem anar al menú Team --> Connect to Team Foundation Server…

dilluns, 29 de maig de 2017

Modificar project.Params dinàmicament desde SSDT


A partir de SSIS 2012 tenim l'opció de treballar en package deploymen o project deploymnent. Si treballem en project deployment tenim un fitxer Project.prarams non és una bona pràctica tenir mapejades les connexions que tenim, com poden ser les de BBDD. Per temes de seguretat també és bona pràctica que les connexions siguin per Windows Authentication, sobretot un cop pujades al servidor. El que ens passa a vegades és que quan desenvolupem el nostre usuari no té permisos de Windows Authentication i ens hem de logar a la BBDD per usuari i password d'SQL Server. Quan tenim aquest cas hem de modificar a mà cadascun dels paràmetres.

Avui veurem una manera d'automatitzar la transformació del fitxer de paràmetres de Windows Authentication a login d'SQL Server i a la inversa. Per fer-ho farem servir PowerShell.

Abans de poder utilitzar PowerShell necessitarem habilitar els permisos, sinó ens pot donar un error del tipus:
    ./XXXXXXXXXXXX.ps1 : No se puede cargar el archivo C:\Temp\XXXXXXXXXXXX.ps1 porque la ejecución de scripts está
    deshabilitada en este sistema. Para obtener más información, vea el tema about_Execution_Policies en
    http://go.microsoft.com/fwlink/?LinkID=135170.
    En línea: 1 Carácter: 1
    + ./XXXXXXXXXXXX.ps1
    + ~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : SecurityError: (:) [], PSSecurityException
        + FullyQualifiedErrorId : UnauthorizedAccess



Per evitar aquest error hem d'executar: Set-ExecutionPolicy Unrestricted
Aquesta comanda s'ha d'executar com a administrador en el PowerShell de 32-bits (per a que funcioni des del SSDT). Jo recomano fer-ho tant en el de 32-bits com el de 64-bits.


Un cop tenim els permisos assignats ja podem començar a fer els scripts. Farem primer un script en PowerShell per a fer la modificació del contingut del fitxer project.Params, després invocarem aquest fitxer des d'un bat per poder-li invocar desde SSDT i finalment configurarem l'excució del .bat com a external tool.

SCRIPT POWERSHELL

 param (
    [Parameter(Mandatory=$true)][string]$path,
    [Parameter(Mandatory=$true)][string]$username,
    [Parameter(Mandatory=$true)][string]$password
 )
$strReplace = "User ID="+$username+";Password="+$password+";"
(Get-Content $path\Project.params) | ForEach-Object { $_ -replace "Integrated Security=SSPI;", $strReplace } | Set-Content $path\Project.params



A aquest script li passem 3 paràmetres: path on està el fitxer Project.params, username d'SQLServer i password.
El que fa l'script és agafar tots els fitxers Project.params del path indicat (només n'hi haurà 1) i per cadascun d'ells substitueix Integrated Security=SSPI; per User ID=<username>;Password=<password>. Finalment bolca el nou contingut altre cop a Project.params

Aquest és l'script per canviar de Windows Authentication a SQL Server login, per al cas invers només cal canviar la darrera fila per:
(Get-Content $path\Project.params) | ForEach-Object { $_ -replace $strReplace, "Integrated Security=SSPI;" } | Set-Content $path\Project.params


SCRIPT BAT
Suposem que el nostre script de powershell es diu: c:/temp/fromWinToUser.ps1

powershell c:/temp/fromWinToUser.ps1 %1 %2 %3 %4 %5 %6


Els paràmetres són els següents:
%1 = -path
%2 = path del fitxer Project.params
%3 = -username
%4 = username SQL Server
%5 = -password
%6 = password SQL Server


Aquest bat ens servirà per poder invocar el PowerShell a través del SSDT

EXECUCIÓ DESDE SSDT
Per executar la comanda des de SSDT anirem a Tools --> External Tools... i n'afegirem una de nova amb les següents característiques:
Title: FromWinToUser
Command: <path del bat>\fromWintoUser.bat
Arguments: -path $(ProjectDir) -username <username> -password <password>

Cal marcar el check "Prompt for arguments" i és aconsellable marcar també "Use Output Window".


Per afegir el pas fromUserToWin s'ha de fer exactament el mateix apuntant a l'altre bat.

Un cop ho tenim configurat ja ens apareix al menú de Tools. Quan ho executem ens apareix la pantalla per introduir els paràmetres. Ho modifiquem amb l'userId i password d'SQL Server i executem.

 Si tot ha anat correcte SSDT ens informarà que el fitxer ha estat modificat des d'una app externa i si el volem sobreescriure.

Quan acabem hem d'executar la funció inversa per retornar les connexions a Windows Authentication.

Hem d'anar amb compte si el path on està el fitxer Project.params té espais, és possible que els paràmetres passats al bat no els agafi correctament.

dilluns, 15 de maig de 2017

Generar ISPAC des de línia de comandes

En SSIS quan treballem en projecte deployment per poder pujar un projecte a producció és necessari fer un Deploy del projecte. El deploy el podem fer des del SSDT (botó dret al nom del projecte --> deploy).


Quan fem aquest click internament s'està generant un fitxer .ispac que conté la informació del projecte (dtsx, params, manifest). Aquest fitxer ispac si no toquem res estarà a dins de la carpeta del nostre projecte a la subcarpeta /bin/development. Si l'obrim amb un descopresor tipus 7-zip podrem veure els fitxers a dins. Un cop generat l'ispac se'ns obre un wizard per poder deployar el projecte a un servidor. Hem de tenir en compte que només es pot deployar si l'usuari amb el que ens hem logat a la màquina té permís sobre el servidor. En el post http://www.eljordifabi.tech/2017/04/desplegament-automatic-de-paquets-ssis.html podeu veure alternatives de desplegament.

dilluns, 1 de maig de 2017

SQL Server 2017

Microsoft ja té preparada la CTP 2 del SQL Server 2017. Saltan-se la freqüència de 2 anys desde la versió 2008, aquest cop només passarà un any entre una versió i una altra.
La podeu descarregar aquí: https://www.microsoft.com/en-us/sql-server/sql-server-2017
i aquí teniu el datasheet: download.microsoft.com/download/F/9/A/F9A1B5AA-D57C-4B4D-9C3E-715B800B0419/SQL_Server_2017_Datasheet.pdf
Es rumoreja que el 31 de maig sortirà ja la versió RTM.

Quines són les principals novetats?
- Poder programar en Python dins del SQL Server. A la versió 2016 ja van introduir l'R i ara com a novetat en l'apartat d'anàlisi introduieixen també Python. Els científics de dades estaran contents (https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/python-in-sql-server-2017-enhanced-in-database-machine-learning/)
- Millora dels backups, tant els diferencials com els de log. Serà de molta ajuda per als DBAs.
- Select  into ... on filegroup. Es podrà indicar a quin filegroup anirà una taula creada amb la sentència Select into .
- Millores en la tempdb. A SQL Server 2016 ja s'havien aplicat millores a la tempdb, Microsoft decideix seguir pel mateix camí a SQL Server 2017.
- Versió per a Linux. Aquest cop les versions per Windows i Linux sembla sortiran a la vegada, una aposta clara de Microsoft cap a servidors amb SO Linux.

dilluns, 24 d’abril de 2017

Desplegament automàtic de paquets SSIS

Avui veurem un sistema per pujar ETLs en la versió SQL Server 2014 que estiguin en project deployment a través de sentències SQL.
Aquest sistema és molt més ràpid que el deploy de projectes a través de SSDT, sobretot quan hi ha molts projectes ja desplegats.

També permet no haver de donar permisos de despegament de projectes a tots els developers ja que l'usuari que desplegarà quedarà encapsulat en el job d'SQL.

El procediment funcionarà de la següent manera:
Tindrem una carpeta on es deixaran els .ispac a desplegar i els .sql amb el codi de modificació de BBDD i amb les configuracions d'environments. Aquesta carpeta serà accessible des del servidor d'SQL Server.
Tindrem una taula on registrarem els .ispac que s'han de desplegar, juntament amb scripts d'alter de BBDD i configruació d'environments.
Hi haurà un job d'SQL Server que s'executarà com un daemon i anirà mirant la taula per si hi ha projectes pendents de desplegar.
Per cada ispac s'executarà el procediment i registrarà si ha acabat correctament o no.

dilluns, 10 d’abril de 2017

Master Data Services (V)

Avui veurem una característica de MDS que ens pot ser útil quan volen guardar fotos inamobibles: el versionat. Podem generar versions dels models per tal i que es quedin en estat només lectura i modificar altres versions.
Si volem crear una nova versió ho hem de fer des del navegador a l'opció Version Managment
Per poder veure aquest apartat hem de tenir permisos de Version Management (menú User and Group Permissions).
Dins de Version Management triarem el model a versionar.
 En aquest punt ens hem de fixar en 2 columnes:
- Status: ens indica si la versió és modificable.
- Validation: ens indica si la versió està validada. Validada significa que s'ha passat el test de validació per que compleixi totes les regles que hem definit.

Per poder validar una versió ha d'estar bloquejada (Lock). L'status canvia a Locked.
Des del menú Validate Version podem validar-la si no ho està, i un cop validada fer Commit.
Un cop fer el commit ja no podrem modificar la versió. El que sí que podrem fer és copiar-la.
Aquesta còpia serà la que podrem modificar. A la taula ens indica a partir de quina versió s'ha creat
Podem canviar el nom de la versió fent doble-click a la columna Name de la versió a reanomenar.

Això és tot sobre les versions. Sobretot molt útils quan hem de deixar dades a la força de només lectura.

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());  
           }  
      }  
 }  

dilluns, 27 de febrer de 2017

Master Data Services (IV)

Després de veure diferents maneres de carregar les taules creades amb el Master Data Services avui veurem algunes consultes útils sobre la metadata.

Quan creem una nova entitat del MDS ens crea una taula dins de la BBDD de MDS, però no són noms intuïtius com el de les taules stag.


La 1a consulta serveix per saber els models creats, la data de creació i modificació i quina és la seva versió activa.
select
 m.id, m.Name, m.EnterDTM, m.EnterUserID, m.LastChgDTM, m.LastChgUserID,
mv.id as idLastVersion
from
mdm.tblModel m
join (select Model_ID, id from [mdm].[tblModelVersion] where status_id=1) mv on m.id=mv.Model_ID

 La 2a consulta serveix per saber les taules d'un model i entitat.

select
e.id, e.Name, e.EntityTable, e.SecurityTable,  e.StagingBase
from
mdm.tblModel m
join [mdm].[tblEntity] e on e.Model_ID=m.ID
where m.Name='Control de gestión'


Un cop tenim les taules veiem que els seus atributs tampoc tenen un nom amigable.

 Amb la 3a consulta podem veure el mapeig entre els noms de les columnes de les taules amb els noms que hem definit nosaltres al MDS.

select
e.id, e.Name,
a.id, a.MemberType_ID, a.DisplayName, a.TableColumn
from mdm.tblModel m
join [mdm].[tblEntity] e on e.Model_ID=m.ID
join [mdm].[tblAttribute] a on a.Entity_ID=e.ID
join (select Model_ID, id from [mdm].[tblModelVersion] where status_id=1) mv on m.id=mv.Model_ID
where m.Name='Control de gestión'
and e.name='Vuelos'
Amb aquestes 3 consultes podem extreure la metadata bàsica per poder atacar a les taules creades pel MDS.

MDS té la capacitat d'auditar els canvis de dades fets. Per poder consultar-los podem executar la query:
SELECT *
FROM [MDS].[mdm].[tblTransaction]
where Version_ID=5
and Entity_ID=12
and Attribute_ID=360
order by lastChgDTM 

On Version_ID és la versió a consultar. Entity_ID és l'ID retornat de la 2a consulta i Attribute_ID és l'ID retornat a la 3a consulta. En aquesta taula hi ha les columnes OldValue i NewValue per veure com han canviat els valors. Per veure el canvi en una fila concreta podem filtrar per la columna MemberCode.
En aquesta taula també es guarda l'id de l'usuari que ho ha modificat i que es pot creuar amb la taula [mdm].[tblUser] . Ara ja tenim als usuaris controlats i ja no podran dir que no han tocat res ;)

Avui hem vist les principals taules de metadata del MDS. En propers posts veurem la gestió de Versions i regles.

dilluns, 13 de febrer de 2017

Master Data Services (III)

En l'anterior post vam veure com crear Models, Entitats i Atributs en MDS. Avui veurem com carregar les dades.
Hi ha 3 maneres de carregar les dades:
La primera és des de la interfície web d'MDS a través de l'Explorer.

A part de carregar dades, podrem veure les que ja hi ha al sistema.
És una forma senzilla de carregar dades, no necessites tenir cap producte instal·lat (excepte Silverlight), però has de carregar les files una per una.

La segona manera és a través de BBDD. Quan es crea una entitat es crea a la vegada una taula a l'esquema stg de la BBDD MDS amb les mateixes columnes (i mateix nom) que els atributs de l'entitat. Hi ha unes columnes extres importants:
- ImportType: indica l'acció a realitzar a les files de la taula (inserir, esborrar, desactivar...)
- BatchTag: agrupa un conjunt de files a processar de cop

Un cop carregada la taula executarem el stored procedure stg.udp_<nom taula stg> per processar les files. Els paràmetres del procediment són:
- VersionName: Versió sobre la que s'aplicaran els canvis.
- LogFlag: si els canvis es guardaran a la taula de log.
- BatchTag: el tag de dades a processar.

Us adjunto els links amb tota la documentació:



Un cop carregades les dades es poden consultar amb l'Explorer per veure si s'han carregat correctament. 

Aquest mètode de càrrega de dades és menys intuïtiu, però permet fer càrregues massives de dades de forma senzilla.

La tercera manera, i la preferida pels usuaris, és a través de l'add-in de l'Excel.
Des de la plana principal de MDS hi ha un link per baixar-vos l'add-in. Un cop instal·lat us apareixerà com a una nova pestanya al ribbon.
Amb l'addin ens podrem connectar a MDS (amb la mateixa URL amb la que ens connectem via web), triar un model i versió, i importar una entitat.
Quan ens connectem a l'add-in no ens deixa triar ni tipus d'autenticació, ni usuari. Utilitza l'usuari de la màquina que ha obert l'Excel. Si ens volem impersonar ho podem fer a través de la comanda runas de cmd. Per exemple: 
runas /netonly /user:sql2014biml\jordi.isidro "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE"

Un cop importades les dades les podrem modificar o afegir i, un cop acabat, clicar a Publish per pujar-les a MDS. Pes esborrar s'ha de seleccionar tota la fila sencera i clicar a Delete.
L'acció delete s'executa internament fila per fila. Si esborrem moltes files l'acció pot ser lenta.


MDS no està pensat per grans volums de dades (centenars de milers de files) i el seu rendiment baixa molt en aquests casos, sobretot a l'importar les dades des de l'add-in o al publicar moltes files a la vegada.

El proper dia veurem consultes útils per saber quines estructures ens ha creat internament el MDS quan hem creat Models, Entitats i Atributs.