SQL Server 2005 Integration Services

  • Articolo

Di Davide Mauri - Microsoft MVP

Una cosa che ormai dovrebbe essere chiara a tutti, e che io stesso ho detto e ridetto più volte, è che SQL Server non è solo un database engine, ma un’intera piattaforma per la gestione e lo sviluppo di soluzioni data-centriche.

Una piattaforma di questo tipo ha evidentemente bisogno, per essere completa, di un set di strumenti atti a facilitare e a permettere l’integrazione della stessa in un ambiente in cui i dati sono eventualmente già presenti, memorizzati in diverse forme, magari in modo disomogeneo, ma che devono comunque essere gestiti dalla piattaforma stessa.

Oltre a questo è altresì necessario, soprattutto oggi in cui il termine “Business Intelligence” è una parola chiave del mercato, avere a disposizione degli strumenti adatti alla gestione ed all’alimentazione di datawarehouse, con la possibilità di poter caricare e manipolare dati provenienti dalle sorgenti più disparate.

Sin dalla versione di 7.0 SQL Server tali strumenti – i DTS, Data Transformation Services – sono stati stati forniti insieme al database server. Con la versione 2000 sono maturati e sono giunti all’apice delle possibilità offerte da quell’architettura e sono quindi giunti alla fine del loro ciclo di vita.

Con la versione 2005 di SQL Server i DTS vengono sostituiti dai SQL Server Integration Services (SSIS per gli amici) che superano tutti i limiti insiti nei DTS e forniscono una solida ed espandibile piattaforma per soddisfare esigenze di integrazione e ed ETL (Extract, transform & Load) presenti e future.

Integration Services: quando e perché

L’utilizzo dei DTS prima e dei SSIS ora è sempre stato associato alla creazione ed alla manutenzione di un datawarehouse. Benchè questo sia sicuramente uno dei maggiori utilizzi di una piattaforma ETL come quella fornita da SQL Server con gli Integration Services, non è e non deve essere l’unico.

I SSIS trovano ampio spazio anche in ogni situazione in cui sia necessario dover gestire problematiche di importazione / esportazione (ad esempio da/verso Excel), oppure di integrazione con altre soluzioni ed altre piattaforme. Non di rado mi è capitato di usarli per integrare i dati presenti in due gestionali differenti, per poter importare dati da sorgenti legacy come AS400 oppure da semplici file di testo.

In ognuno di questi casi si sarebbe potuto optare per la creazione di un’applicazione ad-hoc (come si è quasi sempre fatto in passato), ma un’applicazione di questo tipo diventa difficile da mantenere aggiornata ed al passo con le modifiche che possono essere apportate alle sorgenti dati coinvolte in queste operazioni. Oltre a questo è davvero ardo gestire tutte le eccezioni e tutte le trasformazioni che tipicamente sono necessarie per poter adattare i dati posti in un certo contenitore con una certa forma (ed es. foglio EXCEL con 3 pagine e 30 colonne) ad un altro contenitore e ad un’altra forma (ad es. un database con 5 tabelle e 20 colonne).

I SSIS permettono di gestire tutta complessità in modo molto semplice, ed inoltre permettono di focalizzarsi al 100% sul problema da risolvere. Nel caso in cui si volesse creare un’applicazione ad-hoc piuttosto che un package, molto del nostro tempo verrebbe speso nella scrittura dell’applicazione, nell’implementazione di funzionalità di logging e nella definizione di un architettura abbastanza flessibile da poter accettare alcune modifiche senza dover essere necessariamente ricompilata tutte le volte. Oltre a questo, se volessimo usare appieno tutta la potenza dei server attuali (e dovremmo farlo, altrimenti useremmo solamente una piccola parte delle capacità di elaborazione delle nostre macchine) dovremmo anche preoccuparci di scrivere applicazioni multithread, capaci di scalare in modo efficiente ed utilizzare in modo furbo la memoria a disposizione.

