SSIS - Guida operativa e all'ottimizzazione
Articolo tecnico su SQL Server
Autori: Alexei Khalyako, Carla Sabotta, Silvano Coriani, Sreedhar Pelluru, Steve Howard
Revisori tecnici: Cindy Gross, David Pless, Mark Simms, Daniel Sol
Data di pubblicazione: dicembre 2012
Contesto di applicazione: SQL Server 2012; database SQL di Windows Azure
Riepilogo: SQL Server Integration Services (SSIS) può essere utilizzato come efficace strumento per
spostare i dati da e verso il database SQL di Windows Azure, come parte della soluzione globale di
estrazione, trasformazione e caricamento (ETL) e della soluzione di spostamento di dati, ed è ideale per
spostare dati tra origini e destinazioni nel cloud e, in scenari ibridi, tra ambienti cloud e locali. In questo
white paper vengono descritte le procedure consigliate per SSIS relative a origini e destinazioni cloud,
viene illustrata la pianificazione di progetti SSIS da implementare completamente nel cloud o in ambienti
ibridi di spostamento di dati e viene descritto un esempio di ottimizzazione delle prestazioni in uno
spostamento ibrido tramite la scalabilità orizzontale dello spostamento dei dati.
Copyright
Il documento viene fornito "così com'è". Le informazioni e le opinioni espresse nel presente documento,
inclusi gli URL e altri riferimenti a siti Web, possono essere soggette a modifiche senza preavviso.
L'utente accetta di utilizzare tali informazioni a proprio rischio.
Alcuni esempi raffigurati in questo documento sono forniti a scopo puramente illustrativo e sono fittizi.
Nessuna associazione o connessione reale è intenzionale o può essere desunta.
Il presente documento non implica la concessione di alcun diritto di proprietà intellettuale relativo ai
prodotti Microsoft. È possibile copiare e utilizzare questo documento per fini di riferimento interno.
© 2011 Microsoft. Tutti i diritti sono riservati.
2
Sommario
Introduzione.................................................................................................................................................. 5
Progettazione ................................................................................................................................................ 5
Definizione e descrizione del problema .................................................................................................... 5
Perché lo spostamento dei dati è particolarmente complesso in Azure .................................................. 6
Scenari principali di spostamento dei dati ................................................................................................ 7
Caricamento iniziale dei dati e migrazione dall'ambiente locale al cloud ............................................ 7
Spostamento dei dati generati nel cloud sui sistemi locali ................................................................... 8
Spostamento di dati tra servizi cloud.................................................................................................... 9
Strumenti, servizi e soluzioni esistenti...................................................................................................... 9
SQL Server Integration Services (SSIS) ................................................................................................ 10
Classe SqlBulkCopy in ADO.NET .......................................................................................................... 11
Programma per la copia bulk (BCP.EXE) ............................................................................................. 12
BLOB e code di archiviazione di Windows Azure ................................................................................ 12
Scelte di progettazione e implementazione ........................................................................................... 13
Progettare e implementare un'architettura bilanciata ...................................................................... 13
Considerazioni sui tipi di dati .............................................................................................................. 15
Assemblaggio e distribuzione della soluzione ........................................................................................ 15
Creare soluzioni portatili ..................................................................................................................... 15
Distribuzione di pacchetti e componenti di codice............................................................................. 16
Database SQL di Windows Azure come destinazione dello spostamento dei dati............................. 16
Considerazioni sull'architettura .................................................................................................................. 17
Progettazione del riavvio senza perdita di avanzamento nella pipeline ................................................ 18
Il principio di base ................................................................................................................................... 18
Esempio con una singola destinazione ............................................................................................... 19
Esempio con più destinazioni ................................................................................................................. 25
Altri suggerimenti per il riavvio ............................................................................................................... 27
Progettazione della logica di ripetizione dei tentativi senza intervento manuale ................................. 28
Integrazione della ripetizione dei tentativi ............................................................................................. 29
3
Opzioni di ottimizzazione delle prestazioni di SSIS ..................................................................................... 33
Ottimizzazione delle impostazioni di rete............................................................................................... 33
Impostazioni di rete ............................................................................................................................ 34
Nota: quando si modificano le impostazioni della scheda di interfaccia di rete per
utilizzare i frame jumbo, verificare che l'infrastruttura di rete supporti questo tipo di frame.
Dimensioni di pacchetto ..................................................................................................................... 34
Impostazioni del pacchetto SSIS ............................................................................................................. 35
Considerazioni speciali per i dati BLOB ............................................................................................... 37
Utilizzo delle nuove funzionalità di SSIS 2012 per monitorare le prestazioni in un
sistema distribuito .................................................................................................................................. 39
Registrare le statistiche sulle prestazioni............................................................................................ 40
Visualizzare le statistiche di esecuzione ............................................................................................. 41
Monitorare il flusso di dati .................................................................................................................. 47
Conclusione ................................................................................................................................................. 52
4
Introduzione
SQL Server Integration Services (SSIS) è uno strumento efficace per spostare i dati da e verso il database
SQL di Windows Azure, come parte della soluzione di estrazione, trasformazione e caricamento (ETL)
completa e della soluzione di spostamento di dati in cui non sono necessarie trasformazioni.
SSIS è utilizzabile con svariate origini e destinazioni, che possono trovarsi tutte nel cloud, tutte in
un ambiente locale o combinate in una soluzione ibrida. In questo white paper vengono descritte le
procedure consigliate per SSIS relative a origini e destinazioni cloud, viene illustrata la pianificazione di
progetti SSIS da implementare completamente nel cloud o in ambienti ibridi di spostamento di dati e
viene descritto un esempio di ottimizzazione delle prestazioni in uno spostamento ibrido tramite la
scalabilità orizzontale dello spostamento dei dati.
Progettazione
I progetti per lo spostamento di dati tra archivi dati cloud e locali possono implicare diversi processi in
svariate soluzioni. Le fasi sono spesso molteplici e complesse; ad esempio, il popolamento iniziale della
destinazione con dati che possono provenire da altri sistemi o piattaforme, la manutenzione necessaria
per ribilanciare i set di dati a fronte della variazione del numero delle partizioni e la necessità di
continuare con periodiche operazioni bulk o di aggiornamento sui dati. I presupposti di progettazione
e il progetto stesso di una soluzione che preveda la collocazione di dati nel cloud sono spesso diversi
da quelli necessari nel caso di un tradizionale spostamento di dati che si svolge completamente in
locale. Molte nozioni, esperienze e procedure sono comunque valide, ma è necessario introdurre
delle modifiche che tengano conto delle differenze, ad esempio il fatto che, quando ci si sposta in
un pool condiviso di risorse disponibili a livello commerciale, l'ambiente in uso non è più autonomo
e completamente controllabile. Per ottenere risultati positivi, è necessario gestire queste differenze
con un approccio più bilanciato e scalabile.
Definizione e descrizione del problema
Sia nel caso di soluzioni native create appositamente per il cloud che nel caso di soluzioni migrate, i dati
devono essere spostati in un senso e nell'altro. La probabilità che questa esigenza si presenti in molte
fasi del ciclo di vita delle applicazioni è elevata. Le fasi includono i test di preproduzione, il caricamento
iniziale dei dati, la successiva sincronizzazione tra i dati generati nel cloud e i database locali originali
e gli snapshot periodici di dati creati a partire dal cloud fino agli altri sistemi locali (ad esempio, i data
warehouse).
5
Figura 1. Scenari di spostamento di dati
Questa sezione è incentrata sulla fase di caricamento iniziale dei dati, cioè sull'esperienza end-to-end di
estrazione dei dati dal database di origine, lo spostamento dall'ambiente locale al cloud e il caricamento
dei dati in una destinazione finale. È importante sottolineare che la maggior parte delle procedure
consigliate e delle ottimizzazioni (se non tutte) descritte in questo documento è valida per pressoché
tutti gli scenari descritti, con modifiche minime. Questi scenari e le problematiche principali associate
verranno trattati nelle sezioni successive.
Perché lo spostamento dei dati è particolarmente complesso in Azure
Rispetto a uno scenario di data center tradizionale, in cui gli aspetti più delicati dello spostamento dei
dati tra applicazioni e sistemi sono in genere correlati a problemi quali la compatibilità delle applicazioni,
l'orchestrazione e la sincronizzazione dei processi, le risorse hardware fisiche e le funzionalità di rete,
ambienti cloud come Windows Azure prevedono ulteriori livelli di complessità. La complessità può
riguardare aree come la connettività tra l'ambiente locale e il cloud o tra servizi cloud diversi
e interessare affidabilità, larghezza di banda e latenza della connessione. La considerazione di questa
complessità è un fattore cruciale nello sviluppo di una soluzione ottimale di spostamento dei dati.
Quando la soluzione implica un numero elevato di parti in movimento, è ancora più importante che
nella progettazione vengano giustamente bilanciati tutti i componenti e le tecnologie utilizzati, cercando
di evitare il sovraccarico dei punti deboli del sistema, che ricadrebbe in modo negativo su tutti gli altri.
6
Sulla base dei test effettuati, una delle aree critiche è la capacità della destinazione dei dati di assorbire,
alla velocità appropriata, la quantità di dati in arrivo dall'esterno. L'approccio più comune consiste nello
scalare orizzontalmente il database di destinazione in più nodi di back-end con partizionamento orizzontale
personalizzato (http://social.technet.microsoft.com/wiki/contents/articles/1926.how-to-shard-withwindows-azure-sql-database.aspx). Questa tecnica diventa obbligatoria se la quantità di dati da caricare
è significativa (al momento della stesura di questo documento un volume di 20 GB all'ora è considerato
significativo) e può essere applicata sia alle istanze del database di Azure che a SQL Server in esecuzione
su Macchine virtuali di Windows Azure. Poiché questo metodo non introduce automaticamente la
scalabilità lineare nella soluzione di caricamento dei dati, diventa più forte l'esigenza di bilanciare le altre
parti mobili della soluzione. Nelle sezioni successive verranno descritte le aree più critiche e le opzioni di
progettazione che possono essere adottate per ottimizzare i risultati finali.
Scenari principali di spostamento dei dati
Di seguito vengono descritti i tre scenari principali che verranno considerati come parte dell'esperienza
globale di spostamento dei dati end-to-end. Tali scenari contengono la maggior parte dei temi e delle
problematiche più ricorrenti.
Caricamento iniziale dei dati e migrazione dall'ambiente locale al cloud
Spostamento dei dati generati nel cloud sui sistemi locali
Spostamento di dati tra servizi cloud
Caricamento iniziale dei dati e migrazione dall'ambiente locale al cloud
Database SQL
o SQL Server in una
macchina virtuale
Figura 2. Scenario di caricamento iniziale dei dati
7
Per ogni applicazione da spostare da una distribuzione locale a un ambiente cloud è necessario spostare
anche un certo volume di dati. Quando il volume di dati diventa significativo, questa operazione può
presentare problematiche serie che richiedono un approccio leggermente diverso rispetto a uno
scenario interamente locale, principalmente a causa di due aspetti: la larghezza di banda e la latenza
della rete pubblica e la quantità di risorse (condivise) necessarie per la fase di caricamento dei dati,
disponibili nei nodi hardware fisici che ospitano un database (database SQL di Azure o Macchine virtuali
di Windows Azure) nell'ambiente cloud. Esistono approcci specifici (vedere la figura 2): ad esempio,
il partizionamento dei dati originali in più file bucket e la compressione di questi file prima del
trasferimento in rete possono ridurre l'impatto del componente a più basse prestazioni della soluzione
globale. Il partizionamento dei dati è utile anche sul lato cloud perché favorisce l'inserimento dei dati
in una destinazione che molto probabilmente verrà partizionata in modo orizzontale tra più istanze di
database SQL di Azure o ospitata da più Macchine virtuali di Windows Azure.
Il ruolo di SSIS per l'esecuzione fisica delle operazioni di importazione ed esportazione è sostanziale sia
a livello locale che a livello di cloud. La soluzione globale richiederà altre tecnologie come l'archiviazione
BLOB e le code di Windows Azure per archiviare i formati di file intermedi e per orchestrare la copia
e recuperare l'operazione su più istanze dei processi di importazione di SSIS.
Per ulteriori informazioni strettamente correlate alla migrazione dello schema e degli oggetti di
database in un database SQL di Azure, vedere Migrazione di applicazioni incentrate sui dati
a Windows Azure (http://msdn.microsoft.com/it-it/library/windowsazure/jj156154.aspx).
Spostamento dei dati generati nel cloud sui sistemi locali
Questo scenario è una variante molto simile dello scenario precedente, perché da un punto di vista
tecnico prevede l'inversione del processo di caricamento e del flusso di dati, ma presenta anche qualche
differenza riguardo agli obiettivi complessivi. Lo scenario è tipicamente associato ai dati generati
a freddo che devono essere periodicamente recuperati e caricati in sistemi locali, come i data warehouse,
o ai dati necessari per soluzioni transazionali locali. Di conseguenza, la maggior parte delle tecniche
e delle tecnologie indicate nella sezione precedente è valida anche questo scenario. Tramite SSIS i dati
sul lato cloud vengono estratti, quindi compressi e inviati a un sistema locale, cui si applicheranno tutte
le linee guida tradizionali. Per ulteriori informazioni, vedere la Guida all'ottimizzazione delle prestazioni di
caricamento dei dati (http://msdn.microsoft.com/it-it/library/dd425070(v=SQL.100).aspx).
8
Spostamento di dati tra servizi cloud
In diversi scenari è necessario spostare i dati tra diversi servizi cloud e database. Tra questi sono inclusi il
caso in cui i dati vengono scambiati tra soluzioni diverse che devono interagire e quello in cui i dati
partizionati vengono ridistribuiti tra tabelle ospitate in partizioni diverse, come illustrato nella figura 3
di seguito.
Figura 3. Spostamento di dati tra partizioni di database
Database SQL o SQL Server in una macchina virtuale
Le partizioni possono essere ugualmente ospitate da istanze di database SQL di Azure o SQL Server in
un macchina virtuale di Windows Azure senza che cambino l'approccio e l'architettura sottostanti.
A differenza degli scenari precedenti, l'intero processo di spostamento dei dati avviene in genere nei
limiti di un'unica area di Windows Azure, riducendo notevolmente l'impatto della latenza di rete ed
eliminando la necessità di esportare e importare dati utilizzando un percorso di archiviazione intermedio
(dischi locali o archiviazione di Azure). Esistono alcuni scenari in cui può essere necessario spostare
i dati tra aree diverse, ma questi casi non rientrano nell'ambito di trattazione di questo documento.
Allo stesso tempo, poiché l'origine e la destinazione dei dati si trovano entrambe in un ambiente cloud
condiviso, diventa molto più importante ottimizzare con precisione proprio la fase di caricamento.
Strumenti, servizi e soluzioni esistenti
Per implementare soluzioni per gli scenari descritti in precedenza, è possibile utilizzare una
combinazione di strumenti nuovi ed esistenti e di componenti e approcci utilizzabili sia in locale che
nel cloud. In un ambiente ibrido alcuni di questi componenti dovranno essere posizionati con i sistemi
e le origini dati esistenti nell'ambiente locale, mentre altri risulteranno particolarmente efficienti se
posizionati con le destinazioni dei dati nel cloud.
9
SQL Server Integration Services (SSIS)
Come soluzione primaria di spostamento e integrazione dei dati, SSIS offre una vasta gamma di
funzionalità per la gestione della maggior parte delle aree previste negli scenari trattati. Pur non
appositamente progettati per l'ambiente ibrido, con l'avvento di Macchine virtuali di Windows Azure
i pacchetti SSIS possono essere eseguiti sia in ambienti locali che nel cloud e possono virtualmente
collegare i due mondi in modo diretto. Questa è una grande opportunità di riutilizzo delle conoscenze
e delle competenze della community di sviluppo DBA/ETL, che vanta numerosi professionisti formati ed
esperti in tale tecnologia. Tuttavia è importante sottolineare che non tutti i processi ETL implementati
con SSIS possono essere riutilizzati direttamente nello spostamento di dati da un ambiente locale al cloud.
A seconda della complessità del processo, del volume e della velocità dei dati e delle differenze
intrinseche tra le destinazioni dei dati basate su cloud, come SQL Server in esecuzione su un macchina
virtuale di Windows Azure e il database SQL di Azure, si impongono alcune modifiche architetturali.
Alcune di queste problematiche possono riguardare l'attuale mancanza di capacità nella gestione
degli aspetti di connettività a realtà cloud come il database SQL di Windows Azure o la mole di lavoro
richiesta per progettare pacchetti SSIS in grado di gestire errori e tentativi ripetuti durante i processi di
caricamento dei dati.
Un'altra problematica può essere individuata nella necessità di progettare pacchetti che devono
connettersi a destinazioni di dati partizionate, in cui le entità di database possono essere distribuite su
un numero di nodi fisici che può cambiare nel tempo. La logica e i metadati di partizionamento devono
essere gestiti e recuperati dai file di configurazione o dalle strutture di dati delle applicazioni.
La piattaforma SSIS include già la maggior parte delle funzionalità in grado di gestire tali problematiche.
Ad esempio, è possibile utilizzare i componenti del flusso di dati quali le trasformazioni Suddivisione
condizionale e Multicast per implementare la logica di partizionamento.
Nell'affrontare le problematiche architetturali, l'implementazione pratica della nuova progettazione,
relativamente più impegnativa, può essere attuata adottando il tradizionale approccio degli strumenti
visivi o un metodo più orientato all'automazione e alla programmazione per costruire una soluzione più
complessa. Per l'approccio a livello di programmazione, SSIS offre un ambiente completamente gestibile
tramite script per un'ampia gamma di operazioni, dalla creazione di attività personalizzate nella pipeline
di trasformazione alla strumentazione per facilitare la risoluzione dei problemi e il debug dell'esecuzione
dei pacchetti.
La versione SQL Server 2012 di Integration Services include una soluzione completa di monitoraggio
e gestione basata su un catalogo comune, che può facilitare la progettazione di una soluzione distribuita
di spostamento dei dati e la raccolta di statistiche e risultati dell'esecuzione dei pacchetti.
10
Classe SqlBulkCopy in ADO.NET
Se si opta per lo sviluppo di una soluzione personalizzata per risolvere un problema specifico di spostamento dei
dati, la classe SqlBulkCopy (http://msdn.microsoft.com/it-it/library/system.data.sqlclient.sqlbulkcopy.aspx)
contenuta nella libreria di accesso ai dati ADO.NET è probabilmente uno degli strumenti più comuni da
utilizzare allo scopo. Concepita come semplice wrapper delle API per la copia bulk ODBC, questa classe
accetta come input una tabella di dati e una connessione al database esistente e consente di caricare
dati in SQL Server o in un database SQL di Azure in modo rapido e completamente configurabile.
Un aspetto importante dell'utilizzo della classe SqlBulkCopy per interagire con una destinazione di
dati basata sul cloud è la possibilità di sostituire facilmente la tradizionale classe SqlConnection
(http://msdn.microsoft.com/it-it/library/system.data.sqlclient.sqlconnection.aspx) utilizzata per
interagire con il server con la più appropriata classe ReliableSqlConnection
(http://msdn.microsoft.com/it-it/library/microsoft.practices.enterpriselibrary.windowsazure.
transientfaulthandling.sqlazure.reliablesqlconnection(v=pandp.50).aspx), che fa parte della libreria del
blocco di applicazioni per la gestione degli errori temporanei (http://msdn.microsoft.com/itit/library/hh680934(v=PandP.50).aspx). Ciò semplifica notevolmente l'implementazione di un
meccanismo di logica di ripetizione dei tentativi in un processo di caricamento di dati nuovo o esistente.
Un altro interessante aspetto della libreria sta nella capacità di fornire criteri di ripetizione dei tentativi,
sia standard che personalizzati, da adattare facilmente alle diverse condizioni di connettività.
La classe SqlBulkCopy espone tutti gli attributi e le proprietà necessari per adattare il processo di
caricamento a quasi tutte le condizioni. In questo articolo viene spiegato come ottimizzare le dimensioni
dei batch a seconda della posizione di esecuzione del processo di caricamento dei dati, della quantità di
dati che il processo deve importare e del tipo di connettività disponibile tra il processo e la destinazione
dei dati.
Una situazione in cui la classe SqlBulkCopy non costituisce la scelta più pratica per caricare dati in una
destinazione si verifica quando la quantità di dati in un singolo batch è molto bassa, ad esempio tra 10
e 1000 righe. In questo caso, l'overhead richiesto dalla classe SqlBulkCopy per il controllo iniziale
dei metadati prima del caricamento dei dati può influire sulle prestazioni complessive. Una valida
alternativa per i batch di piccole dimensioni consiste nel definire un parametro con valori di tabella
(TVP) che implementi lo schema desiderato, utilizzando “INSERT INTO Destination SELECT * FROM
@TVP” per caricare i dati.
Per un esempio completo dell'utilizzo dell'API per la copia bulk, vedere la classe SqlBulkCopy
(http://msdn.microsoft.com/it-it/library/system.data.sqlclient.sqlbulkcopy.aspx).
11
Programma per la copia bulk (BCP.EXE)
Il programma per la copia bulk (utilità della riga di comando creata in base alle stesse API per la copia
bulk descritte per la classe SqlBulkCopy) viene utilizzato da qualche tempo per il caricamento bulk dei
dati da e verso un'istanza di SQL Server. Il programma è uno strumento semplice ma potente che
consente di automatizzare con efficienza le soluzioni di spostamento di dati semplici. Uno dei principali
vantaggi dello strumento consiste nella semplicità con cui è possibile automatizzarne l'installazione nei
nodi di calcolo o in Macchine virtuali di Windows Azure e utilizzare gli script esistenti, adattabili
all'esecuzione in un ambiente cloud.
D'altra parte, BCP.EXE non offre funzionalità avanzate per la gestione delle connessioni. Inoltre,
BCP.EXE è impegnativo come SSIS per quanto riguarda l'implementazione di attività affidabili di
spostamento dei dati basate su operazioni di ripetizione dei tentativi che possono provocare instabilità
e perdita di connessione. In più, a differenza degli altri strumenti citati, con BCP.EXE è necessario
importare o esportare i dati da file fisici ospitati in un'unità locale, mappata o collegata. In questo modo
diventa impossibile inviare il flusso di dati direttamente dall'origine alla destinazione o leggere i dati
a livello di codice da origini diverse, come è invece possibile fare con SSIS o un'applicazione basata su
SqlBulkCopy.
BLOB e code di archiviazione di Windows Azure
Per quanto non strettamente correlate allo spostamento dei dati, le funzionalità di archiviazione di
Windows Azure sono senza dubbio parte integrante dell'implementazione di soluzioni complesse che
richiedono un'archiviazione intermedia tra processi locali e cloud, nonché elemento essenziale per
l'orchestrazione di fasi e operazioni tra i due ambienti. I BLOB di archiviazione di Windows Azure
rappresentano un potente meccanismo di archiviazione per caricare file intermedi e per scambiarli tra
nodi di calcolo o Macchine virtuali di Windows Azure e le applicazioni in esecuzione in locale. Le code di
archiviazione di Windows Azure sono un semplice strumento di messaggistica che permette di segnalare
e coordinare l'accesso ai file e ai contenuti archiviati come BLOB di Windows Azure dai processi di
caricamento dei dati.
I BLOB e le code di archiviazione di Windows Azure si integrano facilmente con le applicazioni esistenti,
grazie alla libreria client di archiviazione di Azure .NET che offre un semplice set di classi per interagire
con gli account di archiviazione, il contenitore, i BLOB e le operazioni correlate. Questa libreria nasconde
i dettagli dell'interfaccia basata su REST sottostante e funge da ponte tra i dati locali e i dati del cloud.
Per ulteriori informazioni sull'uso delle code e dei BLOB di archiviazione di Windows Azure,
vedere le pagine relative alla modalità di utilizzo del servizio di archiviazione nelle code
(http://www.windowsazure.com/en-us/develop/net/how-to-guides/queue-service/) e alla modalità di
utilizzo del servizio di archiviazione BLOB di Windows Azure in .NET (http://www.windowsazure.com/
en-us/develop/net/how-to-guides/blob-storage/).
12
Scelte di progettazione e implementazione
Diversi fattori possono influire sulle scelte di progettazione e implementazione per le soluzioni ibride
di spostamento dei dati. La necessità di riutilizzare elementi e processi esistenti anziché iniziare da
un progetto completamente nuovo è probabilmente il fattore di maggiore impatto sulle decisioni
architetturali, seguito dall'insieme di competenze e dai profili dei membri del team a disposizione
(se, ad esempio, il numero di sviluppatori supera il numero di amministratori di database). Valutare se il
team disponga delle competenze per creare una soluzione totalmente personalizzata a livello di codice
o per adattare i processi ETL esistenti. In entrambi i casi, introducendo il cloud nella progettazione
è necessario tenere presenti alcune considerazioni, poiché alcuni dei più ovvi presupposti alla base del
funzionamento dei tradizionali ambienti locali potrebbero non avere alcuna validità in un ambiente cloud.
Un altro aspetto importante della progettazione riguarda la posizione in cui collocare ed eseguire
specifiche attività e servizi di spostamento dei dati, ad esempio la logica di suddivisione condizionale in
base alla quale vengono eseguite le attività di partizionamento orizzontale o di compressione dei dati.
Il modo in cui queste attività vengono implementate a livello di componente (pipeline SSIS o attività
personalizzate) può determinare un utilizzo molto intenso della CPU da parte di tali componenti.
Per bilanciare l'utilizzo delle risorse, può essere consigliabile spostare le attività in Macchine virtuali di
Windows Azure e sfruttare la naturale elasticità dell'ambiente cloud. Allo stesso tempo, la vicinanza alle
origini dati su cui le risorse opereranno potrebbe risultare ancora più vantaggiosa a causa della riduzione
della latenza di rete, che può rivelarsi un fattore decisamente critico per questo tipo di soluzione.
La pianificazione e i test consentono di individuare i colli di bottiglia nelle risorse e facilitano le decisioni
relative all'implementazione delle varie attività.
Il lavoro richiesto per implementare o adattare una soluzione esistente in uno scenario ibrido deve
essere giustificato dai vantaggi che lo scenario ibrido può assicurare. È necessario chiarire i vantaggi
tecnici che verranno ottenuti spostando alcune parti della soluzione nel cloud rispetto ai corrispondenti
svantaggi, in modo da adottare il giusto approccio e implementare una soluzione ibrida di successo.
Le scelte di compromesso sono collegate ad aspetti molto tangibili della progettazione di una soluzione.
Come sfruttare le eccellenti funzionalità di scalabilità orizzontale fornite dalle piattaforme cloud senza
cedere troppo controllo sui componenti della soluzione? Come eseguire i processi di caricamento dei
dati su una piattaforma progettata per la scalabilità orizzontale anziché verticale e ottenere comunque
prestazioni sufficientemente prevedibili? Per rispondere a queste domande è necessario dimenticare
alcuni presupposti relativi alle prestazioni e all'affidabilità della connettività di rete e alla regolare
disponibilità di componenti e servizi applicativi e pianificare invece la possibilità di aggiungere risorse
per risolvere i problemi legati alle prestazioni. L'approccio corretto implica una progettazione che
preveda situazioni di errore, un valore di latenza normalmente più alto rispetto agli ambienti tradizionali
e la possibilità di partizionare un carico di lavoro su molte macchine virtuali o servizi di piccole dimensioni.
Progettare e implementare un'architettura bilanciata
Le considerazioni precedenti dovrebbero indicare la giusta direzione verso la progettazione di una
soluzione di spostamento di dati complessa, con più parti mobili, nessuna delle quali rappresenta
necessariamente l'equivalente ottimale dei tradizionali componenti locali.
13
Il principio a cui ispirarsi è questo: suddividere il processo di spostamento dei dati in più parti di
dimensioni inferiori, dall'estrazione dall'origine dati al caricamento nella destinazione, che devono
avvenire in modo asincrono e orchestrato per adeguarsi all'ambiente a più elevata latenza introdotto
dalla soluzione ibrida. La ricerca del giusto equilibrio tra tutti i componenti di un ambiente è molto
più importante del raggiungimento dei limiti di un singolo componente. Anche i singoli passaggi del
processo, ad esempio il caricamento dei dati, potrebbero richiedere il partizionamento in flussi di
caricamento più piccoli inviati a partizioni o database fisici diversi per superare i limiti di un singolo nodo
di back-end nell'architettura del database SQL di Windows Azure.
Data la natura condivisa, multi-tenant e a disponibilità elevata di alcuni componenti del sistema (i nodi
del database SQL di Windows Azure e il repository dei BLOB di archiviazione di Windows Azure di una
macchina virtuale di Windows Azure che ospita SQL Server), il tentativo di inserire troppi dati in un
singolo nodo potrebbe provocare ulteriori problemi di prestazioni. Un problema di prestazioni può
essere ad esempio il sovraccarico del meccanismo di replica, che provoca il rallentamento dell'intero
processo di caricamento dei dati.
Figura 4. Rappresentazione schematica di un'architettura di caricamento dei dati bilanciata
14
Considerazioni sui tipi di dati
Lo schema di database, la progettazione delle entità e i tipi di dati utilizzati possono influire sul processo
di spostamento dei dati in vari modi. In generale, i tipi di dati più comprimibili possono offrire diversi
vantaggi per il caricamento bulk dei dati dalle rispettive origini in BLOB di Windows Azure o in risorse di
archiviazione locale per le operazioni temporanee. Naturalmente la compressione dei dati prima del
trasferimento in rete migliora le prestazioni.
Assemblaggio e distribuzione della soluzione
L'implementazione e la distribuzione di una soluzione che include data center locali e ambienti basati su
cloud richiedono in genere l'utilizzo di diversi componenti e servizi. Se l'intento è quello di distribuire più
istanze della soluzione di spostamento dei dati, assume particolare importanza assicurare un elevato
livello di automazione alle fasi di distribuzione e configurazione di tutte queste parti. Le tecnologie di
virtualizzazione possono facilitare la creazione di un'immagine master, che può essere utilizzata sia in
locale che nell'infrastruttura Macchine virtuali di Windows Azure per semplificare la distribuzione di
servizi comuni, la cui presenza è necessaria in entrambi gli ambienti.
Allo stesso tempo, l'utilizzo di Macchine virtuali di Windows Azure pone alcuni limiti rispetto alle
possibilità offerte da altri nodi di calcolo di Windows Azure, come i ruoli Web e i ruoli di lavoro, in
termini di applicazione e di pacchetti e servizi correlati (basti pensare, ad esempio, alle attività di avvio).
Se si stanno già sfruttando funzionalità di distribuzione software, ad esempio quelle disponibili tramite
le famiglie di prodotti Windows Server e System Center, un'altra possibilità consiste nel distribuire
i componenti e i pacchetti della soluzione in modo che alcuni componenti vengano eseguiti nel cloud
e altri negli ambienti locali.
Un'ulteriore possibilità consiste nell'installare e configurare manualmente i vari componenti della
soluzione, quali SSIS e Windows Azure SDK (per accedere alle funzionalità di archiviazione di Windows
Azure), nonché tutti i pacchetti di installazione dell'applicazione (con estensione msi) necessari in ogni
macchina virtuale eseguita come parte dell'ambiente distribuito.
Creare soluzioni portatili
Un aspetto che diventa particolarmente importante quando si esegue una soluzione in un'architettura
a scalabilità orizzontale è la possibilità di riconfigurare rapidamente opzioni come le stringhe di
connessione, le credenziali e tutte le altre opzioni di configurazione che includerà la soluzione. Questo
richiede in genere un meccanismo di configurazione centralizzato di qualche tipo, che renda possibile
l'accesso alle informazioni e la loro propagazione a tutti i vari componenti e servizi inclusi nel processo di
spostamento dei dati, in modo che ogni modifica implichi il minimo sforzo. Adottando questo approccio
è possibile implementare strumenti standard come SSIS e componenti e applicazioni sviluppati in modo
personalizzato. L'archiviazione di Windows Azure può costituire una scelta valida per salvare e gestire
le informazioni di configurazione, in quanto è facilmente accessibile e disponibile ai componenti locali
e del cloud.
15
Vale la pena notare che la piattaforma SSIS include già diverse funzionalità che semplificano la
portabilità e la scalabilità di una soluzione, come file e parametri di configurazione. Processi e servizi
aggiuntivi che compongono la soluzione di spostamento di dati end-to-end possono implementare gli
stessi tipi di approcci configurabili, semplificando il passaggio della soluzione tra ambienti diversi.
Distribuzione di pacchetti e componenti di codice
Una volta implementati tutti gli elementi della soluzione, la scelta del processo per distribuire
fisicamente i vari pacchetti SSIS e i componenti di codice in più computer diventa fondamentale.
Un altro aspetto ancora più importante sta nel modo in cui i pacchetti e gli elementi di codice verranno
ospitati ed eseguiti nei vari server e macchine virtuali. Anche se l'ambiente nativo SSIS in SQL Server
2012 offre diversi tipi di archiviazione e modelli di distribuzione dei pacchetti, lo sviluppo di una
soluzione di spostamento di dati end-to-end può comportare scelte diverse. Se l'esigenza è eseguire
servizi o applicazioni di orchestrazione per la supervisione e il controllo del processo di spostamento dei
dati, è necessario valutare come implementarli e in che misura utilizzare l'infrastruttura SSIS sottostante.
Un esempio specifico della distribuzione e del coordinamento dei componenti è disponibile nel white
paper sul ruolo di SQL Server 2012 SSIS nello spostamento di dati ibridi e Windows Azure, disponibile in
MSDN Library nel nodo dedicato ai white paper Microsoft per SQL Server 2012.
Macchine virtuali di Windows Azure e i server fisici basati su Windows Server non implementano tutte
le funzionalità fornite dalla piattaforma Windows Azure per i ruoli di lavoro e i ruoli Web. Per questo
motivo, la scelta migliore consiste nell'implementare tali componenti come servizi Windows, per
assicurarsi che i processi vengano avviati contemporaneamente ai vari host e che continuino a essere
eseguiti indipendentemente dalla sessione utente interattiva nel computer specifico. La piattaforma
.NET facilita la creazione e l'assemblaggio di questo tipo di pacchetto software che può quindi essere
distribuito sui vari host utilizzando le opzioni descritte in precedenza.
I servizi e le applicazioni di orchestrazione interagiscono con i vari componenti esterni (code,
archiviazione BLOB di Windows Azure e così via), richiamano il motore di esecuzione SSIS (DtExec.exe)
e orchestrano le attività di caricamento e trasformazione dei dati su più host o macchine virtuali.
Anche i componenti personalizzati necessari per l'esecuzione del pacchetto dovranno essere distribuiti
su più nodi.
Seguendo questo approccio distribuito è possibile creare un ambiente di distribuzione ed esecuzione
affidabile, portatile e flessibile per ospitare la soluzione end-to-end di spostamento dei dati in
un'infrastruttura completamente ibrida.
Database SQL di Windows Azure come destinazione dello spostamento dei dati
Benché SQL Server e i database SQL di Windows Azure presentino caratteristiche simili, è errato ritenerli
identici. Esistono alcune differenze che possono influire sul modo in cui le applicazioni vengono eseguite
nel database SQL di Windows Azure rispetto al database SQL Server.
16
Il database SQL di Windows Azure è un servizio ospitato in cui viene implementata un'architettura
multi-tenant completa. A differenza delle implementazioni tradizionali di SQL Server, il database SQL di
Windows Azure include funzionalità quali la disponibilità elevata (HA) incorporata e i backup automatici
e può essere eseguito su hardware di largo consumo anziché in server di grandi dimensioni.
Nel database viene eseguito un subset delle funzionalità normalmente utilizzate negli ambienti locali,
tra cui la compressione del database, le query parallele, gli indici columnstore, il partizionamento delle
tabelle e così via. Per ulteriori informazioni sui limiti delle funzionalità del database SQL di Windows
Azure, vedere Limitazioni delle funzionalità di SQL Server (database SQL di Windows Azure)
(http://msdn.microsoft.com/it-it/library/windowsazure/ff394115.aspx).
Una delle principali differenze tra il database SQL di Windows Azure e SQL Server consiste nel fatto che
il database SQL di Windows Azure espone un servizio multi-tenant a scalabilità orizzontale, nel quale
diverse sottoscrizioni condividono le risorse di uno o più computer in un data center Microsoft.
L'obiettivo è quello di bilanciare il carico globale nel data center spostando occasionalmente i clienti da
un computer all'altro. Tali computer sono server standard basati su rack che consentono di ottimizzare il
rapporto prezzo/prestazioni piuttosto che le prestazioni complessive. Non tutti i nodi del database SQL
di Windows Azure utilizzano hardware di fascia molto alta nella soluzione ospitata.
Quando supera i limiti di un singolo computer, l'applicazione di un cliente deve essere modificata in
modo da distribuire il carico di lavoro su più database (spesso, cioè, su più computer) anziché in un
singolo server. Uno degli svantaggi della flessibilità e della facilità di gestione di questo ambiente è che
talvolta l'applicazione può passare a un altro computer in modo imprevisto. Poiché le sessioni sono
indipendenti dallo stato, nella progettazione dell'applicazione vanno utilizzate tecniche che evitano
singoli punti di errore, inclusi la memorizzazione nella cache in altri livelli, nei casi appropriato, e l'uso
della logica di ripetizione dei tentativi per connessioni e comandi ai fini della resilienza agli errori.
Inoltre, poiché i vari livelli dell'infrastruttura di Windows Azure non si trovano nella stessa subnet di
rete, si verificano alcune piccole differenze di latenza tra le applicazioni client e il database SQL di
Windows Azure. Lo stesso vale nei casi in cui le applicazioni e il database sono ospitati nello stesso data
center fisico. Le tradizionali soluzioni SQL Server di caricamento di dati caratterizzate da un elevato
traffico di rete possono risultare più lente in Windows Azure a causa di queste differenze nella rete
fisica. Chi ha familiarità con i primi modelli di elaborazione client-server potrà adottare le stesse
soluzioni, valutando attentamente i round trip tra i livelli di una soluzione per gestire tutte le evidenti
differenze di latenza.
Considerazioni sull'architettura
Alcune delle problematiche più comuni legate ai pacchetti SSIS riguardano le modalità di gestione degli
errori imprevisti nel corso dell'esecuzione e quelle di riduzione del tempo richiesto per completare un
processo ETL quando è necessario riprendere l'elaborazione dopo un errore. Per le attività del flusso di
controllo, come le attività del file system, è possibile utilizzare checkpoint per riprendere l'esecuzione senza
rielaborare le operazioni già completate. Per istruzioni dettagliate sull'utilizzo dei checkpoint, vedere
Riavvio dei pacchetti tramite checkpoint (http://msdn.microsoft.com/it-it/library/ms140226.aspx).
17
Spesso il flusso di dati è la parte di maggiori dimensioni del pacchetto SSIS. In questa sezione vengono
illustrate le strategie per progettare i pacchetti in modo da prevedere la ripetizione automatica dei
tentativi in caso di errore, nonché progettare la pipeline del flusso di dati in modo da riprendere le
attività a partire dal punto di errore anziché riprovare l'intero flusso di dati.
Ulteriori considerazioni per la gestione degli errori temporanei sulla piattaforma di Windows Azure sono
contenute nel white paper sul ruolo di SQL Server 2012 SSIS nello spostamento di dati ibridi e Windows
Azure, disponibile in MSDN Library nel nodo dedicato ai white paper Microsoft per SQL Server 2012.
Progettazione del riavvio senza perdita di avanzamento nella pipeline
Un aspetto critico della progettazione dei pacchetti riguarda la capacità di configurare il pacchetto in
modo che, in caso di errore, le operazioni del pacchetto eseguite fino a quel punto non vadano perdute.
Per gli elementi del flusso di controllo di un pacchetto questo scopo viene raggiunto tramite l'utilizzo dei
checkpoint. Tuttavia, la capacità di riavviare un flusso di dati senza perdita di avanzamento dipende
esclusivamente dalla progettazione del pacchetto. In questa sezione viene illustrata una strategia per la
progettazione dei pacchetti che consente il riavvio del flusso di dati dal punto di errore e la ripetizione
automatica dei tentativi in modo che l'esecuzione del pacchetto non venga interrotta con esito negativo
in caso di perdita della connessione. La previsione di tali perdite o brevi interruzioni della connessione
nella progettazione è particolarmente importante quando si spostano dati da o verso il database SQL di
Windows Azure.
Il principio di base
Sebbene le prestazioni siano importanti in qualsiasi spostamento di dati, è necessario bilanciare le
esigenze di prestazioni con la quantità di lavoro che ci si può permettere di perdere se si verificano
imprevisti nel corso dello spostamento.
In ogni spostamento di dati è necessario saper determinare esattamente quali dati sono già arrivati a
destinazione e quali no. Nel caso di dati che vengono solo inseriti, questa informazione può essere
spesso determinata tramite la sola chiave primaria. Per altri dati, è possibile basarsi sulla data dell'ultima
modifica. Qualsiasi sia la natura dei dati, nella prima parte della progettazione del riavvio è necessario
definire il modo in cui identificare i dati già presenti nella destinazione, i dati da aggiornare nella
destinazione e i dati ancora da spostare nella destinazione. Stabilito ciò, è possibile segmentare e
ordinare i dati in modo tale da elaborare solo quelli non ancora giunti a destinazione o da ridurre al
minimo la quantità di rielaborazione necessaria in ciascuna fase.
La divisione in blocchi e l'ordinamento permettono di tenere traccia facilmente dei blocchi già elaborati
e, all'interno dei blocchi, dei record già elaborati. Adottando questo approccio non è necessario
confrontare ogni riga di origine con la destinazione per determinare se il record è stato elaborato.
I processi ETL più complessi possono prevedere diversi ambienti di gestione temporanea, ciascuno dei
quali è una destinazione per una fase del processo. In questo tipo di scenari, considerare ognuno degli
ambienti di gestione temporanea una destinazione distinta e progettare il riavvio di ogni segmento del
processo ETL.
18
Esempio con una singola destinazione
Il flusso di dati riavviabile più semplice ha piccole dimensioni e un singolo Integer come chiave primaria.
Quando SQL Server è l'origine, è possibile eseguire una query su questa origine utilizzando la procedura
consigliata di limitare i dati estratti dall'origine. Si consideri la classica tabella di esempio di
AdventureWorks: "Production.TransactionHistory". La tabella presenta la struttura seguente:
CREATE TABLE [Production].[TransactionHistory]
(
[TransactionID]
INT
[ProductID]
INT
[ReferenceOrderID]
INT
[ReferenceOrderLineID]
INT
[TransactionDate]
INT
[TransactionType]
NCHAR(1)
[Quantity]
INT
[ActualCost]
MONEY
[ModifiedDate]
DATETIME
)
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NULL
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
PRIMARY KEY,
In questo esempio la tabella ha un singolo Integer come chiave primaria. Se al momento dello
spostamento i dati sono statici o vengono solo inseriti in questa tabella (senza aggiornamenti),
è sufficiente la chiave primaria per determinare se una riga è stata inserita nella destinazione.
Il confronto di ogni valore di chiave primaria con la destinazione è relativamente costoso. È consigliabile
invece utilizzare una strategia di ordinamento dei dati nel file di origine in base alla colonna TransactionID.
Con questa strategia, le uniche informazioni necessarie sono se i dati vengano o meno elaborati in
ordine e quale sia il TransactionID più alto di cui è stato eseguito il commit nella destinazione.
In un pacchetto SSIS questo scopo può essere ottenuto effettuando le operazioni seguenti.
1. Controllare la chiave più alta nella destinazione.
2. Compilare la query dall'origine per estrarre solo i record con un TransactionID maggiore del
TransactionID più alto nella destinazione.
3. Utilizzare un'istruzione ORDER BY TransactionID nella query di origine per assicurarsi che il
confronto con il TransactionID più alto rimanga valido al successivo avvio del pacchetto.
In SSIS, utilizzando un'origine dati relazionale come origine, è possibile utilizzare un'attività Esegui SQL
per estrarre il valore più alto in una variabile nel pacchetto. Prevedere però l'eventualità che nella
destinazione non sia presente alcuna riga.
SELECT MAX(TransactionID) FROM Production.TransactionHistory
Per recuperare il valore massimo di TransactionID, tenere presente che il risultato di una tabella vuota
è null. Ciò può comportare alcuni problemi nella logica utilizzata nel pacchetto. Un approccio migliore
consiste innanzitutto nell'utilizzare un'attività Esegui SQL nell'origine e cercare il valore minimo non
elaborato di TransactionID nell'origine. Quindi, eseguire la query per determinare il valore massimo di
TransactionID nella destinazione o, se non esiste, utilizzare un valore inferiore al TransactionID minimo
dell'origine. Compilare la query di origine per estrarre solo i record maggiori di tale valore, senza dimenticare
di includere ORDER BY TransactionID.
19
NOTA: a livello logico si ottengono gli stessi risultati semplicemente recuperando un valore dalla
destinazione e utilizzando una funzione ISNULL o l'istruzione CASE nella clausola WHERE della query di
origine, ma questo modo di procedere può causare problemi di prestazioni, soprattutto quando le query
di origine sono piuttosto complesse. Evitare di utilizzare questo metodo e trovare invece un valore da
utilizzare con sicurezza come limite inferiore, quindi compilare la query di origine usando questo valore.
NOTA: quando l'origine è SQL Server, l'uso di una clausola ORDER BY su un indice cluster non provoca
attività di elaborazione supplementari per l'ordinamento. I dati sono già ordinati in modo da poter
essere recuperati senza eseguire un'istruzione SORT. Se anche i dati nella destinazione dispongono
di un indice cluster nella stessa colonna, l'ordinamento nell'origine ottimizza la query di origine e gli
inserimenti nella destinazione. Inoltre, assicura l'ordine all'interno della pipeline SSIS, consentendo
pertanto il riavvio del flusso di dati dal punto di errore.
Per compilare questo pacchetto di esempio, effettuare le operazioni seguenti.
1. Creare un nuovo pacchetto in un progetto nuovo o esistente. Rinominare il pacchetto
"SimpleRestart".
2. Creare gestioni connessioni per connettersi all'origine e alla destinazione. Per questo esempio
viene creata una gestione connessione OLE DB sia per il server di origine che per il server di
destinazione.
3. Trascinare una nuova attività Esegui SQL nell'area del flusso di controllo e rinominarla "Pull Min
TransactionID From Source".
4. Creare una variabile SSIS a livello del pacchetto e denominarla minTransactionIDAtSource.
Questa variabile verrà utilizzata per archiviare il valore estratto dall'attività Esegui SQL appena
aggiunta. Verificare che il tipo di dati sia Int32, perché corrisponda al valore di TransactionID
della tabella, e impostare un valore iniziale appropriato.
5. Configurare Pull Min TransactionID From Source come indicato di seguito.
a. Modificare l'attività e impostare Connessione come gestione connessione per il server
di origine.
b. Sebbene sia possibile archiviare SQLStatement in una variabile, per questo esempio
lasciare SQLSourceType impostato come Input diretto. Aprire la finestra di input per
SQLStatement e immettere la query seguente:
SELECT ISNULL(MIN(TransactionID), 0) FROM Production.TransactionHistory
NOTA: testare le query SQL prima di immetterle negli editor di SSIS. Poiché non esiste
una vera e propria Guida per il debug nelle finestre dell'editor di query SSIS, in questo
modo si semplifica il debug.
20
Figura 5. Configurazione dell'attività Esegui SQL per trovare il valore minimo di TransactionID nell'origine.
c. Chiudere la finestra Immetti query SQL facendo clic su OK.
d. Impostare la proprietà ResultSet su Riga singola.
e. Nel riquadro sinistro dell'editor attività Esegui SQL fare clic su Set dei risultati per
configurare la modalità di acquisizione del valore dalla query.
f. Fare clic sul pulsante Aggiungi per aggiungere un set dei risultati.
g. Nel nuovo set dei risultati impostare Nome risultato su 0. Verificare che in Nome
variabile venga visualizzato User::minTransactionIDAtSource (variabile creata nel
passaggio 4). In questa variabile verrà archiviato il risultato della query SQL.
h. Chiudere l'editor attività Esegui SQL. Non dovrebbero essere visualizzati errori
sull'attività dopo la chiusura.
6. Trascinare un'altra attività Esegui SQL nell'area del flusso di controllo. Denominare l'attività Pull
Max TransactionID from Destination. Connettere il vincolo di precedenza di esito positivo da
Pull Min TransactionID From Source alla nuova attività.
7. Creare una nuova variabile con ambito a livello del pacchetto. Denominare la nuova variabile
maxTransactionIDAtDestination. Assegnare alla variabile il tipo di dati Int32 in modo che
corrisponda al tipo di dati di TransactionID e specificare un valore iniziale appropriato.
8. Aprire l'Editor attività Esegui SQL per la nuova attività ed effettuare le operazioni seguenti:
a. Impostare la proprietà ResultSet su Riga singola.
b. Impostare l'attività come gestione di connessione del server di destinazione.
c. Impostare SQLSourceType come Input diretto.
21
d. Per SQLStatement utilizzare SELECT ISNULL(MAX(TransactionID), ?) FROM
Production.TransactionHistory
NOTA: ? è un parametro di query, il cui valore viene impostato temporaneamente.
e. Chiudere l'editor di query facendo clic su OK, quindi scegliere Mapping parametri nel
riquadro sinistro dell'Edtor attività Esegui SQL.
f. Fare clic su Aggiungi per aggiungere un parametro singolo.
i. Per Nome variabile scegliere User::minTransactionIDAtSource.
ii. Per Direzione scegliere Input.
iii. Il tipo di dati deve essere LONG, che in questo contesto corrisponde a un Integer
a 32 bit.
iv. Impostare Nome parametro su 0. Si noti che il nome del carattere deve essere
modificato in 0, altrimenti verrà generato un errore.
g. Fare clic su Set dei risultati nel riquadro sinistro. Fare clic sul pulsante Aggiungi per
aggiungere un nuovo set dei risultati.
i. Modificare Nome risultato in 0.
ii. In Nome variabile scegliere User::maxTransactionIDAtDestination, ovvero la
variabile creata nel passaggio 7. Questa variabile conterrà il risultato della query
immessa dopo l'esecuzione di questa attività.
NOTA: il passaggio successivo dipende dal tipo di origine utilizzato nel flusso di dati.
Per un'origine OLE DB è possibile utilizzare una variabile SSIS che contiene un'istruzione SQL
come query. Al contrario, è possibile parametrizzare una connessione ADO.NET in modo da
utilizzare un parametro di pacchetto o di progetto come query di origine. In questo primo
esempio verrà utilizzata un'origine OLE DB con una variabile che contiene la query di origine.
9. Trascinare un'attività Flusso di dati nell'area del flusso di controllo. Rinominarla in Main data
move e connettere il vincolo di precedenza di esito positivo da Pull Max TransactionID From
Destination all'attività Flusso di dati.
Quando il pacchetto viene eseguito a questo punto, i valori che è necessario saper individuare
per stabilire il punto di inizio dell'esecuzione corrente sono archiviati. È ora necessario
impostare una variabile che contenga la query di origine SQL.
10. Creare una nuova variabile con ambito a livello del pacchetto. Denominare la variabile
sourceQuery e impostare il tipo di dati su String. Utilizzare un'espressione per ottenere
dinamicamente il valore della variabile in fase di esecuzione in base al valore determinato
come punto iniziale della query, effettuando le operazioni seguenti:
a. Fare clic sul pulsante con i puntini di sospensione a destra della colonna Espressione per
visualizzare il Generatore di espressioni.
b. Espandere il nodo Variabili e parametri nella finestra superiore sinistra del Generatore
di espressioni. Utilizzare la variabile User::MaxTransactionIDAtDestination creata nel
passaggio 7, che dovrebbe essere disponibile nell'elenco di variabili. Questa variabile
è di tipo Int32, ma dovrà essere utilizzata come parte di una variabile di tipo String.
A tale scopo, è necessario eseguirne il cast come tipo di dati DT_WSTR. Nel riquadro
superiore destro espandere il nodo Cast di tipo per individuare il cast di tipo (DT_WSTR,
<<lunghezza>>).
22
c. Digitare la query nel riquadro Espressione. Nei punti in cui è necessario inserire il nome
della variabile o il cast di tipo è possibile trascinare l'elemento desiderato dalla finestra
appropriata al riquadro Espressione. In questo modo si riduce il numero di errori di
digitazione nell'editor. Creare l'espressione nel modo seguente:
"SELECT TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID,
TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate FROM
Production.TransactionHistory WHERE TransactionID > " + (DT_WSTR, 12 )
@[User::maxTransactionIDAtDestination] + " ORDER BY TransactionID"
Si noti l'utilizzo del cast di tipo per modificare il valore Integer in un valore String di 12
caratteri al massimo.
Si utilizza una lunghezza di 12 caratteri perché è sufficiente a contenere l'intervallo
completo di valori SQL INT, inclusi i negativi se applicabili. Per BIGINT saranno necessari
22 caratteri. Assegnare alla variabile di tipo carattere la dimensione adeguata al tipo di
dati recuperato.
Dopo aver digitato l'espressione, fare clic sul pulsante Valuta espressione per assicurarsi
che possa essere analizzata correttamente in SSIS. Si noterà che il valore iniziale di
maxTransactionIDAtDestination è stato correttamente inserito nell'espressione valutata.
Questo valore verrà impostato in modo appropriato in fase di esecuzione.
Assicurarsi di includere la clausola ORDER BY nell'istruzione SQL, poiché solo con questa
clausola si ottiene un ordine garantito da un database relazionale. Il metodo di riavvio
creato dipenderà dall'ordine dei valori di chiave.
d. Chiudere il Generatore di espressioni facendo clic sul pulsante OK. L'istruzione SQL
costruita in modo dinamico è ora archiviata nella variabile SourceQuery.
11. Fare doppio clic sull'attività Flusso di dati Main data move per aprire l'area di progettazione del
flusso di dati.
12. Trascinare un'origine OLE DB nell'area di progettazione del flusso di dati. Rinominarla in
Retrieve from TransactionHistory.
NOTA: poiché il nome di un componente del flusso di dati non può contenere punti, non è possibile
utilizzare il nome completo Pull From Production.TransactionHistory. Se non si utilizzano già
i caratteri di sottolineatura per i nomi di tabella, è possibile sostituire il punto con il carattere
di sottolineatura nella convenzione di denominazione SSIS.
23
13. Fare doppio clic sull'origine Retrieve From TransactionHistory per aprire lo strumento Editor
origine OLE DB.
a. Per Gestione connessione OLE DB scegliere la gestione connessione Server di origine
creata.
b. Nell'elenco a discesa Modalità di accesso ai dati selezionare Comando SQL da variabile.
c. Nell'elenco a discesa Nome variabile selezionare la variabile User::sourceQuery creata
nel passaggio 10.
d. Fare clic su Anteprima per verificare che la query possa essere eseguita sul server di
origine.
e. Nella pagina Colonne dell'editor verificare che tutte le colonne siano selezionate.
f. Fare clic su OK per chiudere lo strumento Editor origine OLE DB.
14. Trascinare una destinazione OLE DB sull'area del flusso di controllo. Rinominarla in
TransactionHistory Destination. Connettere l'origine Retrieve From TransactionHistory alla
nuova destinazione. Aprire la destinazione facendo doppio clic e configurarla effettuando le
operazioni seguenti.
a. Selezionare la gestione connessione Server di destinazione nell'elenco a discesa
Gestione connessione OLE DB.
b. Per Modalità di accesso ai dati scegliere Tabella o vista - Caricamento rapido,
se l'opzione non è già selezionata.
c. In Nome tabella o vista selezionare un'opzione nell'elenco a discesa oppure digitare il
nome del server di destinazione. In questo caso si tratta di
Production.TransactionHistory.
d. Se si utilizza la definizione di TransactionHistory fornita sopra nell'esempio, è possibile
mantenere le impostazioni predefinite nella pagina Gestione connessione. Se si utilizza
il database AdventureWorks, sarà necessario selezionare Mantieni valori Identity.
e. Nella pagina Mapping dell'editor eseguire il mapping delle colonne.
NOTA: in quasi tutti i casi è consigliabile inviare le righe di errore a un file di
destinazione e reindirizzare l'output. Questa operazione non è necessaria per
dimostrare il riavvio sulla pipeline. Per i passaggi sulla creazione di un flusso degli errori
e sul reindirizzamento delle righe, vedere Configurazione di un output degli errori in un
componente del flusso di dati (http://msdn.microsoft.com/it-it/library/ms140083.aspx).
f. Fare clic su OK per chiudere lo strumento Editor destinazione OLE DB.
15. Testare questo approccio avviando un'importazione, arrestandola e avviandola nuovamente.
Ogni volta il flusso di dati dovrebbe riprendere dalla riga successiva da spostare, ignorando
quelle precedenti.
24
In questo modo è stato compilato un pacchetto semplice in grado di riavviare il flusso di dati dopo un
errore. Il pacchetto verrà utilizzato come punto di partenza per l'esempio sulla progettazione di un
pacchetto in grado di ripetere i tentativi. Si noti che non è opportuno salvare checkpoint in nessuna
delle attività del flusso di controllo. Se il pacchetto viene interrotto e deve essere riavviato, è necessario
eseguire i componenti Pull Min TransactionID From Source e Pull Max TransactionID From Destination
per trovare il punto esatto di interruzione del flusso di lavoro nell'esecuzione precedente. La progettazione
di un pacchetto in grado di individuare lo stato di avanzamento e riavviare il flusso in questo modo è una
buona prassi in tutte le situazioni in cui le caratteristiche dei dati consentono di individuare il punto di
interruzione del flusso dei dati. Questa prassi diventa particolarmente importante in un ambiente come
il cloud, più esposto alle incertezze della rete.
Esempio con più destinazioni
Questo principio può essere esteso ai flussi di dati con più destinazioni. Uno scenario particolarmente
adatto è quello dello spostamento dei dati in un modello di dati partizionati, tipico per il database SQL
di Windows Azure. In questi casi viene utilizzata una suddivisione condizionale per inviare ogni riga alla
destinazione corretta oppure, per i dati di riferimento, è possibile utilizzare un multicast per inviare
tutti i dati a tutte le destinazioni. Di seguito sono riportati i principi fondamentali da ricordare per la
progettazione del riavvio.
Destinazioni diverse possono trovarsi a punti diversi di avanzamento al momento dell'errore.
Per questo motivo, è necessario trovare il valore di chiave massimo inserito in ogni destinazione.
Creare una variabile che contenga il valore massimo di ogni destinazione.
Il punto iniziale nell'origine sarà il record successivo al valore di chiave minimo inserito correttamente
nelle destinazioni. Se ad esempio i valori di chiave più alti inseriti in un set di partizioni sono quelli
indicati di seguito, il flusso di dati nell'origine deve riprendere dal record successivo a 1000.
o Shard00: valore di chiave 1000
o Shard01: valore di chiave 1100
o Shard02: valore di chiave 1050
Poiché in questo modo è possibile che alcuni dati vengano estratti più di una volta, è necessario
applicare un filtro per ogni destinazione per impedire una violazione di chiave primaria. Utilizzare una
trasformazione Suddivisione condizionale per filtrare i valori di chiave che sono già stati elaborati.
Per l'esempio di partizionamento orizzontale precedente, è necessario creare variabili denominate
maxTransactionIDAtShard00, maxTransactionIDAtShard01 e maxTransactionIDAtShard02.
Nelle attività Esegui SQL trovare i valori da archiviare in ogni variabile. Nella trasformazione
Suddivisione condizionale è possibile definire output denominati Shard00, Shard01 e Shard02.
Le espressioni per gli output dovrebbero essere simili alle seguenti.
ShardID == 0 && TransactionID > @[User::maxTransactionIDAtShard00]
ShardID == 1 && TransactionID > @[User::maxTransactionIDAtShard01]
ShardID == 2 && TransactionID > @[User::maxTransactionIDAtShard02]
25
Se nella pipeline vengono inserite righe con un valore minore del TransactionID di una specifica
partizione, il record indirizzato a tale partizione non viene inviato e non si verifica alcuna
violazione di chiave primaria. Configurare i record in modo che vengano indirizzati all'output
predefinito o a un output non connesso per impedirne l'ulteriore elaborazione.
Figura 2. Suddivisione condizionale configurata per 3 partizioni e per il riavvio senza violazioni di chiave primaria nella
destinazione. All'inizio dell'esecuzione, il valore di chiave massimo in ogni destinazione viene archiviato nelle variabili
maxTransactionIDAtShardXX. Le righe nella pipeline con un valore di chiave troppo basso per una specifica partizione non
verranno inviate alla destinazione e non verranno rielaborate, ma passeranno invece all'output predefinito. Poiché l'output
predefinito non è connesso, le righe non avanzeranno ulteriormente nella pipeline.
26
Altri suggerimenti per il riavvio
Se la destinazione è un file, utilizzare una variabile per rappresentare il nome del file e rendere
ogni nome significativo, ad esempio con un numero progressivo o un timestamp aggiunto alla
fine. Elaborare blocchi di dati diversi in file diversi. Se l'elaborazione avviene nello stesso ordine
e i blocchi vengono definiti in modo deterministico, è possibile verificare quali file sono già stati
creati e individuare il punto iniziale di conseguenza. A seconda delle esigenze e della logica,
potrebbe essere necessario spostare, rinominare o eliminare file già elaborati.
Se l'origine è costituita da più file, elaborare separatamente ogni file e tenere traccia del file
corrente. Al riavvio è possibile riprendere dal file successivo all'ultimo file elaborato
correttamente. Utilizzare un criterio di ordinamento basato sui nomi di file o le date per
assicurarsi che i file vengano elaborati ogni volta nello stesso ordine e poter quindi individuare
facilmente l'ultimo file elaborato correttamente.
Quando SQL Server è l'origine, è possibile utilizzare un tipo di dati String SSIS per eseguire una
query e ottenere i valori Integer. Se è possibile eseguire il cast della stringa come Integer SQL,
questo avverrà durante l'ottimizzazione. In base a questo principio, è possibile modificare nel
tipo di dati String le variabili MaxTransactionIDAtDestination e MinTransactionIDAtSource nel
pacchetto di esempio, modificare i tipi di parametri di input in Pull Max TransactionID From
Destination e utilizzare questo pacchetto come modello, che funzionerà anche con chiavi
primarie di tipo carattere. Non utilizzare un tipo di dati SQL_VARIANT con nessun altro tipo di
dati in SQL, o verranno eseguite analisi complete nel corso del recupero dei valori di chiave
massimi e minimi.
Se l'origine è un file oppure un'origine in cui non è possibile eseguire query con WHERE
o qualsiasi altro tipo di clausola filtro, nel flusso di dati effettuare le operazioni seguenti.
1. Inserire una trasformazione Suddivisione condizionale tra i componenti di origine e di
destinazione.
2. Nella suddivisione condizionale configurare un output. Denominarlo Trash o utilizzare
un altro nome che indichi che si tratta di righe indesiderate.
3. Per la condizione con la quale recuperare le righe inviate alla destinazione utilizzare
un'espressione per filtrare le righe già presenti nella destinazione. Ad esempio,
l'espressione per l'esempio precedente sarebbe la seguente.
[TransactionID] <= @[User::maxTransactionIDAtDestination]
4. Non connettere l'output Trash ad alcun componente, a meno che non sia
assolutamente necessario valutare tali righe. Il suo solo scopo è filtrare le righe già
elaborate nell'ultima esecuzione.
Questa procedura non impedisce la lettura delle righe già elaborate nel file di output,
bensì che le righe vengano inviate di nuovo alla destinazione, risparmiando così
larghezza di banda di rete. Poiché queste righe non vengono inviate alla destinazione,
non è necessario eliminare dati dalla destinazione.
27
In presenza di chiavi composte, utilizzare sottoquery correlate per trovare l'esatto punto di
interruzione. Assicurarsi di ordinare i dati in base alle stesse chiavi. Di seguito è riportato un
esempio.
SELECT MAX(EmployeeID) AS EmployeeID,
(SELECT MAX(EffectiveDate)
FROM HumanResources.EmployeeHistory i_forDate
WHERE h.EmployeeID = i_forDate.EmployeeID) as EffectiveDate,
(SELECT MAX(EffectiveSequence)
from HumanResources.EmployeeHistory i_forSeq
where h.EmployeeID = i_forSeq.EmployeeID
and h.EffectiveDate = i.ForSeq.EffectiveDate) as EffectiveDate
FROM HumanResources.EmployeeHistory h
ORDER BY EmployeeID, EffectiveDate, EffectiveSequence
Si noti l'importanza dell'ordine nell'esempio. Se si spostassero tutti i record di tutti i dipendenti
e questo fosse l'ordine delle chiavi, il criterio di ordinamento sarebbe corretto. Tuttavia, se tutti
o alcuni dipendenti fossero già presenti nella destinazione e si volessero importare solo le
modifiche apportate dall'ultima importazione, l'ordinamento dovrebbe essere basato su
EffectiveDate, EffectiveSequence ed EmployeeID per riprendere dall'ultima esecuzione.
Analizzare gli elementi da importare per definire l'ordine che consente di individuare il punto
da cui riprendere l'esecuzione.
Progettazione della logica di ripetizione dei tentativi senza intervento
manuale
Nei componenti di origine e di destinazione SSIS la logica di ripetizione dei tentativi non è inclusa
direttamente. Tuttavia, gli sviluppatori di pacchetti SSIS hanno a disposizione diversi metodi per gestire
questo aspetto della progettazione. Quando si progettano i pacchetti SSIS in modo che possano essere
riavviati senza perdita di avanzamento nel flusso di dati, è anche possibile configurare la ripetizione
automatica dei tentativi dopo un errore, tenendo presenti alcune considerazioni aggiuntive. In casi
quali lo spostamento dei dati da e verso database SQL di Windows Azure, la ripetizione automatica
dei tentativi potrebbe essere necessaria per la gestione degli errori temporanei, come la limitazione di
risorse o connessioni. In questa sezione vengono ampliati i concetti illustrati nella sezione precedente
e viene descritto un esempio di pacchetto con una semplice logica di ripetizione dei tentativi. Viene poi
illustrato l'utilizzo di un approccio a blocchi in cui la ripetizione dei tentativi viene configurata per ogni
blocco in modo che il pacchetto risulti più affidabile e robusto nei momenti di maggiore probabilità degli
errori temporanei.
Per ulteriori informazioni sulla limitazione SQL, vedere la Guida all'ottimizzazione di elasticità
e prestazioni del database SQL di Windows Azure (http://social.technet.microsoft.com/wiki/
contents/articles/3507.windows-azure-sql-database-performance-and-elasticity-guide.aspx).
28
Integrazione della ripetizione dei tentativi
Nel caso di un singolo pacchetto, effettuare le operazioni seguenti per integrare la ripetizione dei tentativi.
1. Determinare il numero massimo di tentativi effettuati dal pacchetto prima dell'esito negativo.
Per questo esempio viene stabilito che un componente potrà effettuare un massimo di 5 tentativi
prima che il pacchetto restituisca un errore. Creare una variabile nel pacchetto denominata
maxNumOfRetries. Assegnare alla variabile il tipo Int e il valore 5. Tali informazioni verranno
utilizzate nelle espressioni del pacchetto.
2. Impostare una variabile per archiviare lo stato di esito positivo. Creare una nuova variabile nel
pacchetto SSIS e denominarla attemptNumber.
3. Utilizzare un ciclo FOR per effettuare il numero massimo di tentativi se il flusso di dati non dà
esito positivo.
29
4. Inserire l'attività Flusso di dati nel ciclo FOR.
5. Impostare la proprietà MaximumErrorCount del ciclo FOR sul numero massimo di tentativi da
eseguire sul flusso di dati in modo che un esito positivo dopo un nuovo tentativo impedisca
l'errore del pacchetto. A tale scopo, utilizzare un'espressione che includa la variabile
maxNumOfRetries impostata nel passaggio 1.
6. Utilizzare le attività Esegui SQL come indicato nella sezione precedente per trovare i valori di
chiave minimi nell'origine e quelli massimi nella destinazione. Per una ripetizione di tentativi
semplice, questa operazione può essere effettuata all'interno del ciclo FOR. Per esempi più
avanzati, questo passaggio può essere eseguito in un altro punto del flusso di controllo prima
del ciclo FOR.
7. Inserire le attività Esegui SQL nel ciclo FOR.
8. Connettere il vincolo di esito positivo dall'attività Esegui SQL all'attività Flusso di dati.
9. Connettere il vincolo di precedenza di esito positivo dall'attività Flusso di dati a un'attività
Script che imposti la variabile di stato con esito positivo su true per interrompere il ciclo FOR.
Di seguito viene illustrato un esempio della configurazione dell'attività Script.
30
10. Connettere un vincolo di precedenza di errore dall'attività Flusso di dati a un'altra attività Script
per un periodo prevedibilmente più lungo rispetto al problema più frequente o più critico
stimato. In questo esempio verranno impostati 10 secondi per gestire l'eventualità della
limitazione. Impostare la variabile di esito positivo su false.
31
11. Per ogni attività nel ciclo FOR, impostare la proprietà FailPackageOnFailure su false. Con questa
configurazione, verrà segnalato un errore e il pacchetto verrà interrotto con esito negativo solo
nel caso di mancata riuscita del ciclo FOR una volta utilizzati tutti i tentativi configurati.
12. Configurare le attività Esegui SQL dopo l'errore per controllare nuovamente i valori di chiave
minimi nell'origine e quelli massimi nella destinazione e poter riprendere dall'avanzamento
senza ripetere le operazioni. Impostare le attività come descritto nella sottosezione
Progettazione del riavvio della precedente sezione Progettazione del riavvio senza perdita
dell'avanzamento nella pipeline di questo documento.
32
Se ogni volta che si verifica un errore nel flusso di dati non viene raggiunto il numero massimo di
tentativi, il processo torna al punto corrispondente alla ricerca del valore di chiave massimo nella
destinazione. L'elaborazione continua dal punto di cui viene attualmente eseguito il commit nella
destinazione. Se il flusso di dati include più destinazioni, iniziare dopo che il valore di chiave minimo
è arrivato a destinazione e utilizzare una suddivisione condizionale o un flusso di errori per gestire le
righe che causerebbero una violazione di chiave primaria.
Opzioni di ottimizzazione delle prestazioni di SSIS
Prima di considerare i suggerimenti per l'ottimizzazione delle prestazioni ETL, è opportuno esaminare
nell'insieme i componenti e le parti mobili inclusi nel processo. In genere il processo ETL è costituito da
uno o più dei componenti indicati di seguito:
Origine dati: fornisce i dati.
Pacchetto in esecuzione sul server SSIS: esegue l'estrazione dei dati dall'origine dati,
l'elaborazione delle trasformazioni se necessario e il caricamento dei dati nella destinazione.
Destinazione: riceve i dati. La destinazione è in genere un database con una o più tabelle che
ricevono i dati.
Tutte queste parti principali interagiscono sull'interfaccia di rete e si passano i dati. Uno dei primi
passaggi dell'ottimizzazione delle prestazioni ETL consiste nel verificare che la rete funzioni nel miglior
modo possibile.
Per ulteriori informazioni sul server SSIS, vedere Server Integration Services (SSIS)
(http://msdn.microsoft.com/it-it/library/gg471508.aspx).
Ottimizzazione delle impostazioni di rete
Due sono i livelli che possono determinare il modo in cui i dati vengono trasmessi tra le parti definite
sopra: la configurazione della rete fisica e le impostazioni di connessione.
33
Impostazioni di rete
La quantità di dati che possono essere trasmessi in rete contemporaneamente è definita dal frame
Ethernet. Ogni frame deve essere elaborato, il che comporta un certo utilizzo di risorse hardware e
software. Aumentando le dimensioni dei frame ove la scheda di rete lo consenta, è possibile inviare più
byte con un minore overhead sulla CPU e aumentare la velocità effettiva riducendo il numero di frame
da elaborare.
Un frame Ethernet può trasportare fino a 9000 byte. In questo caso viene chiamato frame jumbo.
Per poter utilizzare i frame jumbo, è necessario modificare le impostazioni delle schede di interfaccia di
rete. Come nell'esempio seguente, la proprietà MaxJumboBuffers deve essere impostata su 8192 per
consentire l'utilizzo dei frame jumbo.
Nota: quando si modificano le impostazioni della scheda di interfaccia di rete per utilizzare
i frame jumbo, verificare che l'infrastruttura di rete supporti questo tipo di frame.
Dimensioni di pacchetto
SQL Server supporta fino a 32676 byte in un unico pacchetto di rete SSIS. In genere se un'applicazione
prevede dimensioni di pacchetto predefinite diverse, il valore predefinito sostituisce l'impostazione di
SQL Server. Si consiglia pertanto di impostare la proprietà Packet Size della gestione connessione di
destinazione nel pacchetto SSIS sulle dimensioni di pacchetto predefinite dell'applicazione.
Per modificare questa proprietà, fare clic con il pulsante destro del mouse sulla gestione connessione in
Progettazione SSIS e scegliere Modifica. Nella finestra di dialogo Gestione connessione fare clic su Tutto.
34
Impostazioni del pacchetto SSIS
Oltre alle impostazioni della stringa di connessione, è possibile modificarne anche altre per migliorare le
funzionalità di elaborazione di SQL Server.
Per il flusso di dati SSIS vengono riservati alcuni buffer di memoria per l'elaborazione dei dati.
Se si dispone di server dedicati con più core e maggiore memoria, è spesso possibile modificare le
impostazioni di memoria predefinite per SSIS in modo da sfruttare meglio le capacità del server SSIS.
Di seguito sono riportate le impostazioni di memoria SSIS che è consigliabile modificare.
35
DefaultBufferSize
DefaultBufferMaxRows
EngineThreads
DefaultBufferSize e DefaultBufferMaxRows sono correlate. Il motore flusso di dati tenta di stimare le
dimensioni di una singola riga di dati. Queste dimensioni vengono moltiplicate per il valore archiviato in
DefaultBufferMaxRows, quindi il motore flusso dati tenta di riservare il blocco appropriato di memoria
per il buffer.
[Valore delle dimensioni del buffer] = [Dimensioni di una singola riga di dati] x
[DefaultBufferMaxRows]
Se il valore delle dimensioni del buffer è maggiore dell'impostazione DefaultBufferSize, viene diminuito
il numero di righe di dati.
Se il valore delle dimensioni minime del buffer calcolate internamente è maggiore del valore delle
dimensioni del buffer, il numero di righe di dati viene aumentato. Tuttavia, il valore di
DefaultBufferMaxRows non viene superato.
Quando si modificano le impostazioni DefaultBufferSize e DefaultBufferMaxRows, è bene prestare
attenzione ai valori che provocheranno la scrittura dei dati sui dischi da parte del motore flusso di dati.
Il page out della memoria sul disco del server SSIS influisce negativamente sulle prestazioni di
esecuzione del pacchetto SSIS. Controllando il contatore dei buffer con spooling è possibile stabilire se
i buffer dei dati vengono scritti temporaneamente sul disco quando un pacchetto è in esecuzione.
Per ulteriori informazioni sui contatori delle prestazioni dei pacchetti SSIS e su come ottenere le
statistiche dei contatori, vedere Contatori delle prestazioni (http://msdn.microsoft.com/itit/library/ms137622.aspx).
36
L'impostazione EngineThreads indica quanti thread possono essere utilizzati per l'esecuzione di
un'attività. Quando si utilizza il server multicore, è consigliabile aumentare il valore predefinito 10.
Indipendentemente dal valore di questa proprietà, non verranno comunque utilizzati più thread del
necessario. Il motore può inoltre utilizzare un numero di thread maggiore di quello specificato da questa
proprietà, se necessario per evitare problemi di concorrenza. Per i pacchetti complessi è consigliabile
iniziare con un minimo di 1 thread del motore per ogni albero di esecuzione, senza però scendere sotto
il valore predefinito 10.
Per ulteriori informazioni sulle impostazioni, vedere Funzionalità delle prestazioni del flusso di dati
(http://msdn.microsoft.com/it-it/library/ms141031.aspx).
Considerazioni speciali per i dati BLOB
Quando in una pipeline SSIS è presente una quantità di dati maggiore rispetto alle dimensioni
preimpostate del buffer della pipeline, viene eseguito lo spooling dei dati. Questo influisce
negativamente sulle prestazioni, in particolare quando si gestiscono dati BLOB, ad esempio XML, di tipo
testo o immagine. Quando i dati BLOB sono nella pipeline, SSIS riserva una metà di un buffer ai dati
all'interno di righe e un'altra metà ai dati BLOB. I dati BLOB che restano esclusi dalla metà del buffer
vengono sottoposti a spooling. È quindi opportuno intraprendere le azioni seguenti per ottimizzare
i pacchetti SSIS in previsione dell'inserimento di dati BLOB nella pipeline:
1. Modificare il valore di BLOBTempStoragePath e BufferTempStoragePath in modo che puntino
a un'unità a prestazioni elevate. Per impostazione predefinita, gli oggetti sottoposti a spooling
vengono scritti in file temporanei nella directory definita dalle variabili di ambiente TEMP
o TMP, che per impostazione predefinita si trova sull'unità del sistema operativo. In genere
l'unità del sistema operativo non è un'unità a prestazioni elevate. Per assicurarsi che i file di
spooling dei dati temporanei vengano scritti su un'unità a prestazioni elevate, impostare il
valore di BLOBTempStoragePath nelle proprietà dell'attività Flusso di dati SSIS su una directory
di una risorsa di archiviazione di tipo adeguato. Come per tutte le proprietà di SSIS, è possibile
impostare questo valore utilizzando un'espressione.
2. Impostare DefaultBufferSize e DefaultBufferMaxRows in modo da ridurre al minimo la
necessità di spooling. Poiché i dischi sono in genere il componente più lento sul server e hanno
normalmente una velocità molto più bassa del processore o della memoria, è possibile che un
uso poco efficiente dello spazio del buffer si riveli in realtà più efficiente di una configurazione
che consente lo spooling. Per ridurre lo spooling dovuto ai dati BLOB, utilizzare un metodo come
quello indicato di seguito per determinare i valori di DefaultBufferSize e DefaultBufferMaxRows
quando il flusso di dati contiene dati BLOB.
a. Determinare il valore di MaxBufferSize. Poiché il flusso di dati conterrà dati BLOB,
è consigliabile iniziare con il valore massimo consentito di 100 MB o 104.857.600 byte.
b. Dividere questo numero per 2. Nell'esempio il risultato è 52.428.800 byte, che equivale
alla metà del buffer che può contenere dati BLOB.
37
c. Selezionare una dimensione da utilizzare come dimensione stimata dei dati BLOB da
elaborare nel flusso di dati. Un buon punto di partenza per determinare questa
dimensione consiste nel calcolare la lunghezza media + 2 deviazioni standard sulla
lunghezza media di tutti i dati BLOB che saranno presenti in un buffer. Questo valore
conterrà approssimativamente il 98% di tutti i dati BLOB. Poiché è probabile che un
singolo buffer SSIS contenga più righe, questo metodo impedisce quasi del tutto il
verificarsi di operazioni di spooling.
Se l'origine è SQL Server, è possibile utilizzare una query analoga alla seguente
per acquisire la lunghezza.
SELECT CAST
(
AVG(DATALENGTH(ColName))
+ (2 * STDEV(DATALENGTH(Demographics)))
AS INT
) AS Length FROM SchemaName.TableName
Se la tabella è troppo grande per consentire l'esecuzione di query sul set di dati
completo per ottenere i valori di media e deviazione standard, utilizzare un
metodo simile a quello descritto nella pagina relativa al campionamento casuale
in T-SQL (http://msdn.microsoft.com/it-it/library/aa175776(v=SQL.80).aspx) per
individuare un esempio in cui trovare la lunghezza da utilizzare.
d. Dividere il numero ottenuto nel passaggio b per il numero ottenuto nel passaggio c.
Utilizzare il risultato o un numero leggermente minore come valore di
DefaultBufferMaxRows per l'attività Flusso di dati.
SUGGERIMENTO: DefaultBufferMaxRows e MaxBufferSize sono entrambe configurabili tramite
espressioni. È possibile avvalersi di questa possibilità per i set di dati in cui la natura statistica della
lunghezza dei dati BLOB può cambiare spesso o per la creazione di pacchetti modello per impostare
questi valori in fase di esecuzione. Per impostare questi valori come dinamici, effettuare le operazioni
seguenti.
1. Creare una nuova variabile a livello di pacchetto. Denominarla DefaultMaxRowsInBuffer.
Mantenere il tipo di dati Int32. Creare una variabile simile se si desidera impostare
dinamicamente la proprietà MaxBufferSize.
2. Utilizzare un'attività Esegui SQL o un'attività Script per trovare il valore da utilizzare per
DefaultBufferMaxRows. Archiviare il valore calcolato nella variabile DefaultMaxRowsInBuffer
creata nel passaggio 1.
NOTA: per ulteriori informazioni su come utilizzare un'attività Esegui SQL per recuperare un
singolo valore in una variabile SSIS, vedere Set di risultati nell'attività Esegui SQL
(http://technet.microsoft.com/it-it/library/cc280492.aspx).
3. Nella casella delle proprietà dell'attività Flusso di dati in cui si desidera impostare
DefaultBufferMaxRows selezionare Espressioni per visualizzare la finestra di dialogo Editor
espressioni di proprietà.
38
4. In Editor espressioni di proprietà scegliere DefaultBufferMaxRows dal menu a discesa
Proprietà e fare clic sul pulsante con i puntini di sospensione per aprire il Generatore di
espressioni.
5. Trascinare la variabile creata nel passaggio 1 dall'elenco Variabili e parametri nell'angolo in
alto a sinistra alla casella Espressione e fare clic su Valuta espressione per visualizzare il valore
predefinito della variabile nella casella Valore valutato.
6. Fare clic su OK nelle finestre di dialogo Generatore di espressioni ed Editor espressioni di
proprietà per salvare la configurazione. In questa configurazione il valore delle proprietà verrà
impostato in fase di esecuzione per ridurre le probabilità di spooling dei dati BLOB sui dischi.
Utilizzo delle nuove funzionalità di SSIS 2012 per monitorare le prestazioni in
un sistema distribuito
In SQL Server 2012 sono disponibili nuove funzionalità per monitorare le prestazioni dei progetti di
Integration Services (SSIS) distribuiti sul server SSIS. È possibile registrare le informazioni sulle
prestazioni di runtime per l'esecuzione di un pacchetto, visualizzare le statistiche di esecuzione
e monitorare il flusso di dati del pacchetto.
39
Registrare le statistiche sulle prestazioni
Specificare l'ambito delle informazioni registrate durante l'esecuzione di un pacchetto selezionando uno
dei livelli di registrazione seguenti. Per registrare le statistiche sulle prestazioni, selezionare il livello
Prestazioni o Dettagliato.
Livello di
registrazione
Nessuno
Valore
Descrizione
0
Standard
1
La registrazione è disabilitata. Viene registrato solo lo stato dell'esecuzione
del pacchetto.
Vengono registrati tutti gli eventi tranne gli eventi personalizzati e di
diagnostica. È il valore predefinito.
Prestazioni
2
Vengono registrate solo le statistiche sulle prestazioni e gli eventi OnError
e OnWarning.
Dettagliato
3
Vengono registrati tutti gli eventi, inclusi gli eventi personalizzati e di
diagnostica.
In Integration Services sono disponibili numerosi eventi personalizzati per
la scrittura di voci di log per i pacchetti e per molte attività. È possibile
utilizzare tali voci per salvare informazioni dettagliate su stato di
esecuzione, risultati e problemi, registrando eventi predefiniti o messaggi
definiti dall'utente da analizzare in un secondo momento.
Per ulteriori informazioni, vedere Messaggi personalizzati per la
registrazione (http://msdn.microsoft.com/it-it/library/ms345174.aspx)
È possibile specificare il livello di registrazione eseguendo una o più delle operazioni seguenti per
un'istanza di esecuzione di un pacchetto.
Impostare i parametri per un'istanza di esecuzione di un pacchetto tramite la stored procedure
catalog.set_execution_parameter_value (http://msdn.microsoft.com/it-it/library/ff877990.aspx).
Configurare un'istanza di esecuzione di un pacchetto nella finestra di dialogo Esegui pacchetto.
Configurare un processo di SQL Server Agent per l'esecuzione di un pacchetto nella finestra di
dialogo Nuovo passaggio di processo.
Per impostare il livello di registrazione nella finestra di dialogo Esegui pacchetto
1.
2.
3.
4.
40
In SQL Server Management Studio passare al pacchetto in Esplora oggetti.
Fare clic con il pulsante destro del mouse sul pacchetto e selezionare Esegui.
Selezionare la scheda Avanzate.
In Livello di registrazione selezionare il livello di registrazione.
Per impostare il livello di registrazione nella finestra di dialogo Nuovo passaggio di processo
1. Creare un nuovo processo espandendo il nodo SQL Server Agent in Esplora oggetti, facendo clic
con il pulsante destro del mouse su Processi, quindi scegliendo Nuovo processo.
Oppure
Modificare un processo esistente espandendo il nodo SQL Server Agent, facendo clic con il
pulsante destro del mouse su un processo esistente e quindi scegliendo Proprietà.
2. Fare clic su Passaggi nel riquadro sinistro, quindi scegliere Nuovo per aprire la finestra di dialogo
Nuovo passaggio di processo.
3. Selezionare Pacchetto di SQL Server Integration Services nella casella di riepilogo Tipo.
4. Nella scheda Pacchetto selezionare Catalogo SSIS nella casella di riepilogo Origine pacchetto,
specificare il server, quindi immettere il percorso del pacchetto nella casella Pacchetto.
5. Nella scheda Configurazione fare clic su Avanzate, quindi selezionare un livello di registrazione
nella casella di riepilogo Livello di registrazione.
6. Completare la configurazione del passaggio di processo e salvare le modifiche.
Per impostare il livello di registrazione utilizzando la stored procedure
catalog.set_execution_parameter_value, impostare parameter_name su LOGGING_LEVEL
e parameter_value sul valore corrispondente a Prestazioni o Dettagliato. Nell'esempio seguente viene
creata un'istanza di esecuzione del pacchetto Package.dtsx e viene impostato il livello di registrazione su
2. Il pacchetto è contenuto nel progetto SSISPackages, che si trova nella cartella Packages.
Declare @execution_id bigint
exec catalog.create_execution 'Packages', 'SSISPackages',
'Package.dtsx', NULL, 1, @execution_id output
exec catalog.set_execution_parameter_value @execution_id, 50,
'LOGGING_LEVEL', 2
Visualizzare le statistiche di esecuzione
Le viste e le stored procedure del database SSISDB e i report standard disponibili in SQL Server
Management Studio forniscono numerose informazioni sulle esecuzioni dei pacchetti e altre
informazioni pertinenti. Un'esecuzione è un'istanza di esecuzione di un pacchetto.
Tra i report standard, Dashboard Integration Services, Tutte le esecuzioni e Tutte le connessioni sono
particolarmente utili per visualizzare le informazioni di esecuzione dei pacchetti.
Il report Dashboard Integration Services fornisce le informazioni seguenti per i pacchetti in esecuzione
o eseguiti nelle ultime 24 ore.
41
Report Dashboard Integration Services
Sezione del report
Informazioni sulle
esecuzioni
Informazioni sui
pacchetti
Informazioni sulle
connessioni
Informazioni
dettagliate sui
pacchetti
Descrizione
Indica il numero di esecuzioni che si trovano in stati diversi (operazione non
riuscita, in esecuzione, operazione riuscita e altri).
Indica il numero totale di pacchetti eseguiti.
Indica le connessioni utilizzate nelle esecuzioni che hanno restituito un
errore.
Indica per ogni pacchetto i dettagli relativi alle esecuzioni completate.
Vengono ad esempio riportati il numero di esecuzioni non riuscite rispetto
al totale, la durata di un'esecuzione (in secondi) e la durata media delle
esecuzioni negli ultimi tre mesi.
È possibile visualizzare ulteriori informazioni sull'esecuzione di un pacchetto
facendo clic su Prestazioni di esecuzione, Panoramica e Tutti i messaggi.
Il report Prestazioni di esecuzione contiene grafici della durata delle ultime
10 esecuzioni riuscite e dei valori di Tempo di attività e Tempo totale per
i componenti del flusso di dati del pacchetto. Il tempo di attività si riferisce
alla quantità totale di tempo di esecuzione del componente in tutte le fasi,
mentre il tempo totale è quello trascorso per un componente. Il report
contiene queste informazioni per i componenti del flusso di dati solo se
il livello di registrazione dell'ultima esecuzione del pacchetto è stato
impostato su Prestazioni o Dettagliato.
Il report Panoramica indica lo stato delle attività del pacchetto. Il report
Messaggi contiene i messaggi di evento e di errore per il pacchetto e le
attività, ad esempio la data e l'ora di inizio e di fine e il numero di righe
scritte.
42
Il report Tutte le esecuzioni fornisce le informazioni indicate di seguito, relative alle esecuzioni
completate nell'istanza di SQL Server connessa. Possono essere presenti più esecuzioni dello stesso
pacchetto. A differenza del report Dashboard Integration Services, è possibile configurare il report
Tutte le esecuzioni per visualizzare le esecuzioni avviate durante un intervallo di date. Gli intervalli di
date possono coprire più giorni, mesi o anni.
Report Tutte le esecuzioni
Sezione del report
Filtro
Informazioni sulle esecuzioni
Descrizione
Indica il filtro attualmente applicato al report,
ad esempio Intervallo ora di inizio.
Indica le date e le ore di inizio e di fine e la durata
dell'esecuzione di ogni pacchetto.
È possibile visualizzare un elenco di valori dei
parametri utilizzati con l'esecuzione di un
pacchetto, ad esempio i valori passati a un
pacchetto figlio utilizzando l'attività Esegui
pacchetto. Per visualizzare l'elenco dei parametri,
fare clic su Panoramica.
43
Il report Tutte le connessioni fornisce le informazioni indicate di seguito, relative alle connessioni non
riuscite per le esecuzioni completate nell'istanza di SQL Server.
Sezione del report
Filtro
Dettagli
44
Descrizione
Indica il filtro attualmente applicato al report, ad
esempio le connessioni con una stringa specificata
e il valore di Intervallo di ore ultimo errore.
Impostare un valore per Intervallo di ore ultimo
errore per visualizzare solo gli errori di connessione
restituiti durante un intervallo di date. L'intervallo
può coprire più giorni, mesi o anni.
Indica la stringa di connessione, il numero di
esecuzioni durante le quali si è verificato un errore
di connessione e la data e l'ora dell'ultima
connessione non riuscita.
Oltre a visualizzare i report standard disponibili in SQL Server Management Studio, è anche possibile
eseguire una query sulle viste del database SSISDB per ottenere informazioni analoghe sulle esecuzioni
dei pacchetti. Nella tabella seguente vengono descritte le viste principali.
Vista del database SSISDB
catalog.executable_statistics
(http://msdn.microsoft.com/
it-it/library/hh479592.aspx)
Descrizione
Consente di visualizzare una riga per ogni file eseguibile
in esecuzione, inclusa ogni iterazione di un file eseguibile.
Un eseguibile è un'attività o un contenitore aggiunto al flusso
di controllo di un pacchetto.
Ad esempio, la vista indica la durata dell'esecuzione
dell'eseguibile, la data e l'ora in cui il file eseguibile passa
alle fasi di pre-esecuzione e post-esecuzione e il risultato
dell'esecuzione del file eseguibile, ad esempio l'esito positivo
o negativo.
catalog.executions
(http://msdn.microsoft.com/
it-it/library/ff878089.aspx)
Un esempio di risultato di esecuzione è il codice restituito
dalla stored procedure eseguita da un'attività Esegui SQL.
Per ulteriori informazioni, vedere Parametri e codici restituiti
nell'attività Esegui SQL.
Consente di visualizzare informazioni sulle esecuzioni dei
pacchetti. I pacchetti eseguiti con l'attività Esegui pacchetto
sono inclusi nella stessa esecuzione del pacchetto padre.
Ad esempio, la vista indica lo stato di un'esecuzione (in corso,
operazione non riuscita, operazione riuscita e altri), la memoria
fisica totale e quella disponibile sul server all'avvio
dell'esecuzione e la memoria di paging totale e quella
disponibile all'avvio dell'esecuzione.
catalog.execution_component_phases Consente di visualizzare le date e le ore di inizio e di fine per
(http://msdn.microsoft.com/
i componenti del flusso di dati per ogni fase di un'esecuzione.
it-it/library/hh230981.aspx)
Tali informazioni vengono visualizzate per le esecuzioni di più
pacchetti.
catalog.event_messages
(http://msdn.microsoft.com/
it-it/library/hh479594.aspx)
La vista contiene queste informazioni per i componenti
del flusso di dati solo quando il livello di registrazione
dell'esecuzione è impostato su Prestazioni o Dettagliato.
Consente di visualizzare informazioni sui messaggi registrati
durante operazioni come la creazione e l'avvio dell'esecuzione
di un pacchetto. Il valore per questo tipo di operazione è 200.
Ad esempio, la vista indica il testo del messaggio, il componente
del pacchetto e il componente del flusso di dati che
rappresentano l'origine del messaggio e l'evento associato
al messaggio.
I messaggi visualizzati per l'esecuzione di un pacchetto dipendono
dal livello di registrazione impostato per l'esecuzione.
45
Vista del database SSISDB
catalog.event_message_context
(http://msdn.microsoft.com/
it-it/library/hh479590.aspx)
Descrizione
Consente di visualizzare le informazioni sulle condizioni
associate ai messaggi di evento delle esecuzioni.
Ad esempio, la vista indica l'oggetto associato al messaggio di
evento, ad esempio un valore di variabile o un'attività, e il
nome e il valore della proprietà associati al messaggio di evento.
Viene inoltre visualizzato l'ID di ogni messaggio di evento.
È possibile ottenere ulteriori informazioni su un messaggio
di evento specifico eseguendo una query sulla vista
catalog.event_messages.
È possibile utilizzare la vista catalog.execution_component_phases per calcolare il tempo di esecuzione
in tutte le fasi (tempo di attività) e il tempo totale trascorso (tempo totale) per i componenti di un
pacchetto. In questo modo è possibile determinare più facilmente i componenti la cui esecuzione è più
lenta del previsto.
Questa vista viene popolata quando il livello di registrazione dell'esecuzione del pacchetto è impostato
su Prestazioni o Dettagliato. Per ulteriori informazioni, vedere Registrare le statistiche sulle prestazioni
in questo articolo.
Nell'esempio seguente vengono calcolati il tempo di attività e il tempo totale per i componenti in
esecuzione con ID 33. Nel calcolo vengono utilizzate le funzioni sum e DATEDIFF.
Declare @execution_id bigint
Set @execution_id = 33
select package_name, task_name, subcomponent_name, execution_path,
sum (DATEDIFF(ms, start_time, end_time)) as active_time,
DATEDIFF(ms,min(start_time), max(end_time)) as total_time
from catalog.execution_component_phases
where execution_id = @execution_id
group by package_name, task_name, subcomponent_name, execution_path
order by active_time desc
46
Infine, è possibile utilizzare la funzione dm_execution_performance_counters per ottenere le statistiche
dei contatori delle prestazioni, ad esempio il numero di buffer utilizzati e il numero di righe lette e scritte
per l'esecuzione in corso.
Nell'esempio seguente la funzione restituisce le statistiche di un'esecuzione in corso con ID 34.
select * from [catalog].[dm_execution_performance_counters] (34)
Nell'esempio seguente la funzione restituisce le statistiche di tutte le esecuzioni in corso.
select * from [catalog].[dm_execution_performance_counters] (NULL)
Monitorare il flusso di dati
In Integration Services sono disponibili funzionalità e strumenti utili per la risoluzione dei problemi del
flusso di dati in un pacchetto nel corso dell'esecuzione.
Aggiunta di una scelta dei dati al flusso di dati durante l'esecuzione
SQL Server Integration Services (SSIS) in SQL Server 2012 offre una nuova funzionalità che consente
di aggiungere una scelta dei dati in un percorso del flusso di dati di un pacchetto in fase di esecuzione
e inviare l'output dalla scelta dei dati a un file esterno. Per utilizzare questa funzionalità è necessario
distribuire il progetto SSIS utilizzando il modello di distribuzione del progetto in un server SSIS. Dopo
avere distribuito il pacchetto nel server, è necessario eseguire script T-SQL sul database SSISDB per
aggiungere le scelte dei dati prima dell'esecuzione del pacchetto. Di seguito viene fornito uno scenario
di esempio:
1. Creare un'istanza di esecuzione di un pacchetto tramite la stored procedure
catalog.create_execution (http://msdn.microsoft.com/it-it/library/ff878034).
47
2. Aggiungere una scelta dei dati tramite la stored procedure catalog.add_data_tap
(http://msdn.microsoft.com/it-it/library/hh230989) o catalog.add_data_tap_by_guid
(http://msdn.microsoft.com/it-it/library/hh230991).
3. Avviare l'istanza di esecuzione del pacchetto tramite la stored procedure
catalog.start_execution (http://msdn.microsoft.com/it-it/library/ff878160).
Di seguito è riportato uno script SQL di esempio che esegue i passaggi descritti nello scenario precedente:
Declare @execid bigint
EXEC [SSISDB].[catalog].[create_execution] @folder_name=N'ETL Folder',
@project_name=N'ETL Project', @package_name=N'Package.dtsx',
@execution_id=@execid OUTPUT
EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid,
@task_package_path = '\Package\Data Flow Task',
@dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source
Output]', @data_filename = 'output.txt'
EXEC [SSISDB].[catalog].[start_execution] @execid
I parametri per i nomi di cartella, di progetto e di pacchetto della stored procedure create_execution
corrispondono ai nomi della cartella, del progetto e del pacchetto nel catalogo di Integration Services.
È possibile ottenere tali nomi per utilizzarli nella chiamata a create_execution da SQL Server
Management Studio, come illustrato nella figura seguente. Se il progetto SSIS non viene visualizzato,
è possibile che non sia stato ancora distribuito sul server SSIS. Fare clic con il pulsante destro del mouse
sul progetto SSIS in Visual Studio e scegliere Distribuisci per distribuire il progetto nel server SSIS previsto.
48
Anziché digitare le istruzioni SQL, è possibile generare lo script di esecuzione del pacchetto con la
procedura seguente:
1. Fare clic con il pulsante destro del mouse su Package.dtsx e scegliere Esegui.
2. Fare clic sul pulsante della barra degli strumenti Script per generare lo script.
3. Aggiungere l'istruzione add_data_tap prima della chiamata a start_execution.
Il parametro task_package_path della stored procedure add_data_tap corrisponde alla proprietà
PackagePath dell'attività Flusso di dati in Visual Studio. In Visual Studio fare clic con il pulsante destro del
mouse sull'attività Flusso di dati e scegliere Proprietà per aprire la finestra Proprietà. Annotare il valore
della proprietà PackagePath per utilizzarlo come valore per il parametro task_package_path per la
chiamata alla stored procedure add_data_tap.
Il parametro dataflow_path_id_string della stored procedure add_data_tap corrisponde alla proprietà
IdentificationString del percorso del flusso di dati a cui si desidera aggiungere una scelta dei dati.
Per ottenere dataflow_path_id_string, fare clic sul percorso del flusso di dati e annotare il valore della
proprietà IdentificationString indicato nella finestra Proprietà.
49
Quando si esegue lo script, l'output viene archiviato in <Programmi>\Microsoft SQL
Server\110\DTS\DataDumps. Se esiste già un file con lo stesso nome, viene creato un nuovo file con un
suffisso (ad esempio output[1].txt).
Come accennato in precedenza, è possibile anche utilizzare la stored procedure
catalog.add_data_tap_by_guid (http://msdn.microsoft.com/it-it/library/hh230991) invece di
add_data_tap. Questa stored procedure accetta l'ID dell'attività Flusso di dati come parametro anziché
task_package_path. È possibile ottenere l'ID dell'attività Flusso di dati nella finestra delle proprietà di
Visual Studio.
Rimozione di una scelta dei dati
È possibile rimuovere una scelta dei dati prima di avviare l'esecuzione utilizzando la stored procedure
catalog.remove_add_data_tap. Questa stored procedure accetta come parametro l'ID della scelta dei
dati, che è possibile ottenere come output della stored procedure add_data_tap.
DECLARE @tap_id bigint
EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid,
@task_package_path = '\Package\Data Flow Task',
@dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source
Output]', @data_filename = 'output.txt' @data_tap_id=@tap_id OUTPUT
EXEC [SSISDB].[catalog].remove_data_tap @tap_id
50
Visualizzazione di un elenco di tutte le scelte dei dati
È anche possibile elencare tutte le scelte dei dati utilizzando la vista catalog.execution_data_taps.
Nell'esempio seguente vengono estratte le scelte dei dati per una specifica istanza di esecuzione
(ID: 54).
select * from [SSISDB].[catalog].execution_data_taps where
execution_id=@execid
Considerazione sulle prestazioni
L'abilitazione del livello di registrazione Dettagliato e l'aggiunta di scelte dei dati aumentano le
operazioni di I/O eseguite dalla soluzione di integrazione dei dati in uso. Per questo motivo,
è consigliabile aggiungere scelte dei dati solo ai fini della risoluzione dei problemi.
Monitoraggio del flusso di dati durante l'esecuzione
È possibile utilizzare la vista del database SSISDB catalog.execution_data_statistics
(http://msdn.microsoft.com/it-it/library/hh230986.aspx) per analizzare il flusso di dati dei pacchetti.
Viene visualizzata una riga ogni volta che un componente del flusso di dati invia dati a un componente
a valle. Grazie a queste informazioni è possibile determinare con maggiore precisione le righe inviate
a ciascun componente.
Nota: per acquisire informazioni con la vista catalog.execution_data_statistics, è necessario che il livello
di registrazione sia impostato su Dettagliato.
Nell'esempio seguente viene visualizzato il numero di righe inviate tra i componenti di un pacchetto.
execution_id è l'ID di un'istanza di esecuzione che è possibile ottenere come valore restituito dalla
stored procedure create_execution o nella vista catalog.executions.
use SSISDB
select package_name, task_name, source_component_name,
destination_component_name, rows_sent
from catalog.execution_data_statistics
where execution_id = 132
order by source_component_name, destination_component_name
51
Nell'esempio seguente viene calcolato il numero di righe al millisecondo inviate da ogni componente per
un'esecuzione specifica. I valori calcolati sono:
total_rows: somma di tutte le righe inviate dal componente
wall_clock_time_ms: tempo totale di esecuzione trascorso, in millisecondi, per ogni componente
num_rows_per_millisecond: numero di righe al millisecondo inviate da ogni componente
La clausola HAVING viene utilizzata per impedire un errore di divisione per zero nei calcoli.
use SSISDB
select source_component_name, destination_component_name,
sum(rows_sent) as total_rows,
DATEDIFF(ms,min(created_time),max(created_time)) as
wall_clock_time_ms,
((0.0+sum(rows_sent)) /
(datediff(ms,min(created_time),max(created_time)))) as
[num_rows_per_millisecond]
from [catalog].[execution_data_statistics]
where execution_id = 132
group by source_component_name, destination_component_name
having (datediff(ms,min(created_time),max(created_time))) > 0
order by source_component_name desc
Conclusione
SQL Server Integration Services (SSIS) può essere utilizzato come efficace strumento per spostare
i dati da e verso il database SQL di Windows Azure, come parte della soluzione globale di estrazione,
trasformazione e caricamento (ETL) e della soluzione di spostamento di dati, ed è ideale per spostare
dati tra origini e destinazioni nel cloud e, in scenari ibridi, tra ambienti cloud e locali. In questo white
paper sono state descritte le procedure consigliate SSIS per le origini e le destinazioni cloud, è stata
illustrata la pianificazione di progetti SSIS sia circoscritti al cloud che riguardanti ambienti ibridi ed
è stato descritto in modo dettagliato un esempio di ottimizzazione delle prestazioni in uno spostamento
ibrido tramite la scalabilità orizzontale dello spostamento dei dati.
52
Per ulteriori informazioni
http://www.microsoft.com/italy/server/sql: sito Web SQL Server
http://technet.microsoft.com/it-it/sqlserver/: TechCenter di SQL Server
http://msdn.microsoft.com/it-it/sqlserver/: risorse online per gli sviluppatori di SQL Server
Il documento è risultato utile? Esprimere una valutazione utilizzando una scala compresa tra 1
(scarso) e 5 (eccellente), indicandone i motivi. Ad esempio:
La valutazione è alta perché il documento contiene esempi validi, catture di schermate
nitide, spiegazioni chiare o altri motivi?
La valutazione è bassa perché il documento contiene esempi confusi, catture di
schermate sfocate, spiegazioni poco chiare?
I commenti aiuteranno Microsoft a migliorare la qualità dei white paper pubblicati.
Commenti e suggerimenti.
53
