UGISS User Group Italiano SQL Server
Scenario
L’azienda XYZ vuole automatizzare l’invio a ciascun agente di reports periodici con l’elenco degli ordini raccolti e le provvigioni spettanti. I reports sono generati tramite SQL Server Reporting Services ed accedono ad un database SQL Server. L’invio dei reports avviene tramite email.
Soluzione
La soluzione proposta utilizza SQL Server Integration Services, che si occupa di reperire le informazioni sugli agenti (Codice, Cognome e Nome, Email, Provvigione %), di lanciare un custom task che genera il report in formato pdf e lo memorizza nella cartella dell’agente ed infine di inviare per email il report prodotto a ciascun agente.
Vediamo la soluzione in dettaglio.
Come prerequisito per far funzionare il package occorre scaricare da Codeplex ed installare il custom task che si occupa di interfacciare Reporting Services. Si chiama SSIS ReportGenerator Task (http://reportgeneratortask.codeplex.com/) ed annovera tra le sue funzionalità la possibilità di fare il rendering di un report locale (utilizzando le informazioni di un dataset SSRS) o di collegarsi ad un server Reporting Services. Il report può essere salvato con il prefisso composto dalla data ed ora di esecuzione, ma sè possibile intervenire attraverso le espressioni per modificarne il nome.
Va detto che è un progetto ancora in beta, ma per quel che ho potuto verificare funziona già abbastanza bene.
La soluzione si compone di due progetti: il report da produrre ed il package Integration Services che svolge il lavoro di automazione. Il progetto Reporting Services è molto semplice ed è composto di un data source e di un report. Il report attinge da un datasource generato tramite la query:
–C.FirstName + ‘ ‘ + C.LastName AS FullName,
SOH.SalesOrderNumber,
CU.FirstName + ‘ ‘ + CU.LastName AS Customer,
OrderDate,
ShipDate,
TotalDue,
SP.CommissionPct,
TotalDue*SP.CommissionPct AS Commissions
FROM Sales.SalesOrderHeader SOH
INNER JOIN HumanResources.Employee E on E.EmployeeID = SOH.SalesPersonID
INNER JOIN Sales.SalesPerson SP ON SP.SalesPersonID = SOH.SalesPersonID
INNER JOIN Person.Contact C ON C.ContactID = E.ContactID
INNEr JOIN Person.Contact CU ON CU.ContactID = SOH.CustomerID
WHERE E.EmployeeID = @SalesPersonID
che si occupa di reperire dal database AdventureWorks le informazioni principali dell’ordine e delle provvigioni applicate all’agente, passato come argomento al report (@SalesPersonID).
Il risultato dell’esecuzione del report è il seguente:
La parte più interessante della soluzione è però rappresentata dal progetto SSIS ed in particolare dall’utilizzo del custom task che si occupa di generare il report.
Il package si compone di quattro task. Il primo in ordine di esecuzione è un Execute SQL Task che si occupa di lanciare la query sul database AdventureWorks per reperire l’elenco degli agenti e i relativi indirizzi di posta elettronica per il successivo invio dei reports. Particolarità di questo task è la memorizzazione del risultato della query in una variabile di tipo Object. Per fare questo nella sezione General è stata impostata la proprietà Resultset a "Full resultset" e nella sezione Resultset è stato associato alla variabile "ElencoAgentiDataset" il risultato della query. Va notato che la proprietà Result name deve essere impostata al valore 0 perchè SSIS memorizzi correttamente il resultset.
Gli altri task sono racchiusi in un For Each Loop Container, che si occupa di estrarre da ciascuna riga presente nel dataset generato dal task precedente i dati dell’agente e memorizzarli in altrettante variabili.
Lo script task, che viene eseguito per primo ad ogni ciclo, si occupa di creare la cartella di destinazione (che sarò uguale al nome dell’agente) e di memorizzare in una variabile il codice dell’agente ed il path completo del file PDF dove sarà memorizzato il report. La memorizzazione del codice dell’impiegato è in realtà un barbatrucco 🙂 Se si utilizza infatti la variabile scritta dal For Each Loop container il task che genera il report non riesce ad accedervi per problemi di locking, bypassati utilizzando una seconda variabile che memorizza il valore del codice agente.
Il Report Generator Task è molto semplice da utilizzare: per prima cosa si sceglie se utilizzare un report server o un dataset presente su un report locale. Nel nostro caso utilizziamo un report server e quindi dobbiamo indicarne l’URL (http://localhost/reportserver). Quindi specifichiamo quale report vogliamo eseguire indicandone il path nella casella Report o scegliendolo dalla treeview che viene visualizzata cliccando sul pulsante a fianco.
Il parametro Save as consente di indicare il nome del file .pdf ed è possibile scegliere se far precedere il nome dalla data di generazione del report mediante la checkbox "Add date as prefix to filename" oppure fornire il nome di una variabile che conterrà il valore del prefisso da aggiungere in testa al nome del file.
E’ anche possibile visualizzare un anteprima del report e creare uno snapshot (SSRS si memorizza i dati con i quali ha generato il report per poter riprodurre lo stesso report anche a distanza di tempo).
Nella sezione Report parameter possiamo indicare tutti i parametri del report e specificare la variabile che ne contiene il valore. Nel nostro caso abbiamo utilizzato EmpID, cioè il codice dell’agente.
Per ultimo abbiamo inserito un Email Task che si occupa di inviare il report all’agente, utilizzando come parametri le variabili che contengono l’indirizzo email ed il nome del file generato, entrambi assegnati mediante espressioni.
Il codice di esempio dell’intera soluzione è disponibile qui.
Conclusioni
Questo è un esempio di come sia possibile utilizzare gli strumenti messi a disposizione da SQL Server per creare soluzioni complesse di automazione. Un ulteriore miglioramento potrebbe essere quello di verificare la dimensione del report prodotto e, se supera una dimensione di soglia, prevederne l’invio a mezzo FTP, in modo da evitare le costrizioni cui solitamente sono sottoposti files molto grandi nell’invio come allegati a messaggi di posta elettronica.