Insomma, un lavoro non da poco. Con i SSIS tutto questa infrastruttura (scalabile, configurabile, flessibile) viene già fornita, dobbiamo solo usarla; ed a questo punto conviene usarla ovunque sia possibile e cioè in praticamente tutte le situazioni in cui si ha la necessità di integrare, di implementare operazioni di ETL o di effettuare operazioni di importazione ed esportazione in modalità batch (ma non solo, come vedremo nella sezione dedicata all’esecuzione dei package).

 

Sviluppo di packages

A differenza di quanto accadeva con i DTS, i SSIS vengono sviluppati da un tool totalmente esterno a SQL Server, ossia il Business Intelligence Studio, che altri non è che Visual Studio 2005.

Per creare un nuovo package (che è l’unità minima di distribuzione ed esecuzione di una soluzione basata sui SSIS) è necessario creare un progetto “Integration Services Project”, che si trova sotto la voce “Business Intelligence Project” direttamente da Visual Studio 2005:

Nel caso in cui sulla macchina abbiate già installato una versione di Visual Studio 2005, questo verrà semplicemente esteso ed abilitato allo sviluppo dei progetti di tipo “Business Intelligence”; se invece Visual Studio 2005 non è presente verrà installato automaticamente, e sarà possibile creare ed editare solo progetti del tipo suddetto e pertanto la lista dei “Project Types” sarà molto più corta di quella mostrata nell’immagine riportata in precedenza.

Una volta selezionato il progetto, verrà creata una vera e propria soluzione (esattamente come accade per le “classiche” soluzioni di Visual Studio, ad esempio applicazioni Winform o Web).

Sin da questo punto iniziale ci sono grosse ed importanti novità: è ora finalmente possibile (grazie al fatto che lo sviluppo dei package è completamente fatto tramite Visual Studio), mettere soluzioni e progetti dei SSIS all’interno di un sistema per il controllo del codice sorgente, così da facilitare il lavoro in gruppo ed avere una garanzia di accessibilità a versioni precedenti dei package stessi.

Elementi di un package

Un package è composto da diversi elementi, ed ogni elemento ha un propria collocazione anche a livello di editor grafico. A differenza dei DTS in cui era presente una sola “superficie” di sviluppo, nella quale venivano posizionati tutti gli elementi che andavano a comporre il package, con i SSIS abbiamo ben quattro (tre più una per essere precisi) diverse aree dedicate a diverse componenti:

  1. Connection Manager

  2. Control Flow + Event Handlers

  3. Data Flow

La sezione dedicata ai Connection Manager permette di disporre in modo ordinato e facilmente accessibile tutte le connessioni a risorse esterne al package che saranno utilizzate nello stesso.

E’ bene sottolineare che qualsiasi accesso ad un risorsa esterna al package (FTP, SMTP, File) necessita di un connection manager dedicato. Oltre a questi tipi di connessioni ci sono le “solite note” per l’accesso ai dati: ODBC, OLEDB, EXCEL, FLAT File, ecc.

Il Control Flow è la superficie dove viene disegnato il flusso di lavoro che il pacchetto deve eseguire. Il flusso è implementato attraverso l’utilizzo di task, che rappresentano la minima unita di lavoro all’interno di un package. Ogni task può essere collegato ad altri attraverso l’uso di constraint (rappresentati da frecce che indicano l’orientamento del flusso di lavoro).

Una grossa novità, introdotta dai nuovi componenti chiamati container, è la possibilità di gestire con molta semplicità cicli di tipo for…next e foreach.

L’area dedicata allo sviluppo della fase di data pump, ossia l’estrazione, la trasformazione ed il caricamento dei dati da una sorgente dati (anche non relazionale) ad un’altra, è l’area chiamata “Data Flow”.

Anche in questo caso il Data Flow si costruisce visualmente (a differenza di quanto accadeva con i DTS), utilizzando gli oggetti Transformation. Ognuno di questi oggetti può essere collegato con altri al fine di poter creare trasformazioni anche complesse senza dover necessariamente scrivere codice.

Attorno a queste funzionalità c’è la sezione dedicata alla gestione degli eventi. Tramite un Event Handler, ad esempio, è possibile definire in modo generalizzato per tutto il package come questo si deve comportare nel caso incontrasse un errore durante l’esecuzione del control flow o dei data flow.

Il Control Flow

Il control flow permette di definire il comportamento, la logica e le condizioni secondo le quali il package andrà ad operare.

E’, nella sostanza, una sorta di editor visuale del processo che è necessario definire per effettuare le operazioni sui dati che si devono implementare. Le operazioni sono rappresentate da task, che sono semplici unità di lavoro. Ci sono numerosi tipi di task già presenti nativamente, e sono normalmente sufficienti per coprire le normali necessità di base ed oltre.

Per fare qualche esempio, esistono task per:

  • effettuare download / upload di file via FTP

  • operare sul file system

  • inviare email

  • eseguire codice SQL (non solo su SQL Server, ma su qualsiasi database supportato dai connection manager)

  • eseguire un data flow

  • eseguire un’applicazione o un comando del sistema operativo

com’è possibile notare, già solamente di task citati coprono molte delle necessità tipiche di un processo di ETL o di integrazione; nel caso però ci fosse bisogno di qualcosa in più è possibile sfruttare lo “Script Task” che mette a disposizione Visual Basic .NET (con tanto di editor evoluto, ossia Visual Studio for Applications, completo di intellisense e syntax-highlighting) per l’implementazione della logica desiderata tramite l’impiego della potenza e della flessibilità .NET Framework.

La sequenza con cui i task vengono eseguiti è definita dai constraint che esistono su ogni task. E’ infatti possibile definire, collegando i task tra loro, quali sono lo condizioni di precedenza da rispettare affinchè un task possa essere eseguito. E’ possibile quindi stabilire che un task possa essere eseguito se e solo se il task che lo precede è stato:

  • eseguito

  • eseguito con successo

  • eseguito con errori

oltre a queste condizioni sullo stato di esecuzione è possibile definirne altre, basate questa volta sul risultato di un’espressione (scritta in un particolare linguaggio conosciuto come DTS Expression, la cui sintassi è un misto di C# e T-SQL, pensato e progettato per essere molto semplice da usare).

L’espressione deve dare un risultato booleano e permette di verificare la sussistenza di certe condizioni; ad esempio:

@filename == “orders.txt”

Verifica che la variabile @filename contenga il valore orders.txt.

Ogni task può essere subordinato ad una o più condizioni ed esso stesso può stabilirne più di una per i task successivi. Nel caso su di un task ci siano più condizioni, è possibile scegliere se doverle onorare tutte (logical AND) o almeno una (logical OR).

Vediamo due semplici esempi per rendere più chiaro il tutto:

Nell’immagine di esempio il “Send Mail Task” viene eseguito se il Data Flow Task è stato eseguito correttamente e se l’espressione definita nelle proprietà del constraint

è vera.

In questo caso, invece

Il Data Flow Task viene eseguito se almeno uno tra i due task precedenti (Execute SQL e File System) è stato eseguito correttamente.

Tutte queste caratteristiche garantiscono la possibilità di realizzare workflow (e quindi logiche di funzionamento) anche molto complessi semplicemente disegnandoli! Un grossissimo passo avanti rispetto al passato e una notevole riduzione dei tempi di sviluppo dei package, che non richiedono necessariamente la presenza di uno sviluppatore per essere utilizzati a dovere.

Nel control flow, oltre ai task, possono essere inseriti altri tipi di oggetti: i containers. Attualmente ci sono tre tipi di container utilizzabile direttamente:

  • Sequence

  • ForLoop

  • ForEach

I container, come suggerisce la parola, non solo altro che contenitori di task. Un sequence container è un contenitore molto semplice che ha il solo scopo di aiutare lo sviluppatore nella gestione delle transazioni. Se è necessario avere solo un certo numero di task che lavorano in modo transazionale, si pensi ad esempio a due task di tipo “Execute SQL” che operano su due database diversi ma che devono però compiere un’unica transazione, è sufficiente inserirli in un sequence container e dichiarare il container transazionale (la proprietà TransactionOption deve essere impostata a Required)

I container invece di tipo “For” effettuano un ciclo eseguendo i task a loro interno per “n” volte. Questo numero può essere un valore conosciuto a priori e quindi si può utilizzare il forloop container; se invece è necessario eseguire un insieme di task per ogni elemento presente in un certo insieme (ad esempio per tutti i file *.txt presenti in una directory), è allora ideale l’utilizzo del foreach container.

Il Data Flow

Il data flow è la superficie dove viene disegnato il flusso di dati prelevati da una sorgente, trasformati e quindi caricati in una destinazione. Per questo motivo è anche definito come fase di “Data Pump”.

In ogni package ci possono essere più Data Flow (tanti quanti ne servono), e l’esecuzione di ognuno viene comandata dai Data Flow task posti nel Control Flow. In pratica, se durante l’esecuzione del Control Flow l’engine dei SSIS trova un task di tipo Data Flow, attiva l’engine che si occupa della fase di Data Pump.

Un Data Flow si compone di sources (sorgenti), transformations (transformazioni) e destinations (destinazioni). Questi oggetti sono collegati l’uno all’altro tramite delle linee orientate che indicano la direzione del flusso dati.

Gli oggetti di tipo transformation permettono di mappare correttamente i dati dalla sorgente alla destinazione in modo praticamente visuale, senza dover necessariamente scrivere codice. I dati fluiscono dalla sorgente alla destinazione passando riga per riga attraverso tutte le trasformazioni poste sul loro percorso, e ogni trasformazione può modificare i dati che passano attraverso di essa.

Per lavorare in modo efficiente le righe vengono estratte dalle sorgenti dati e caricate in memoria, sottoforma di tabella, a blocchi. In questo modo i SSIS riescono a massimizzare l’utilizzo di memoria, garantendo così ottime performance durante l’esecuzione di tutte le trasformazioni presenti.

Numerose sono le trasformazioni fornite nativamente, e tra esse troviamo trasformazioni per:

  • effettuare operazioni sui dati presenti nel flusso generando nuove colonne o modificando i valori di quelle già esistenti (Derived Column)

  • effettuare il lookup dei dati presenti nel flusso su altre sorgenti dati, in modo tale da arricchire il flusso con informazioni aggiuntive. Tipico esempio è quella del lookup di un codice prodotto per recuperarne gli attributi aggiuntivi come colore, dimensione, ecc. ecc. (Lookup)

  • suddivisione del flusso dati in modo da elaborare i dati secondo diverse logiche. (Conditional Split)

  • ordinamento dei dati ed eventuale eliminazione dei duplicati (Sort)

anche in questo caso, nell’eventualità (non molto frequente) che le trasformazioni già fornite non siano sufficienti a soddisfare le nostre necessità, è possibile utilizzare la “Script Transformation”. Con questa trasformazione ci facciamo carico di scrivere uno script – utilizzando Visual Basic .NET – che effettui le trasformazioni che desideriamo. La cosa molto interessante è che una Script Transformation può agire anche da sorgente dati e da destinazione dati. In pratica ciò significa che se abbiamo una sorgente dati per cui non è presente nessun driver (perché magari i dati sono memorizzati in un file in un formato nativo, prodotto da nostra applicazione), possiamo scriverci (sempre via script) la sorgente dati che va ad estrarre i dati da tali file e li inserisce quindi nel flusso dati, che possiamo poi sviluppare nel modo usuale.

Una caratteristica importante del Data Flow è che può gestire sorgenti e destinazioni multiple. Esse possono essere inoltre eterogenee, ma non per questo le trasformazioni definite dovranno avere qualche particolare accorgimento. Questa possibilità di essere indipendente dalla forma orginale e finale dei dati è garantita dall’archittettura dei SSIS. Una volta letti i dati dalla sorgente sono in un formato interno, vi vengono applicate le trasformazioni e quindi sono riconvertiti nel formato finale. Quest’astrazione permette di poter prendere dei flussi provenienti da SQL Server, Oracle, DB2 e qualsiasi altra sorgente dati anche non relazionale, e di trattarli come se in realtà non ci fossero differenze tra i tre prodotti (dal punto di vista dei tipi di dati utilizzati). E’ evidente che questa è una funzionalità importantissima in quanto permette ai package di essere indipendenti dalle sorgenti e dalle destinazioni, garantendo cosi un’elevata flessibilità ed un più semplice sviluppo degli stessi.

Event Handlers

Ogni task, container e package è in grado di generare eventi. Un evento tipico è l’individuazione di un errore. E’ possibile creare dei control flow secondari ed associarli ad un determinato evento, in modo da poterlo gestire in una sede separata rispetto al control flow principale.

Un esempio tipico è l’evento OnError. Si può intercettare l’evento a livello di package, e creare per esso un control flow contenente un Send Mail Task, cosi che nell’eventualità dell’accadimento di un errore venga inviata una mail al referente del processo implementato nel package.

La cosa comoda ed interessante degli eventi è che funzionano come delle scatole cinesi. Si supponga di avere un errore in un Execute SQL Task, posto all’interno di un Sequence Container.

Ad ognuno di questi oggetti può essere associato un Event Handler per l’evento OnError. Nel caso tale handler sia presente sul Task, verrà eseguito l’handler ad esso associato. Se non è presente verrà seguito, se esistente, l’handler sul Sequence Container. Se non è presente nemmeno questo verrà eseguito quello sul Package. Se non è presente neppure quest’ultimo l’evento non verrà gestito.

Questa possibilità di “bubbling” degli eventi, permette di poter creare Event Handler “globali”, posti sul package, eliminando molta della complessità che altrimenti si dovrebbe gestire se tutto dovesse essere fatto solamente nel control flow principale.

Debugging

I SSIS supportano un debugging piuttosto avanzato dei package. Su ogni task è possibile mettere un breakpoint, in modo da poter fermare il package e verificare lo stato dei task, le valorizzazioni delle varie proprietà e delle variabili. Ma la cosa ancor più interessante è che è possibile effettuare anche il debugging, row-by-row delle righe di codice scritte in uno Script Task, esattamente come accade per le applicazioni usuali.

Semplicemente posizionando un breakpoint sulla riga del codice alla quale siamo interessati, durante l’esecuzione del package, Visual Studio – al momento dell’esecuzione della riga suddetta – interverrà e ci mostrerà il codice con le “solite” possibilità di Step-Into, Step-Over e via dicendo.

E’ evidente come tale funzionalità si fondamentale per poter capire dove si annida il problema in script di medio-alta complessità.

La possibilità di fare la stessa cosa anche negli Script Transformation del DataFlow è, nella versione corrente, purtroppo assente.

Il debug degli script nel DataFlow si deve quindi limitare all’utilizzo di un poco ortodosso comando MsgBox, che però, per fortuna, non è modale e quindi l’esecuzione del package può essere interrotta in qualsiasi momento, anche se la Message Box è visibile e sta aspettando la pressione del tasto “Ok” per procedere all’elaborazione della riga di dati successiva.

A proposito di dati c’è un funzionalità di debug davvero importante, fornita dalle “Data Views”.

Esse permettono di bloccare il dataflow e mostrarci i dati che stanno passando nel flusso di trasformazione. Questo permette, in presenza di trasformazioni non banali, formate da parecchie Transformations, di poter capire, nel caso in cui la trasformazione non fornisca il risultato voluto, a quale punto della sequenza di trasformazione l’errore viene introdotto.

Logging

Il logging è supportato nativamente dai package. A differenza di quanto accadeva in passato, però, l’utilizzo della funzionalità di logging deve essere abilitata al momento della creazione del package, in quanto per poter utilizzare il logging è necessario che nel package sia presente un connection manager dedicato ad esso.

Questo approcio è un po’ limitante in quanto non ci permette di poter attivare o disattivare il logging in modo arbitrario e rende inoltre la configurazione dello stesso un po’ macchinosa. In aggiunta a questi problemi il logging fornito nativamente non permette di poter realisiticamente fare del debug “post mortem”, tipica necessità che si ha quando un package è andato in errore durante un’elaborazione notturna, mentre il tutto funziona benissimo se lo proviamo a lanciare durante il giorno.

In questi, per poter venire a capo del problema, è necessario avere nel log la maggior quantità di dettagli possibili relativi allo stato del package al momento dell’errore: i valori di tutte le variabili utilizzate in quell’istante, il valore delle proprietà dell’oggetto che ha provocato l’errore e via dicendo.

Tutte queste informazioni sono disponibili tramite l’object model esposto dai SSIS, ma non sono raggiungibili tramite il tool di esecuzione ufficiale DTEXEC.

Per questo motivo ho scritto un tool alternativo, chiamato DTLoggedEXEC che risolve tutti questi problemi. Il tool sfrutta appieno l’ampio object model fornito da SSIS, cosi da fornire il massimo dettaglio possibile riguardo l’esecuzione di un package, andando inoltre a permettere l’attivazione del log anche su quei package che non sono stati originariamente predisposti per supportarlo.

DTLoggedExec è disponibile insieme al codice sorgente su CodePlex:

https://www.codeplex.com/DTLoggedExec

 

Esecuzione

Un package SSIS viene eseguito all’interno di Visual Studio solamente durante lo sviluppo, ed ogni package così eseguito lavora in modalità di debug. Per eseguire un package in un ambiente di produzione si deve utilizzare il tool DTEXEC, fornito sia a 32 che a 64bit.

DTEXEC accetta una nutrita serie di parametri, per cui, per comodità si può utilizzare il tool grafico DTEXECUI che aiuta nella scrittura e nella definizione degli stessi.

Nel caso in cui il package debba essere schedulato utilizzando il SQL Server Agent è possibile invocarne l’esecuzione utilizzando un nuovo job step dedicato a questo scopo (che si chiama appunto “SQL Server Integration Services Package”)

Una cosa particolarmente interessante è che i package possono essere invocati anche via codice. Nel namespace Microsoft.SqlServer.Dts.Runtime sono contenute tutte le classi necessarie. Questo ovviamente permette di poter creare applicazioni che comandano ed interagiscono fortemente con gli stessi, potendo, ad esempio, eseguire l’importazione di un file EXCEL utilizzando un package SSIS ma rimanendo all’interno della nostra applicazione.

Il risultato dell’importazione può essere anche mostrato in una datagrid, effettuando il binding della stessa ad un package in cui sia presente un dataflow che fa uso della “DataReader destination”!

Un esempio pratico di questa opportunità lo potete trovare sui Book Online a questo indirizzo:

Loading the output of a Local Package

https://msdn2.microsoft.com/library/ms135917.aspx

NOTE SULLA SICUREZZA

I package sviluppati sono salvati in formato XML all’interno di file .DTSX. Questo approccio rende molto semplice lo spostamento dei package da un computer ad un altro, ma richiede che i dati sensibili al suo interno (ad esempio login e password delle stringhe di connessione ai database) siano criptati. L’argomento è molto importante ed è quindi bene approfondirlo nei book online (questo articolo ha già raggiunto il limite massimo di caratteri concessomi… anzi, in realtà siamo ormai ben oltre ):

Security Considerations for Integration Services

https://msdn2.microsoft.com/library/ms137833.aspx

 

Conclusioni

L’articolo ha fornito un’introduzione pratica ai SQL Server Integration Services. Per tutti coloro che sono interessati ad approfondire l’argomento, andando anche a toccare elementi che in questo articolo non sono stati coperti, come ad esempio lo sviluppo di task o transformation custom, l’integrazione dei package con proprie applicazioni, lo scripting e via dicendo, consiglio di far riferimento ai seguenti testi:

  • Professional SQL Server 2005 Integration Services, Wrox, ISBN 0764584359

  • Microsoft SQL Server 2005 Integration Services, SAMS, ISBN 0672327813

Per chi invece desidera avere un tutorial introduttivo per iniziare ad usare i SSIS consiglio queste risorse:

http://www.dotnethell.it/articles/SSIS-SQL-Server-Integration-Services.aspx

I Books online:

https://msdn2.microsoft.com/library/ms141026.aspx

Ed un completo Hands-On lab per fare pratica:

https://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en