Guide de fonctionnement et de paramétrage de

advertisement
Guide de fonctionnement et de paramétrage
de SSIS
Article technique SQL Server
Auteurs : Alexei Khalyako, Carla Sabotta, Silvano Coriani, Sreedhar Pelluru, Steve Howard
Relecteurs techniques : Cindy Gross, David Pless, Mark Simms, Daniel Sol
Date de publication : décembre 2012
S'applique à : SQL Server 2012, Base de données SQL Windows Azure
Résumé : SQL Server Integration Services (SSIS) peut être utilisé efficacement comme outil pour
déplacer des données dans et à partir de la Base de données SQL Windows Azure, dans le cadre de la
solution d'extraction, de transformation et de chargement (ETL, Extract, Transform and Load) et de la
solution de déplacement des données. SSIS peut être utilisé efficacement pour déplacer des données
entre les sources et les destinations dans le cloud, et dans un scénario hybride entre le cloud et le site.
Ce document présente les meilleures pratiques SSIS pour les sources et les destinations du cloud,
aborde la planification de projets SSIS si le projet se trouve entièrement dans le cloud ou comporte
des déplacements de données hybrides et vous guide tout au long d'un exemple d'optimisation des
performances sur un déplacement hybride en mettant à l'échelle le déplacement des données.
Copyright
Ce document est fourni « en l'état ». Les informations et les opinions exprimées dans ce document,
y compris les adresses URL et les autres références à des sites Internet, peuvent faire l'objet de
modifications sans préavis. Vous assumez tous les risques liés à son utilisation.
Certains exemples mentionnés dans ce document ne sont fournis qu'à titre indicatif et sont fictifs.
Toute ressemblance ou similitude avec des éléments réels est purement fortuite et involontaire.
Ce document ne vous concède aucun droit de propriété intellectuelle portant sur les produits Microsoft.
Vous pouvez copier et utiliser ce document à titre de référence pour un usage interne.
© 2011 Microsoft. Tous droits réservés.
2
Sommaire
Introduction .................................................................................................................................................. 5
Création de projet ......................................................................................................................................... 5
Portée du problème et description........................................................................................................... 5
Pour quelles raisons le déplacement des données est-il essentiel dans Windows Azure ? ........................ 6
Scénarios clés de déplacement des données ........................................................................................... 7
Chargement initial et migration des données du site vers le cloud ..................................................... 7
Déplacement des données générées dans le cloud vers des systèmes sur site ................................... 8
Déplacement de données entre les services de cloud computing ....................................................... 9
Outils existants, services et solutions ....................................................................................................... 9
SQL Server Integration Services (SSIS) ................................................................................................ 10
Classe SqlBulkCopy dans ADO.NET ..................................................................................................... 11
Programme de copie en bloc (BCP.EXE) ............................................................................................. 12
Stockage Windows Azure (objets blob et files d'attente). .................................................................. 12
Options de conception et d'implémentation.......................................................................................... 13
Concevoir et implémenter une architecture équilibrée ..................................................................... 14
Éléments à prendre en considération pour les types de données ..................................................... 15
Empaquetage et déploiement de solution ............................................................................................. 15
Créer des solutions portables ............................................................................................................. 16
Distribution des packages et des composants de code ...................................................................... 16
Base de données SQL Windows Azure comme destination de déplacement des données ............... 18
Considérations sur l'architecture ................................................................................................................ 19
Conception pour un redémarrage sans perte de progression dans le pipeline ..................................... 19
Principe de base ...................................................................................................................................... 19
Exemple avec une seule destination................................................................................................... 20
Exemple avec plusieurs destinations ...................................................................................................... 26
Autres conseils pour le redémarrage ...................................................................................................... 28
Conception pour nouvelle tentative sans intervention manuelle .......................................................... 30
Incorporation de nouvelle tentative ....................................................................................................... 31
3
Options de réglage des performances SSIS ................................................................................................ 35
Réglage des paramètres réseau .............................................................................................................. 35
Paramètres réseau .............................................................................................................................. 36
Remarque : lorsque vous modifiez les paramètres de votre carte d'interface réseau de façon
à utiliser les trames Jumbo, vérifiez que l'infrastructure réseau prend en charge ce type de trame.
Taille des paquets ............................................................................................................................... 36
Paramètres de package SSIS ................................................................................................................... 37
Considérations spéciales relatives aux données BLOB ....................................................................... 39
Utilisation des nouvelles fonctionnalités de SSIS 2012 pour analyser les performances sur un
système distribué .................................................................................................................................... 41
Enregistrer les statistiques de performances ..................................................................................... 42
Afficher les statistiques d'exécution ................................................................................................... 43
Analyser le flux de données ................................................................................................................ 49
Conclusion ................................................................................................................................................... 54
4
Introduction
SQL Server Integration Services (SSIS) est un outil efficace pour déplacer des données dans et à partir de
Base de données SQL Windows Azure, dans le cadre de la solution d'extraction, de transformation et de
chargement (ETL, Extract, Transform and Load) ou de la solution de déplacement des données
lorsqu'aucune transformation n'est nécessaire. SSIS est efficace pour diverses sources et destinations si
elles sont toutes dans le cloud, toutes sur site, ou combinées dans une solution hybride. Ce document
présente les meilleures pratiques SSIS pour les sources et les destinations du cloud, aborde la
planification de projets SSIS si le projet se trouve entièrement dans le cloud ou comporte des
déplacements de données hybrides et vous guide tout au long d'un exemple d'optimisation des
performances sur un déplacement hybride en mettant à l'échelle le déplacement des données.
Création de projet
Les projets qui déplacent des données entre le cloud et les banques de données sur site peuvent
impliquer divers processus dans différentes solutions. Il existe souvent plusieurs parties commençant
par le remplissage initial de la destination, qui peut accepter des données d'un autre système ou d'une
autre plateforme, via des opérations de maintenance, telles que le rééquilibrage des datasets parmi le
nombre variable de partitions et se poursuivant probablement par des opérations de données en bloc
ou des actualisations périodiques. La création de projet et les hypothèses sous-jacentes diffèrent
souvent pour une solution portant sur des données dans le cloud par rapport à un environnement
traditionnel de déplacement des données entièrement sur site. Nombre d'enseignements, d'expériences
et de pratiques s'appliquent toujours, mais des modifications sont nécessaires pour adapter les
différences comme le fait que votre environnement n'est plus autonome et complètement sous votre
contrôle, car vous effectuez un déplacement vers un pool partagé de ressources de base. Ces différences
nécessitent une approche plus équilibrée et plus évolutive en vue d'une réussite.
Portée du problème et description
Pour les deux solutions natives conçues depuis le début pour le cloud et pour les solutions migrées, les
données doivent être déplacée dans les deux sens. Cela se produit généralement dans plusieurs phases
du cycle de vie des applications. Les phases comprennent les tests de préproduction, le chargement
initial de données, la synchronisation successive des données entre les données générées dans le cloud
et les bases de données sur site d'origine, ainsi que les instantanés de données récurrents provenant du
cloud transmis à d'autres systèmes sur site (par exemple des entrepôts de données).
5
Illustration 1 Scénario de déplacement des données
Cette section est principalement axée sur la phase initiale de chargement de données : examen de
l'expérience de bout en bout d'extraction de données de la base de données source, de déplacement
du site vers le cloud et de chargement des données dans une destination finale. Il est important de
souligner que la plupart des meilleures pratiques et optimisations (pour ne pas dire toutes) présentées
dans ce document s'appliquent également à la plupart des scénarios décrits avec des modifications
minimes. Nous aborderons ces scénarios et leurs principaux problèmes dans les sections suivantes.
Pour quelles raisons le déplacement des données est-il essentiel dans Windows
Azure ?
Par rapport à une expérience de centre de données traditionnelle, où les problèmes liés au déplacement
des données entre les applications et les systèmes sont généralement liés aux problèmes de compatibilité
des applications, d'orchestration et de synchronisation des processus, ainsi qu'aux ressources matérielles
physiques et aux fonctionnalités réseau, dans des environnements de cloud comme Windows Azure,
il existe plusieurs couches supplémentaires de complexité. La complexité peut résider dans des zones
telles que la connectivité entre le site et le cloud (ou entre différents services de cloud computing) et
être associée à la fiabilité, à la bande passante à et à la latence de connexion. Il est indispensable de
tenir compte de cette complexité pour développer une solution optimale de déplacement des données.
Lorsque vous disposez d'un grand nombre d'éléments non fixes impliqués dans la solution, il est encore
plus important de concentrer vos efforts sur la recherche d'une conception équilibrée entre tous les
composants et technologies concernés. Essayez d'éviter de saturer le point le plus faible de la chaîne,
car cela aura un impact négatif sur tous les autres.
6
Selon nos tests, un des points les plus importants est la possibilité pour la destination des données
d'ingérer, au débit approprié, la quantité de données provenant de l'extérieur. L'approche la plus
courante consiste à mettre à l'échelle la base de données de destination sur plusieurs nœuds principaux
en utilisant le partitionnement (http://social.technet.microsoft.com/wiki/contents/articles/1926.howto-shard-with-windows-azure-sql-database.aspx) personnalisé. Cette technique est obligatoire si la
quantité de données à charger est significative (plus de 20 Go/heure est considéré comme significatif
lors de la rédaction de ce document), et peut être appliquée aux instances de Base de données SQL
Azure et SQL Server exécutées dans des ordinateurs virtuels Windows Azure. Étant donné que cela
n'introduira pas automatiquement l'évolutivité linéaire dans la solution de chargement de données,
il y a un besoin accru d'équilibrage des autres éléments non fixes de la solution. Dans les sections
suivantes, nous allons décrire les éléments les plus importants et les options de création qui peuvent
être adoptées pour optimiser les résultats finaux.
Scénarios clés de déplacement des données
Voici les trois principaux scénarios que nous allons examiner dans le cadre de l'expérience de
déplacement des données de bout en bout. Ils contiendront la plupart des thèmes et problèmes
récurrents que nous avons trouvé.



Chargement initial et migration des données du site vers le cloud
Déplacement des données générées dans le cloud vers des systèmes sur site
Déplacement de données entre les services de cloud computing
Chargement initial et migration des données du site vers le cloud
Base de données SQL
ou SQL Server dans
un ordinateur virtuel
Illustration 2 Scénario de chargement initial des données
7
Chaque application qui doit être déplacée d'un déploiement sur site vers un environnement de cloud
requiert un certain volume de données à déplacer. Lorsque le volume de données devient significatif,
cette opération peut présenter des problèmes critiques qui vont nécessiter une approche légèrement
différente par rapport à celle utilisée sur site. Cela est principalement causé par les deux éléments
suivants : à la bande passante et à la latence du réseau public, ainsi qu'à la quantité de ressources
(partagées) nécessaires pour exécuter la phase de chargement de données, disponibles dans le ou les
nœuds du matériel physique qui héberge une base de données (la Base de données SQL Azure ou les
ordinateurs virtuels Windows Azure) dans l'environnement de cloud. Il existe des méthodes spécifiques
(voir l'illustration 2), telles que le partitionnement des données d'origine dans plusieurs fichiers du
compartiment et la compression de ces fichiers avant leur transfert sur le réseau, ce qui peut réduire
l'impact du composant le moins performant dans la solution globale. Le partitionnement des données
va également aider du côté cloud à faciliter l'insertion de ces données dans une destination de données.
Celle-ci est très probablement partitionnée sur plusieurs instances de Base de données SQL Windows
Azure ou hébergée par plusieurs ordinateurs virtuels Windows Azure.
SSIS jouera un rôle important sur site et du côté cloud pour exécuter physiquement les opérations
d'importation et d'exportation. La solution globale exigera des technologies supplémentaires comme les
files d'attentes et le stockage d'objets blob Windows Azure pour stocker les formats de fichiers intermédiaires
et orchestrer l'opération de copie et de récupération sur plusieurs instances du processus d'importation SSIS.
Pour plus d'informations spécifiques liées à la migration d'un schéma et d'objets de base de données
vers Base de données SQL Azure, consultez Migration d'applications centrées sur les données vers
Windows Azure (http://msdn.microsoft.com/fr-fr/library/windowsazure/jj156154.aspx).
Déplacement des données générées dans le cloud vers des systèmes sur site
Ce scénario est une variante proche du précédent. D'un point de vue technique il concerne l'inversion
du processus de chargement et du flux de données, bien qu'il puisse exister des différences en termes
d'objectifs généraux. Ce scénario est généralement associé à des données générées à froid qui doivent
être récupérées périodiquement et chargées dans des systèmes sur site, tels que des entrepôts de
données, ou à des données qui vont alimenter les solutions transactionnelles locales. Cela signifie
que la plupart des techniques et technologies mentionnées dans la section précédente sont également
appropriées pour ce scénario. SSIS extrait les données du côté cloud, puis les compresse et les
renvoie à un système sur site où de nouveau toutes les instructions traditionnelles s'appliquent.
Pour plus d'informations, consultez Guide des performances du chargement de données
(http://msdn.microsoft.com/fr-fr/library/dd425070(v=SQL.100).aspx).
8
Déplacement de données entre les services de cloud computing
Il existe plusieurs scénarios où le déplacement des données entre différents services de cloud computing
et bases de données est requis. Cela inclut l'échange de données entre les différentes solutions qui
doivent interagir et la redistribution des données partitionnées entre les tables hébergées dans
différentes partitions comme dans l'illustration 3 ci-dessous.
Illustration 3 Déplacement de données entre des partitions de base de données
Base de données SQL ou SQL Server dans un ordinateur virtuel
Ces partitions peuvent également être hébergées par des instances de Base de données SQL Azure ou
SQL Server dans un ordinateur virtuel Windows Azure sans modifier la méthode et l'architecture sousjacentes. La différence par rapport aux scénarios précédents repose sur le fait que le processus complet
de déplacement des données se produit généralement dans les limites d'une seule zone Windows
Azure, ce qui réduit de façon significative l'impact de la latence réseau et supprime la nécessité
d'exporter et d'importer des données via un emplacement de stockage intermédiaire (disques locaux ou
stockage Windows Azure). Certains scénarios peuvent nécessiter le déplacement de données entre des
régions, mais ce sujet dépasse l'objet de ce document. En même temps, comme la source et la
destination des données vont toutes les deux être hébergées dans un environnement de cloud partagé,
la nécessité de paramétrer avec soin la phase de chargement en particulier augmente fortement.
Outils existants, services et solutions
Pour implémenter des solutions qui couvrent les scénarios décrits précédemment, utilisez une
combinaison d'outils nouveaux et existants, et des composants et des méthodes qui peuvent aider à la
fois sur site et dans le cloud. Dans un environnement hybride, certains de ces composants devront être
placés sur des systèmes existants et des sources de données sur site alors que d'autres tireront parti
d'une installation avec les destinations de données dans le cloud.
9
SQL Server Integration Services (SSIS)
En guise de solution principale de déplacement et d'intégration de données, SSIS offre une grande
variété de fonctionnalités qui couvrent la plupart des ressources nécessaires dans cette plage de
scénarios. Bien que n'étant pas conçus spécifiquement pour l'environnement hybride, avec l'arrivée des
ordinateurs virtuels Windows Azure, les packages SSIS peuvent être exécutés sur site et dans le cloud et
peuvent, éventuellement, connecter directement les deux mondes. Cela ouvre la porte à une grande
quantité de connaissances et à la réutilisation des compétences dans la communauté des développeurs
DBA/ETL, car de nombreux professionnels existants ont été formés et exposés à cette technologie ;
toutefois, il est important de comprendre que tous les processus ETL existants implémentés avec SSIS ne
peuvent pas être directement réutilisés lors du déplacement de données du site vers le cloud.
Selon la complexité du processus, le volume et la vitesse des données et les différences intrinsèques
entre les destinations de données du cloud telles que SQL Server s'exécutant sur ordinateur virtuel
Windows Azure et la Base de données SQL Azure, un certain degré de nouvelle architecture est nécessaire.
Certains de ces problèmes peuvent être liés au manque actuel de fonctionnalités pour gérer la réalité
d'une connexion au cloud lorsque vous vous connectez à Base de données SQL Windows Azure ou à la
quantité de travail nécessaire pour la conception de packages SSIS qui doivent gérer les échecs et les
nouvelles tentatives lors du chargement de données.
Un autre problème peut être lié à la conception de packages qui doivent se connecter aux destinations
de données partitionnées, où les entités de bases de données peuvent être réparties sur un nombre
parfois variable de nœuds physiques. La logique et les métadonnées de partitionnement doivent être
gérées et récupérées dans des fichiers de configuration de l'application ou des structures de données.
La plateforme SSIS dispose déjà de la plupart des fonctionnalités nécessaires pour traiter ces problèmes.
Par exemple, utilisez des composants de flux de données, tels que le fractionnement conditionnel et les
transformations de multidiffusion pour implémenter la logique de partitionnement.
Lors de la résolution des problèmes d'architecture, un effort sera nécessaire pour implémenter la
nouvelle conception, en utilisant un outil visuel (méthode traditionnelle) ou via un moyen plus
automatisé par programme pour architecturer une solution plus complexe. Pour l'approche par
programme, SSIS offre un environnement entièrement scriptable qui s'étend de la création de tâches
personnalisées dans le pipeline de transformation à l'instrumentation du moteur qui aide à dépanner et
déboguer l'exécution du package.
Dans la version SQL Server 2012 d'Integration Services, une solution complète d'analyse et de gestion,
basée sur un catalogue commun, peut vous aider à concevoir une solution distribuée de déplacement
des données et à collecter des informations sur les statistiques et les résultats d'exécution du package.
10
Classe SqlBulkCopy dans ADO.NET
Si le développement d'une solution personnalisée de déplacement des données est l'approche par
défaut pour résoudre un problème particulier de déplacement des données, la classe SqlBulkCopy
(http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlbulkcopy.aspx ) de la bibliothèque
d'accès aux données ADO.NET est probablement un des outils les plus courants pour effectuer cette
tâche. Créée comme un wrapper léger autour des API de copie en bloc ODBC, cette classe accepte une
table de données en entrée et une connexion de base de données existante et offrira un moyen rapide
et entièrement configurable pour charger des données dans SQL Server ou dans une Base de données
SQL Windows Azure.
Un aspect important lié à l'utilisation de la classe SqlBulkCopy pour interagir avec une destination
de données du cloud est la possibilité de remplacer facilement la classe SqlConnection
(http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlconnection.aspx) utilisée pour
interagir avec le serveur par la classe ReliableSqlConnection (http://msdn.microsoft.com/fr-fr/library/
microsoft.practices.enterpriselibrary.windowsazure.transientfaulthandling.sqlazure.reliablesqlconnectio
n(v=pandp.50).aspx ) plus appropriée, qui fait partie de la bibliothèque du bloc d'applications de gestion
d'erreurs transitoires (http://msdn.microsoft.com/fr-fr/library/hh680934(v=PandP.50).aspx ). Cela
simplifie considérablement la tâche d'implémentation d'un mécanisme de logique de nouvelle tentative
dans un processus de chargement de données nouveau ou existant. Une autre facette intéressante de la
bibliothèque est la capacité de mettre à disposition des stratégies de nouvelle tentative personnalisées et
standard afin de pouvoir s'adapter facilement aux différentes conditions de connectivité.
La classe SqlBulkCopy expose tous les attributs et les propriétés nécessaires pour pouvoir adapter le
processus de chargement à quasiment toutes les conditions. Cet article explique comment paramétrer
et optimiser la taille des lots selon l'emplacement d'exécution du processus de chargement des
données, la quantité de données que le processus doit importer et la connectivité disponible entre le
processus et la destination des données.
Une situation où la classe SqlBulkCopy ne serait pas le choix le plus efficace pour charger des données
dans une destination est lorsque la quantité de données d'un seul lot est faible, par exemple comprise
entre 10 et 1 000 lignes par lot. Dans ce cas, la surcharge requise par la classe SqlBulkCopy pour établir
le contrôle des métadonnées initiales avant le démarrage du chargement des données peut nuire aux
performances globales. Une autre approche pour les lots de petite taille consiste à définir un paramètre
table qui implémente le schéma souhaité, puis à utiliser « INSERT INTO Destination SELECT * FROM
@TVP » pour charger les données.
Pour un exemple détaillé de l'utilisation de l'API de copie en bloc, consultez Classe SqlBulkCopy
(http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlbulkcopy.aspx).
11
Programme de copie en bloc (BCP.EXE)
Le programme de copie en bloc (utilitaire en ligne de commande créé sur les mêmes API de copie en
bloc que celles décrites pour la classe SqlBulkCopy) a été utilisé pendant un certain temps pour charger
en masse des données vers et à partir d'une instance de SQL Server. Il s'agit d'un outil simple mais
puissant pour automatiser efficacement les solutions simples de déplacement des données. Un des
principaux avantages de l'outil réside dans le fait qu'il est simple d'automatiser son installation dans les
nœuds de calcul ou les ordinateurs virtuels Windows Azure et d'utiliser des scripts existants qui peuvent
être adaptés en vue d'une exécution dans un environnement de cloud.
En revanche, BCP.EXE ne fournit aucune fonctionnalité avancée de gestion des connexions. En outre,
BCP.EXE nécessite le même effort que SSIS pour l'implémentation de tâches de déplacement des
données fiables basées sur les opérations de nouvelle tentative qui peuvent entraîner l'instabilité et la
perte de connexion. En outre, contrairement aux autres outils que nous avons mentionnés, BCP.EXE doit
importer ou exporter des données de fichiers physiques hébergés dans un lecteur local, mappé ou
attaché. Cela rend impossible la création du flux de données de la source vers la destination, la lecture
des données directe ou par programme à partir de différentes sources de données, comme SSIS ou une
application basée sur SqlBulkCopy peut le faire.
Stockage Windows Azure (objets blob et files d'attente).
Bien qu'il ne s'agisse pas d'outils strictement liés au déplacement des données, les fonctionnalités de
Stockage Windows Azure font indéniablement partie de l'implémentation de solutions complexes qui
requièrent un stockage intermédiaire entre les processus sur site et les processus du cloud, et sont
indispensables pour orchestrer les phases et les opérations entre ces deux environnements. Les objets
blob Windows Azure constituent un puissant mécanisme de stockage pour charger les fichiers
intermédiaires, et pour échanger ces fichiers entre des nœuds de calcul ou des ordinateurs virtuels
Windows Azure et des applications exécutées sur site. Les files d'attente Windows Azure constituent un
outil simple de messagerie qui peut être utilisé pour signaler et coordonner l'accès aux fichiers et au
contenu stockés en tant qu'objets blob Windows Azure, depuis des processus de chargement de
données.
Il est facile d'intégrer les objets blob et les files d'attente de stockage Windows Azure dans les
applications existantes, grâce à la bibliothèque cliente de stockage Windows Azure .NET qui offre un
ensemble simple de classes pour interagir avec les comptes de stockage, le conteneur, les objets blob et
les opérations associées. Cette bibliothèque masque les détails de l'interface REST sous-jacente et crée
un pont entre les données locales et les données du cloud. Pour plus d'informations sur l'utilisation des
files d'attente et des objets blob de stockage Windows Azure, consultez Procédure : utiliser le service de
stockage de files d'attente (http://www.windowsazure.com/en-us/develop/net/how-to-guides/queueservice/ ) and Procédure : utiliser le service de stockage d'objets blob Windows Azure dans .NET
(http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/).
12
Options de conception et d'implémentation
Plusieurs facteurs peuvent avoir un impact sur les options de conception et d'implémentation
concernant les solutions hybrides de déplacement des données. La nécessité de réutiliser les artefacts et
les processus existants par rapport à une feuille vierge comme point de départ est probablement ce qui
a le plus d'impact sur les décisions en matière d'architecture, suivie par les compétences et les profils
des membres de l'équipe (davantage de développeurs ou davantage d'administrateurs de base de
données disponibles, par exemple). L'équipe dispose-t-elle des compétences nécessaires pour générer
complètement une solution personnalisée par programme, ou l'équipe dispose-t-elle des compétences
nécessaires pour adapter les processus ETL existants ? Dans les deux cas, vous devez tenir compte de
plusieurs points lorsque vous introduisez le cloud dans la conception, car certaines des hypothèses
évidentes que vous avez pu émettre sur les environnements traditionnels sur site peuvent ne pas être
valables dans un environnement de cloud.
Un autre aspect important de la conception est de savoir où placer et exécuter les tâches et les services
spécifiques au déplacement des données, tels que la logique de fractionnement conditionnel qui
effectue les activités de partitionnement ou de compression des données. Selon la façon dont ces tâches
sont implémentées au niveau du composant (pipeline SSIS ou tâches personnalisées), ces composants
peuvent être très gourmands en ressources processeur. Pour équilibrer la consommation de ressources,
il est logique de déplacer des tâches vers les ordinateurs virtuels Windows Azure et d'exploiter
l'élasticité naturelle de l'environnement de cloud. En même temps, la proximité des sources de données
qu'il va traiter peut fournir bien d'autres avantages en raison de la latence réseau réduite qui peut
vraiment être essentielle dans ce type de solution. La planification et les tests vous aideront à déterminer
les goulots d'étranglement de ressources spécifiques et à décider comment implémenter différentes tâches.
L'effort nécessaire pour implémenter ou adapter une solution existante à un scénario hybride doit être
justifié par les avantages présentés par le scénario hybride. Vous devez clarifier les avantages techniques
qui seront obtenus en déplaçant certains éléments de votre solution vers le cloud par rapport à l'ensemble
des opérations qui peuvent être partiellement perdues, pour permettre une réflexion appropriée et
réussir une implémentation hybride. Ces inconvénients sont liés à des aspects tangibles d'une conception
de solution. Comment pouvez-vous tirer parti des fonctionnalités de montée en puissance parallèle
fournies par les plateformes de cloud sans perdre trop de contrôle sur les composants de votre
solution ? Comment pouvez-vous exécuter vos processus de chargement de données sur une
plateforme conçue pour la montée en puissance parallèle plutôt que pour la montée en puissance par
unité et toujours fournir des performances prédictibles acceptables ? Pour répondre à ces questions, vous
devez abandonner certaines hypothèses sur les performances et la fiabilité de la connexion réseau, les
composants et les services d'application qui sont toujours activés et en cours d'exécution, et planifier les
ressources qui peuvent être ajoutées afin de résoudre les problèmes de performance. Cela exige
d'entrer dans un monde où une défaillance de conception est requise, la latence est généralement plus
élevée que dans votre expérience passée et le partitionnement d'une charge de travail sur plusieurs
petits ordinateurs virtuels ou services est fortement souhaitée.
13
Concevoir et implémenter une architecture équilibrée
Toutes ces considérations doivent vous guider dans la bonne direction lors de la conception d'une
solution complexe de déplacement des données avec plusieurs éléments non fixes, où aucun d'entre
eux ne représente nécessairement l'équivalent « haut de gamme » des composants sur site
traditionnels.
Le principe directeur doit être le suivant : Décomposez le processus de déplacement des données en
plusieurs éléments de plus petite taille, allant de l'extraction de la source de données au chargement de
la destination de données, qui doivent être asynchrones et orchestrés pour s'ajuster à l'environnement
de latence plus élevée introduit par la solution hybride. Trouver le bon équilibre entre les composants
d'un environnement est beaucoup plus important qu'obtenir la qualité de l'acier (les limites) d'un seul
composant. Même les étapes individuelles de ce processus, comme le chargement de données par
exemple, doivent être partitionnées en flux de données de chargement de plus petite taille dans
différentes partitions ou bases de données physiques, de façon à surmonter les limites d'un seul nœud
principal dans notre architecture de Base de données SQL Windows Azure.
En raison de la nature partagée au sein d'une architecture mutualisée hautement disponible de certains
composants de notre système (les nœuds Base de données SQL Windows Azure et le référentiel d'objets
blob de stockage Windows Azure pour un ordinateur virtuel Windows Azure qui héberge SQL Server),
le fait d'envoyer trop de données dans un seul nœud peut créer des problèmes de performances
supplémentaires. Un exemple de problème de performances est la saturation du mécanisme de
réplication, qui entraîne le ralentissement de la totalité du processus de chargement de données.
14
Illustration 4 Représentation schématique d'une architecture équilibrée de chargement de données
Éléments à prendre en considération pour les types de données
Le schéma de la base de données, la création d'entités et les types de données utilisés peuvent avoir un
impact sur le processus de déplacement des données de différentes façons. En règle générale, les types
de données pouvant obtenir un taux de compression plus élevé fournissent plusieurs avantages lorsque
les données sont chargées en masse à partir de leurs sources dans les objets blob Windows Azure ou
dans le stockage local pour les opérations temporaires. Il va de soi, que le fait de compresser les
données avant de les transférer sur le réseau améliore les performances.
Empaquetage et déploiement de solution
Implémenter et déployer une solution qui s'étend dans les centres de données locaux et les environnements
de cloud signifie généralement que vous devez gérer plusieurs composants et services. Il est encore plus
important d'offrir un niveau élevé d'automation dans le déploiement et la configuration de tous ces
éléments si vous envisagez de déployer plusieurs instances de la solution de déplacement des données.
Les technologies de virtualisation aident à créer une image principale. L'image principale peut
éventuellement être utilisée sur site dans l'infrastructure d'ordinateurs virtuels Windows Azure afin de
simplifier le déploiement des services courants qui doivent être présents dans ces deux environnements.
En même temps, l'utilisation d'ordinateurs virtuels Windows Azure présente des limites par rapport à ce
que peuvent fournir d'autres nœuds de calcul Windows Azure, tels que les rôles Web et les rôles de travail,
en termes d'application et de packages et services corrélés (pensez aux tâches de démarrage, par exemple).
15
Si vous exploitez déjà des fonctionnalités de distribution de logiciels, telles que celles disponibles via les
familles de produits Windows Server et System Center, une autre possibilité consiste à distribuer les
composants de la solution et les packages avec certains composants exécutés dans le cloud et d'autres
exécutés dans les environnements sur site.
Une autre possibilité consiste à installer et configurer manuellement les différents composants de la
solution, dont SSIS et le Kit de développement logiciel (SDK) Windows Azure (pour accéder aux
fonctionnalités de stockage Windows Azure), ainsi que tous les packages d'installation d'application
(.msi) nécessaires dans chaque ordinateur virtuel qui s'exécute dans le cadre de l'environnement
distribué.
Créer des solutions portables
Un aspect devient encore plus important lorsque vous exécutez une solution dans une architecture de
montée en puissance parallèle. Il s'agit de la possibilité de reconfigurer rapidement des options, telles
que les chaînes de connexion, les informations d'identification et d'autres options de configuration qui
seront incluses dans la solution. Cela implique généralement une forme de mécanisme centralisé de
configuration, où les informations sont accessibles et propagées à tous les composants et services
impliqués dans le processus de déplacement des données pour garantir que chaque modification
nécessite un effort minimal. Les deux outils standard, tels que SSIS, et les composants et les applications
personnalisés, peuvent facilement être implémentés avec cette approche à l'esprit. Le stockage Windows
Azure peut être un bon choix pour stocker et gérer les données de configuration, étant donné qu'il est
facilement accessible et disponible pour les composants sur site et ceux du cloud.
Notez que la plateforme SSIS inclut déjà plusieurs fonctionnalités qui simplifient le portage et la mise
à l'échelle d'une solution, notamment les fichiers et les paramètres de configuration. Les processus et
les services supplémentaires qui composent la solution de bout en bout de déplacement des données
peuvent implémenter les mêmes types d'approches configurables, ce qui facilite le déplacement de la
solution entre différents environnements.
Distribution des packages et des composants de code
Une fois tous les éléments de la solution implémentés, le processus choisi pour distribuer physiquement
les différents packages SSIS et les composants de code sur plusieurs ordinateurs est essentiel. Un autre
aspect, encore plus essentiel, est la façon dont ces packages et éléments de code seront hébergés et
exécutés sur les différents serveurs et ordinateurs virtuels. Si l'environnement natif SSIS dans SQL
Server 2012 offre différents types de stockage de package et modèles de déploiement, développer une
solution de bout en bout de déplacement des données peut nécessiter des options de différence. Si
vous devez exécuter des services/applications d'orchestration pour superviser et contrôler le processus
de déplacement des données, comment ces services et applications peuvent-ils être implémentés ? Et
quelle part de l'infrastructure SSIS sous-jacente SSIS pouvez-vous utiliser ? Un exemple spécifique de
distribution et de coordination des composants est fourni dans le livre blanc « SQL Server 2012 SSIS pour
Azure et le déplacement des données hybrides ». Ce document est disponible dans la bibliothèque
MSDN sous le nœud Livres blancs Microsoft pour SQL Server 2012.
16
Les ordinateurs virtuels Windows Azure et les serveurs Windows Server n'implémentent pas certaines
des fonctionnalités fournies par la plateforme Windows Azure pour les rôles Web et les processus de
travail. Par conséquent, le meilleur choix consiste à implémenter ces composants en tant que services
Windows, afin que les processus démarrent au démarrage des différents hôtes, et continuent à s'exécuter
de manière indépendante à partir d'une session utilisateur interactive sur cet ordinateur particulier.
La plateforme .NET rend très facile la création et l'empaquetage de ce type de package logiciel qui peut ensuite
être distribué et déployé sur les différents hôtes à l'aide des options qui ont été précédemment décrites.
Les services et applications d'orchestration interagissent avec les différents composants externes
(stockage d'objets blob Windows Azure, files d'attente, etc.), appellent le moteur d'exécution SSIS
(DtExec.exe) et orchestrent les tâches de chargement et de transformation de données sur les différents
hôtes ou ordinateurs virtuels.
Les composants personnalisés nécessaires pour l'exécution du package devront également être
distribués sur plusieurs nœuds.
Avec cette approche distribuée, un environnement de déploiement et d'exécution fiable, portable et
flexible peut être créé pour héberger notre solution de bout en bout de déplacement des données dans
une infrastructure entièrement hybride.
17
Base de données SQL Windows Azure comme destination de déplacement des données
Si SQL Server et les Bases de données SQL Windows Azure présentent des similitudes, il est erroné de les
considérer comme étant identiques. Il existe plusieurs différences qui peuvent affecter la manière dont
les applications s'exécutent sur la base de données SQL Windows Azure par rapport à une base de
données SQL Server.
La base de données SQL Windows Azure est un service hébergé implémentant une architecture
entièrement mutualisée. Contrairement aux implémentations traditionnelles de SQL Server, Base de
données SQL Windows Azure contient des fonctionnalités telles que la haute disponibilité intégrée et les
sauvegardes automatiques, et elle s'exécute sur le matériel de base et non sur des serveurs de grande
taille. La base de données exécute un sous-ensemble des fonctionnalités couramment utilisées dans
les environnements sur site, notamment la compression de base de données, les requêtes parallèles,
les index columnstore, le partitionnement de table, etc. Pour plus d'informations sur les limites des
fonctionnalités pour Base de données SQL Windows Azure, consultez Limitations des fonctionnalités SQL
Server (Base de données SQL Windows Azure) (http://msdn.microsoft.com/fr-fr/library/windowsazure/
ff394115.aspx).
Une des plus grandes différences entre Base de données SQL Windows Azure et SQL Server est que Base
de données SQL Windows Azure expose un service mutualisé de montée en puissance parallèle, où les
abonnements individuels partagent les ressources d'un ou plusieurs ordinateurs dans un centre de
données Microsoft. L'objectif vise à équilibrer la charge globale dans le centre de données en déplaçant
occasionnellement les clients vers différents ordinateurs. Ces ordinateurs sont des serveurs en rack
standard, permettant d'optimiser le rapport coût/performances plutôt que les performances globales.
Tous les nœuds Base de données SQL Windows Azure n'utilisent pas un matériel très haut de gamme
dans le cadre de l'offre hébergée.
Lorsqu'une application cliente doit dépasser les capacités d'un seul ordinateur, l'application doit être
modifiée de façon à répartir la charge de travail du client entre plusieurs bases de données (probablement
plusieurs ordinateurs) plutôt qu'un seul serveur. Un des inconvénients de l'élasticité et de la gestion de
cet environnement est que parfois votre application peut se déplacer vers un autre ordinateur de façon
inattendue. Les sessions étant sans état, la création de l'application doit utiliser des techniques qui évitent
les points d'échec uniques. Cela inclut la mise en cache dans d'autres couches si nécessaire, ainsi que
l'emploi d'une logique de nouvelle tentative pour la connexion et les commandes, résiliente à l'échec.
En outre, les différentes couches d'une infrastructure Windows Azure ne vont pas se trouver sur le
même sous-réseau, et il y aura, par conséquence, certaines différences de latence entre les applications
clientes et Base de données SQL Windows Azure. Ceci s'applique même si les applications et les bases de
données sont hébergées dans le même centre de données physique. Les solutions traditionnelles de
chargement de données SQL Server qui sont très « bavardes » peuvent s'exécuter plus lentement dans
Windows Azure en raison de ces différences physiques de réseau. Pour ceux qui sont familiarisés avec
l'informatique client-serveur d'origine, les mêmes solutions s'appliquent ici : concevez soigneusement
les boucles entre les couches d'une solution pour gérer toutes les différences de latence visibles.
18
Considérations sur l'architecture
Certains des problèmes les plus courants avec les packages SSIS sont liés à la gestion des erreurs
inattendues lors de l'exécution et à la manière de réduire le temps nécessaire pour terminer l'exécution
d'un processus ETL lorsque vous devez reprendre le traitement après une défaillance. Pour les tâches de
flux de contrôle, telles que les tâches du système de fichiers, des points de contrôle peuvent être utilisés
pour reprendre l'exécution sans retraiter le travail déjà effectué. Pour obtenir des instructions sur
l'utilisation des points de contrôle, consultez Redémarrer des packages à l'aide de points de contrôle
(http://msdn.microsoft.com/fr-fr/library/ms140226.aspx).
Souvent, le flux de données constitue la plus grande partie du package SSIS. Dans cette section, vous
verrez des stratégies pour concevoir des packages autorisant une nouvelle tentative automatique après
l'échec, et concevoir le pipeline de flux de données pour vous permettre de redémarrer à partir du point
d'échec plutôt que d'effectuer une nouvelle tentative dans le flux de données entier.
Les éléments supplémentaires à prendre en compte pour la gestion des erreurs temporaires de la
plateforme Windows Azure sont détaillés dans le livre blanc « SQL Server 2012 SSIS pour Windows Azure
et le déplacement de données hybride ». Ce document est disponible dans la bibliothèque MSDN sous le
nœud Livres blancs Microsoft pour SQL Server 2012.
Conception pour un redémarrage sans perte de progression dans le pipeline
Lors de la conception des packages, l'un des soucis majeurs réside dans la conception de façon à ce qu'en
cas de défaillance, vous ne perdiez pas la progression du package jusqu'à ce point. Pour les éléments du
flux de contrôle d'un package, cette opération s'effectue en utilisant des points de contrôle. Toutefois,
être en mesure de redémarrer un flux de données sans perte de progression est uniquement possible
grâce à la conception du package. Dans cette section, vous verrez une stratégie pour la conception de
package qui permet de redémarrer le flux de données à partir du point d'échec, et permet au package
d'effectuer automatiquement une nouvelle tentative de façon à ce qu'il n'échoue pas en cas de suppression
de connexion. La planification de ces suppressions ou interruptions courtes de connexion est particulièrement
importante lors du déplacement de données vers ou à partir de Base de données SQL Windows Azure.
Principe de base
Même si les performances sont importantes dans tout déplacement de données, vous devez équilibrer
les exigences en matière de performances par rapport à la progression que vous pouvez vous permettre
de perdre si une erreur se produit lors du déplacement de données.
19
Dans chaque déplacement de données, vous devez disposer d'un moyen de savoir quelles données ont
été déchargées ou non dans la destination. En cas d'insertion de données uniquement, cela peut être
déterminé par la clé primaire. Pour d'autres données, cela peut être la date de la dernière modification.
Quelle que soit la nature des données, la première partie de la conception du redémarrage consiste
à comprendre comment vous identifiez les données qui existent déjà dans la destination, les données
qui doivent être mises à jour dans la destination et les données qui doivent être remises à la destination.
Une fois que vous avez établi ces points, segmentez et triez les données d'une manière qui vous permet
de traiter uniquement les données qui ne sont pas arrivées à destination, ou de réduire la révision à une
étape quelconque.
La segmentation et le tri vous permettent de suivre facilement les segments qui ont été traités, puis le
tri vous permet de suivre les enregistrements déjà traités dans n'importe quel segment. Avec cette
approche, vous n'avez pas besoin de comparer chaque ligne source avec la destination afin de savoir si
l'enregistrement a été traité.
Les processus ETL plus complexes peuvent avoir plusieurs environnements temporaires. Chaque
environnement temporaire est une destination pour une étape du processus ETL. Dans ces types
d'environnements, considérez chacun de vos environnements temporaires comme une destination
distincte, puis créez chaque segment de votre processus ETL pour le redémarrage.
Exemple avec une seule destination
Le flux de données le plus simple pouvant être redémarré est de petite taille avec un seul entier comme
clé primaire. Lorsque SQL Server constitue la source, exécutez une requête par rapport à cette source en
utilisant la meilleure pratique de limitation des données extraites de la source. Considérez l'exemple de
table classique d'AdventureWorks : « Production.TransactionHistory ». La table a la structure suivante :
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,
Dans cet exemple, la table possède un seul entier comme clé primaire. Si les données sont statiques au
moment du déplacement, ou si les données sont uniquement insérées dans cette table (aucune mise
à jour), tout ce que vous devez savoir est si une ligne arrivée à destination est la clé primaire. Comparer
chaque valeur de clé primaire avec la destination est une opération relativement coûteuse. Utilisez
plutôt une stratégie de tri des données dans le fichier source par la colonne TransactionID.
20
Avec une telle stratégie, la seule chose que vous devez savoir est que les données sont traitées dans
l'ordre et quels sont les TransactionID les plus élevés validés dans la destination.
Dans un package SSIS, effectuez cette opération comme suit.
1. Vérifiez la clé la plus élevée dans votre destination.
2. Générez votre requête dans la source pour extraire uniquement les enregistrements avec un
TransactionID supérieur au TransactionID le plus élevé dans la destination.
3. Utilisez la clause ORDER BY TransactionID dans votre requête source pour garantir que la
comparaison du TransactionID le plus élevé reste valide au démarrage suivant de votre package.
Dans SSIS, avec une source de données relationnelle comme source, utilisez une tâche d'exécution SQL
pour extraire la valeur la plus élevée dans une variable de votre package. Toutefois, tenez compte de la
possibilité qu'aucune ligne n'existe dans la destination.
SELECT MAX(TransactionID) FROM Production.TransactionHistory
Pour récupérer la valeur maximale de TransactionID, considérez que le résultat d'une table vide est Null.
Cela peut poser des problèmes dans la logique que vous utilisez dans votre package. Une meilleure approche
consiste à utiliser d'abord une tâche d'exécution SQL dans la source et rechercher la valeur minimale de
TransactionID non traités dans la source. Ensuite, interrogez la valeur maximale de TransactionID de la
destination, ou si aucune n'existe, puis utilisez une valeur inférieure à la valeur minimale de TransactionID
de la source. Générez votre requête source pour extraire uniquement les enregistrements supérieurs
à cette valeur, et n'oubliez pas d'utiliser ORDER BY TransactionID dans votre requête.
REMARQUE : bien que logiquement, vous obteniez les mêmes résultats en récupérant uniquement une
valeur de votre destination, et en utilisant une fonction ISNULL, ou une instruction CASE dans la clause
WHERE de la requête source, cela peut affecter les performances, surtout si vos requêtes source gagnent
en complexité. Résistez à la tentation de prendre ce raccourci. Recherchez plutôt une valeur que vous
pouvez utiliser sans risque en tant que limite inférieure, et générez votre requête source avec cette valeur.
REMARQUE : lorsque la source est SQL Server, l'utilisation d'une clause ORDER BY dans un index cluster
n'entraîne pas de travail supplémentaire de SQL Server pour le tri. Les données sont déjà classées de
sorte qu'elles puissent être récupérées sans exécuter d'opération SORT. Si les données de la destination
possèdent également un index cluster sur la même colonne, le tri dans la source optimise la requête
source et également les insertions dans la destination. L'autre effet est la garantie de l'ordre dans le
pipeline SSIS, ce qui vous permet de redémarrer le flux de données au point de défaillance.
Pour créer cet exemple de package, procédez comme suit.
1. Créez un nouveau package dans un projet nouveau ou existant. Renommez le package
« RedémarrageSimple ».
2. Créez des gestionnaires de connexions pour vous connecter à la source et à la destination.
Pour cet exemple, un gestionnaire de connexions OLE DB est créé pour les serveurs source et
de destination.
21
3. Faites glisser une nouvelle tâche d'exécution SQL sur la surface de flux de contrôle et
renommez-la « Pull Min TransactionID From Source ».
4. Créez une variable SSIS au niveau du package et nommez-la minTransactionIDAtSource. Nous
allons utiliser cette variable pour stocker la valeur extraite de la tâche d'exécution SQL que vous
venez d'ajouter. Vérifiez que le type de données est Int32 de façon à ce qu'il corresponde à la
valeur du TransactionID dans la table, et définissez une valeur initiale appropriée.
5. Configurez Pull Min TransactionID From Source comme suit.
a. Modifiez-le et définissez la Connexion de façon à ce que ce soit le gestionnaire de
connexions pour votre serveur source.
b. Bien que vous puissiez enregistrer votre SQLStatement dans une variable, pour cet
exemple conservez SQLSourceType comme Entrée directe. Ouvrez la fenêtre d'entrée
pour SQLStatement, puis entrez la requête suivante :
SELECT ISNULL(MIN(TransactionID), 0) FROM Production.TransactionHistory
REMARQUE : testez vos requêtes SQL avant de les écrire dans les éditeurs SSIS. Cela
simplifie le débogage, car il n'existe pas d'aide réelle sur le débogage dans les fenêtres
de l'éditeur de requête SSIS.
Illustration 5 : Configuration de la tâche d'exécution SQL pour rechercher le transactionID minimal dans la source.
22
c. Pour fermer la fenêtre Entrer une requête SQL, cliquez sur OK.
d. Affectez la valeur Ligne unique à la propriété ResultSet.
e. Dans le volet gauche de l'éditeur de tâche d'exécution SQL, cliquez sur Ensemble de
résultats pour configurer la façon dont vous allez capturer la valeur de cette requête.
f. Pour ajouter un jeu de résultats, cliquez sur Ajouter.
g. Dans le nouveau jeu de résultats, remplacez la valeur de Nom de résultat par 0. Vérifiez
que User::minTransactionIDAtSource (variable que vous avez créée au cours de l'étape 4)
s'affiche sous Nom de la variable. Cette variable va stocker le résultat de la requête SQL.
h. Fermez l'Éditeur de tâche d'exécution SQL. Aucune erreur ne doit s'afficher dans la
tâche après fermeture.
6. Faites glisser une autre tâche d'exécution SQL sur la surface de contrôle. Nommez cette tâche
Pull Max TransactionID from Destination. Connectez la contrainte de précédence de réussite de
Pull Min TransactionID From Source à cette nouvelle tâche.
7. Créez une variable dont l'étendue est le package. Nommez cette nouvelle variable
maxTransactionIDAtDestination. Attribuez-lui le type de données Int32 pour qu'il corresponde
au type de données du TransactionID, et attribuez-lui une valeur initiale appropriée.
8. Ouvrez l'Éditeur de tâche d'exécution SQL pour la nouvelle tâche et procédez comme suit :
a. Affectez la valeur Ligne unique à la propriété ResultSet.
b. Paramètre du gestionnaire de connexions du serveur de destination
c. SQLSourceType : Entrée directe
d. Pour SQLStatement, utilisez SELECT ISNULL(MAX(TransactionID), ?) FROM
Production.TransactionHistory
REMARQUE : ? représente un paramètre de requête. Nous allons définir la valeur de ce
paramètre momentanément.
e. Fermez l'Éditeur de requêtes en cliquant sur OK, puis cliquez sur Mappage de
paramètre dans le volet gauche de l'Éditeur de tâche d'exécution SQL.
f. Pour ajouter un seul paramètre, cliquez sur Ajouter.
i. Pour Nom de la variable, choisissez User::minTransactionIDAtSource.
ii. Pour Direction, vous devez choisir Entrée.
iii. Le type de données doit être LONG, qui est un entier 32 bits dans ce contexte.
iv. Remplacez le Nom du paramètre par 0. Notez que cette valeur doit être
remplacée par 0. Le nom du caractère génère une erreur.
g. Dans le volet gauche, cliquez sur Ensemble de résultats. Pour ajouter un ensemble de
résultats, cliquez sur Ajouter.
i. Remplacez le Nom de résultat par 0.
ii. Sous Nom de la variable, choisissez User::maxTransactionIDAtDestination qui
est la variable que vous avez créée à l'étape 7. Cette variable contient le résultat
de la requête entrée après exécution de cette tâche.
23
REMARQUE : l'étape suivante varie selon le type de source que vous allez utiliser dans votre flux
de données. Une source OLE DB peut utiliser une variable SSIS qui contient une instruction SQL
en tant que requête. Une connexion ADO.NET ne peut pas effectuer cette opération, mais elle
peut être paramétrée de façon à utiliser un paramètre du projet comme requête source.
Dans ce premier exemple, vous allez utiliser une source OLE DB avec une variable contenant la
requête source.
9. Faites glisser une tâche de flux de données sur votre surface de contrôle. Renommez-la
Déplacement de données principal, puis connectez la contrainte de précédence de réussite de
Pull Max TransactionID From Destination à cette tâche de flux de données.
Lorsque le package s'exécute à ce stade, vous avez enregistré les valeurs que vous devez
connaître pour créer le point de départ de l'exécution actuelle. Ensuite, vous devez configurer
une variable pour qu'elle contienne la requête source SQL.
10. Créez une variable dont l'étendue est le niveau du package. Nommez cette variable sourceQuery
et définissez le type de données à chaîne. Vous allez utiliser une expression pour dériver
dynamiquement la valeur de cette variable au moment de l'exécution, en fonction de la valeur
que vous avez déterminée comme point de départ pour votre requête, en procédant comme suit.
a. À droite de la colonne Expression, cliquez sur le bouton (...) pour ouvrir la boîte de
dialogue Générateur d'expression.
b. Développez le nœud Variables et paramètres dans la fenêtre supérieure gauche du
Générateur d'expressions. Vous allez utiliser la variable
User::MaxTransactionIDAtDestination créée à l'étape 7. Cette variable doit s'afficher
dans les variables répertoriées. Cette variable est de type Int32 ; toutefois, vous allez
l'utiliser dans le cadre d'une variable chaîne. Pour cela, vous devez la convertir en type
de données DT_WSTR. Dans le volet supérieur droit, développez le nœud Casts de type
pour rechercher le cast de type (DT_WSTR), <<length>>.
c. Dans la zone Expression, tapez votre requête. Là où vous avez besoin du nom de la
variable, ou du cast de type, faites-les glisser à partir de la fenêtre appropriée dans la
zone Expression pour les ajouter. Cela permet de réduire le nombre de fautes de frappe
dans cet éditeur. Créez l'expression comme suit :
« SELECT TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID,
TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate FROM
Production.TransactionHistory WHERE TransactionID > » + (DT_WSTR, 12 )
@[User::maxTransactionIDAtDestination] + « ORDER BY TransactionID »
Notez l'utilisation du cast de type pour changer la valeur entière en une chaîne avec un
maximum de 12 caractères.
Une largeur de 12 caractères a été utilisée, car elle est suffisante pour contenir la
gamme complète de valeurs INT SQL, notamment les valeurs négatives, le cas échéant.
Pour BIGINT, vous aurez besoin d'une largeur de 22 caractères. Dimensionnez votre
variable caractère selon le type de données récupérées.
24
Une fois l'expression entrée, cliquez sur le bouton Évaluer l'expression pour vous
assurer que SSIS peut analyser l'expression correctement. Vous constaterez qu'il a
correctement placé la valeur initiale de maxTransactionIDAtDestination dans
l'expression évaluée. Cette valeur est définie correctement au moment de l'exécution.
Veillez à inclure la clause ORDER BY dans l'instruction SQL. Seule une clause ORDER BY
permet d'obtenir un ordre garanti d'une base de données relationnelle. La méthode de
redémarrage que vous créez dépend de l'ordre des valeurs de clé.
d. Pour fermer le Générateur d'expressions, cliquez sur OK. L'instruction SQL construite
dynamiquement est désormais stockée dans la variable SourceQuery.
11. Double-cliquez sur la tâche de flux de données Déplacement de données principal pour ouvrir
l'aire de conception du flux de données.
12. Faites glisser une source OLE DB sur votre surface de contrôle de flux de données. Renommez-la
Retrieve from TransactionHistory.
REMARQUE : vous ne pouvez pas incorporer un point dans le nom d'un composant de flux de
données ; par conséquent, le nom complet Pull From Production.TransactionHistory n'est pas
autorisé. Si vous n'utilisez pas de traits de soulignement pour les noms de tables, remplacez le
trait de soulignement par un point dans la convention d'affectation des noms SSIS.
13. Double-cliquez sur la source Retrieve From TransactionHistory pour ouvrir l'Éditeur de source OLE DB.
a. Pour Gestionnaire de connexions OLE DB, choisissez votre gestionnaire de connexions
de Serveur source.
b. Dans la liste déroulante Mode d'accès aux données, choisissez Commande SQL à partir
d'une variable.
c. Dans la liste déroulante Nom de la variable, sélectionnez la variable User::sourceQuery
créée à l'étape 10.
d. Cliquez sur Aperçu pour garantir l'exécution de votre requête sur le serveur source.
e. Dans la page Colonnes de l'éditeur, assurez-vous que toutes les colonnes sont
sélectionnées.
f. Cliquez sur OK pour fermer la boîte de dialogue Éditeur de source OLE DB.
14. Faites glisser une destination OLE DB sur la surface de contrôle. Renommez-la
TransactionHistory Destination. Connectez la source Retrieve From TransactionHistory à la
nouvelle destination. Double-cliquez pour ouvrir la destination et configurez en procédant
comme suit.
a. Sélectionnez le gestionnaire de connexions de Serveur de destination dans la liste
déroulante Gestionnaire de connexions OLE DB.
b. Pour Mode d'accès aux données, choisissez Table ou vue - chargement rapide si ce
n'est déjà fait.
25
c. Sous Nom de la table ou de la vue, choisissez dans la liste déroulante, ou tapez le nom
de votre serveur de destination. Dans ce cas, il s'agit de Production.TransactionHistory.
d. Si vous utilisez la définition de TransactionHistory fournie ci-dessus, pour la
démonstration, conservez les paramètres par défaut de la page Gestionnaire de
connexions. Si vous utilisez une base de données AdventureWorks, vous devez
sélectionner conserver valeurs identité.
e. Sur la page Mappages de l'éditeur, mappez vos colonnes.
REMARQUE : dans presque tous les cas, il est conseillé d'envoyer des lignes d'erreur
vers un fichier de destination et de rediriger la sortie. Cela n'est pas nécessaire pour
illustrer le redémarrage sur le pipeline. Pour connaître les étapes de création d'un flux
d'erreurs et de redirection des lignes, consultez Configurer une sortie d'erreur dans un
composant de flux de données (http://msdn.microsoft.com/fr-fr/library/ms140083.aspx).
f. Cliquez sur OK pour fermer l'Éditeur de destination OLE DB.
15. Pour tester cette approche, démarrez et arrêtez une importation, puis redémarrez. Chaque fois,
le flux de données doit redémarrer à la ligne suivante qui doit être déplacée. Les lignes
précédentes sont ignorées.
Vous venez de créé un package simple qui peut redémarrer le flux de données après une défaillance.
Ce package sera utilisé comme point de départ pour l'exemple de conception d'un package qui peut
effectuer une nouvelle tentative. Notez que vous ne souhaitez pas que les tâches du flux de contrôle
enregistrent les points de contrôle. Si le package échoue et doit être redémarré, vous avez besoin que
les composants Pull Min TransactionID From Source et Pull Max TransactionID From Destination
s'exécutent afin de trouver exactement où le flux de données a été abandonné lors de l'exécution
précédente. La conception d'un package pour pouvoir rechercher sa progression et redémarrer son flux
est recommandée lorsque les caractéristiques existent dans les données de façon à pouvoir rechercher
le point auquel le flux de données a été interrompu. Toutefois, cette pratique est particulièrement
importante dans un environnement tel que le cloud, qui est plus vulnérable aux aléas du réseau.
Exemple avec plusieurs destinations
Ce principe peut être étendu aux flux de données avec plusieurs destinations. Cela s'avère utile lors du
déplacement de données dans un modèle de données partitionné comme cela est souvent le cas avec
Base de données SQL Windows Azure. Dans ce cas, un fractionnement conditionnel permet d'envoyer
chaque ligne à la destination appropriée, ou pour les données de référence, la multidiffusion peut être
utilisée pour envoyer les données à toutes les destinations. Vous trouverez ci-dessous les règles
à retenir pour une conception pour le redémarrage.
26


Les différentes destinations ont pu progressé jusqu'à des points différents au moment de la
défaillance. Par conséquent, vous devez rechercher la valeur de clé la plus élevée insérée dans
chaque destination. Créez une variable pour contenir la valeur la plus élevée dans chaque
destination.
Le point de départ dans la source est l'enregistrement suivant après la valeur de clé la plus basse
qui a été correctement insérée dans les destinations. Par exemple, si la valeur de clé la plus
élevée insérée dans un jeu de partitions se présente comme suit, dans la source, vous devez
reprendre le flux de données à l'enregistrement suivant après 1000.
o Shard00 key value 1000
o Shard01 key value 1100
o Shard02 key value 1050
Dans la mesure où cela laisse la possibilité d'extraire certaines données plusieurs fois, vous devez
filtrer chaque destination afin d'empêcher une violation de clé primaire. Utilisez une transformation
de fractionnement conditionnel pour filtrer les valeurs de clé qui ont déjà été traitées.
Dans l'exemple de partitionnement ci-dessus, vous devez créer les variables nommées
maxTransactionIDAtShard00, maxTransactionIDAtShard01 et maxTransactionIDAtShard02. Dans
les tâches d'exécution SQL, vous devez rechercher les valeurs à stocker dans chacune d'elles.
Dans le fractionnement conditionnel, vous pouvez définir des sorties nommées Shard00,
Shard01 et Shard02. Les expressions dans les sorties doivent ressembler aux suivantes.
ShardID == 0 && TransactionID > @[User::maxTransactionIDAtShard00]
ShardID == 1 && TransactionID > @[User::maxTransactionIDAtShard01]
ShardID == 2 && TransactionID > @[User::maxTransactionIDAtShard02]
Si un nombre de lignes inférieur au TransactionID entrent dans le pipeline d'une partition
spécifique, mais que l'enregistrement va dans cette partition, l'enregistrement n'est pas envoyé
et aucune violation de clé primaire n'est rencontrée. Laissez ces enregistrements accéder à la
sortie par défaut, ou à une sortie déconnectée afin qu'ils ne soient plus traités.
27
Illustration 2 : Un fractionnement conditionnel configuré pour 3 partitions et configuré pour autoriser le redémarrage sans
violations de clé primaire dans la destination. Au début de toute exécution, la valeur de clé maximale dans chaque destination
est stockée dans des variables maxTransactionIDAtShardXX. Dans le pipeline, les lignes destinées à cette partition avec une
valeur de clé trop basse ne sont pas transmises à la destination, et ne sont pas retraitées. Ces lignes sont envoyées vers la
sortie par défaut. Étant donné que la sortie par défaut n'est pas connectée, elles ne progresseront pas dans le pipeline.
Autres conseils pour le redémarrage

28
Lorsque la destination est un fichier, utilisez une variable pour représenter le nom de fichier et
rendre chaque nom de fichier explicite (notamment avec un classement ou un horodateur
ajouté à la fin). Traitez les différents segments de données dans des fichiers distincts. Si vous
effectuez le traitement dans le même ordre, et les segments sont définis de manière
déterministe, déterminez quels fichiers sont déjà créés et déterminez le point de départ à partir
de là. En fonction de vos besoins et de la logique, vous devrez peut-être déplacer, renommer ou
supprimer les fichiers qui ont déjà été traités.



Si la source est constituée de plusieurs fichiers, traitez chaque fichier séparément et suivez le
fichier actif. Au redémarrage, reprenez après le dernier fichier qui a été correctement traité.
Utilisez un classement par nom de fichier ou par date pour vous assurer que vous traitez les
fichiers dans le même ordre à chaque exécution et pouvoir ainsi aisément déterminer quel est le
dernier fichier qui a été traité.
Lorsque SQL Server est la source, utilisez un type de données chaîne SSIS pour rechercher les
valeurs entières. Du moment que la chaîne peut être convertie en entier SQL, SQL convertira le
type de données pendant l'optimisation. Avec ce principe, changez le type des variables
MaxTransactionIDAtDestination et MinTransactionIDAtSource dans l'exemple de package en
chaîne, modifier les types de paramètre d'entrée dans Pull Max TransactionID From Destination
et utilisez ce package comme modèle qui fonctionne également avec des clés primaires de
caractères. N'utilisez pas un type de données SQL_VARIANT sur un autre type de données dans
SQL, car cela entraîne des analyses complètes lors de la récupération des valeurs de clé
minimale et maximale.
Si la source est un fichier, ou n'importe quelle source où vous ne pouvez pas exécuter une
requête avec une clause WHERE ou tout autre type de clause de filtre, effectuez les opérations
suivantes dans votre flux de données.
1. Insérez une transformation de fractionnement conditionnel entre les composants
source et de destination.
2. Dans le fractionnement conditionnel, configurez une sortie. Nommez cette sortie Corbeille
ou avec un autre nom qui vous permet de savoir que vous ne souhaitez pas ces lignes.
3. Pour la condition qui permet d'envoyer les lignes à la destination, utilisez une expression
pour filtrer les lignes qui sont déjà dans votre destination. Par exemple, l'expression de
l'exemple précédent doit être la suivante.
[TransactionID] <= @[User::maxTransactionIDAtDestination]
4. Ne connectez pas la sortie Corbeille à un composant, sauf si vous devez réellement
évaluer ces lignes. Son seul objectif est de filtrer les lignes que vous avez traitées lors de
votre dernière exécution.
Cela ne vous empêche pas de lire les lignes que vous avez déjà traitées dans ce fichier,
mais vous empêche de les renvoyer à votre destination, ce qui permet d'économiser la
bande passante réseau. Étant donné que ces lignes ne sont pas envoyées à votre
destination, vous n'avez pas besoin de supprimer des données de votre destination.
29

Lors du traitement de clés composites, utilisez les sous-requêtes corrélées pour rechercher le
point d'arrêt exact. Veillez à trier les données par les mêmes clés. Voici un exemple.
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
où h.EmployeeID = i_forSeq.EmployeeID
et h.EffectiveDate = i.ForSeq.EffectiveDate) AS EffectiveDate
FROM HumanResources.EmployeeHistory h
ORDER BY EmployeeID, EffectiveDate, EffectiveSequence
Notez le rôle de l'ordre dans cet exemple. Si vous déplacez tous les enregistrements de tous les
employés, et qu'il s'agit de l'ordre de vos clés, cet ordre est approprié. Toutefois, si tous les (ou
certains) employés existaient déjà dans la destination, et vous souhaitez importer uniquement
les modifications qui sont entrées en vigueur depuis la dernière importation, le tri par
EffectiveDate, EffectiveSequence et EmployeeID est celui que vous devez utiliser pour pouvoir
reprendre là où vous avez arrêté. Analysez ce que vous importez pour définir l'ordre qui vous
permet de déterminer le point auquel vous devez reprendre.
Conception pour nouvelle tentative sans intervention manuelle
Les composants source et de destination SSIS n'incorporent pas directement la logique de nouvelle
tentative. Toutefois, le développeur de package SSIS n'est pas sans recours pour gérer cette situation.
Le fait de concevoir vos packages de façon à pouvoir redémarrer vos packages SSIS sans perdre la
progression dans votre flux de données vous permet également d'effectuer automatiquement une
nouvelle tentative après un échec avec certaines éléments supplémentaires à prendre en considération.
Par exemple, lors du déplacement de données vers ou à partir de Bases de données SQL Windows
Azure, vous devrez peut-être effectuer automatiquement une nouvelle tentative pour gérer les
conditions d'erreurs temporaires telles que des ressources ou des connexions qui sont limitées. Cette
section s'appuie sur les concepts illustrés dans la dernière section et illustre une logique simple de
nouvelle tentative dans votre package. Elle illustre ensuite l'utilisation d'une approche segmentée pour
permettre une nouvelle tentative de chaque segment de façon à ajouter la robustesse à votre package
pendant des périodes où des erreurs temporaires sont les plus probables.
Pour plus d'informations sur la limitation SQL, consultez Performances de la Base de données SQL
Windows Azure et guide de résilience (http://social.technet.microsoft.com/wiki/contents/articles/
3507.windows-azure-sql-database-performance-and-elasticity-guide.aspx).
30
Incorporation de nouvelle tentative
Dans le cas d'un seul package, l'incorporation de nouvelle tentative implique les étapes suivantes.
1. Déterminez le nombre maximal de tentatives d'exécution du package avant qu'il se solde par
un échec. Dans le cadre de cet exemple, nous allons déterminer qu'un composant sera autorisé
à effectuer un maximum de cinq tentatives avant que le package ne se solde par un échec. Créez
une variable dans le package nommé maxNumOfRetries. Affectez à cette variable le type int et
attribuez-lui la valeur 5. Elle sera utilisée dans les expressions du package.
2. Configurez une variable pour stocker l'état de réussite. Créez un nouvelle variable dans le
package SSIS. Nommez cette variable attemptNumber.
3. Utilisez une boucle FOR jusqu'au nombre maximal de tentatives si le flux de données échoue.
31
4. Insérez la tâche de flux de données à l'intérieur de la boucle FOR.
5. Définissez la propriété MaximumErrorCount sur la boucle FOR au nombre maximal de
tentatives défini pour le flux de données de sorte qu'une réussite après une tentative n'entraîne
pas l'échec de votre package. Effectuez cette opération avec une expression qui utilise la
variable maxNumOfRetries que vous avez configurée à l'étape 1.
6. Utilisez les tâches d'exécution SQL comme indiqué dans la dernière section pour rechercher
les valeurs de clé minimales dans la source et les valeurs de clé maximales dans la destination.
Pour une nouvelle tentative simple, cela peut être effectué dans la boucle FOR. Pour obtenir des
exemples plus avancés, cela peut apparaître ailleurs dans le flux de contrôle avant la boucle
FOR.
7. Placez les tâches d'exécution SQL dans la boucle FOR.
8. Connectez la contrainte de réussite de la tâche d'exécution SQL à la tâche de flux de données.
9. Connectez la contrainte de précédence de réussite de la tâche de flux de données à une tâche
de script qui définit la variable d'état de réussite à true pour sortir de la boucle FOR. L'exemple
suivant montre la configuration de la tâche de script :
32
10. Connectez une contrainte de précédence d'échec de la tâche de flux de données à une autre
tâche de script lorsque vous vous attendez à ce qu'une durée soit plus longue que celle du
problème le plus fréquent ou le plus fastidieux prévu. Pour cet exemple nous utiliserons
10 secondes pour couvrir la possibilité de limitation. Affectez la valeur False à la variable de
réussite.
33
11. Pour chaque tâche dans la boucle FOR, affectez la valeur false à la propriété
FailPackageOnFailure. Dans cette configuration, uniquement lorsque la boucle FOR échoue
après toutes les tentatives de reprise configurées, un échec est signalé et le package échoue.
12. Configurez les tâches d'exécution SQL après l'échec afin de revérifier les valeurs de clé
minimales de la source et les valeurs de clé maximales de la destination afin que la progression
puisse reprendre sans réexécuter une tâche. Configurez ces tâches de la façon décrite dans la
sous-section Conception pour le redémarrage de la section Conception pour un redémarrage
sans perte de progressions dans le pipeline plus haut dans ce document.
34
À chaque erreur dans le flux de données, si le nombre maximal de nouvelles tentatives n'est pas atteint,
le processus revient au point de recherche de la valeur de clé maximale dans la destination. Le processus
continue le traitement à partir du point actuellement validé dans la destination. S'il y a plusieurs
destinations dans le flux de données, commencez après la valeur de clé la plus basse arrivée dans la
destination, et utilisez un fractionnement conditionnel ou un flux d'erreurs pour gérer toutes les lignes
qui risquent d'entraîner une violation de clé primaire.
Options de réglage des performances SSIS
Avant d'aborder les conseils et les astuces concernant le réglage des performances ETL, vous devez
revenir en arrière et examiner de quels composants et éléments non fixes vous disposez. Généralement
le processus ETL est composé d'un ou de plusieurs des composants suivants :



Source de données : fournit les données
Package exécuté sur le serveur SSIS : extrait les données de la source de données, traite les
transformations si cela est nécessaire et charge les données dans la destination
Destination : reçoit les données. Une destination est généralement une base de données avec
une ou plusieurs tables qui acceptent des données.
Tous ces éléments clés fonctionnent ensemble sur l'interface réseau et se transmettent des données.
Une des premières étapes de réglage des performances ETL consiste à s'assurer que le réseau offre des
performances optimales.
Pour plus d'informations sur le serveur SSIS, consultez Serveur Integration Services (SSIS)
(http://msdn.microsoft.com/fr-fr/library/gg471508.aspx).
Réglage des paramètres réseau
Il existe deux couches qui peuvent affecter la façon dont les données sont transmises entre les éléments
définis ci-dessus : la configuration réseau physique et les paramètres de connexion.
35
Paramètres réseau
La trame Ethernet définit le volume de données pouvant être transmises sur le réseau en même temps.
Chaque trame doit être traitée, ce qui requiert une certaine utilisation des ressources matérielles et
logicielles. En augmentant la taille de l'image prise en charge par la carte réseau, nous pouvons envoyer
plus d'octets avec moins de surcharge sur l'UC et augmenter le débit en réduisant le nombre de trames
devant être traitées.
Une trame Ethernet peut contenir jusqu'à 9 000 octets. Il s'agit d'une trame Jumbo. Pour passer
à l'utilisation de trames Jumbo, vous devez modifier les paramètres de vos cartes d'interface réseau.
Comme le montre l'exemple suivant, la valeur 8192 est affectée à la propriété MaxJumboBuffers pour
permettre l'utilisation de trames jumbo.
Remarque : lorsque vous modifiez les paramètres de votre carte d'interface réseau de façon
à utiliser les trames Jumbo, vérifiez que l'infrastructure réseau prend en charge ce type de
trame. Taille des paquets
SQL Server accepte jusqu'à 32 676 octets dans un paquet réseau SSIS. Généralement, si une application
a une taille de paquet par défaut différente, cette valeur par défaut remplace le paramètre SQL Server.
Par conséquent, il est recommandé de définir la propriété Taille de paquet du gestionnaire de
connexions de destination dans le package SSIS à la taille de paquet par défaut de l'application.
Pour modifier cette propriété, cliquez avec le bouton droit sur le gestionnaire de connexions dans le
Concepteur SSIS, puis cliquez sur Modifier. Dans la boîte de dialogue Gestionnaire de connexions,
cliquez sur Toutes.
36
Paramètres de package SSIS
Outre les paramètres de chaîne de connexion, il existe d'autres paramètres que vous pouvez souhaiter
modifier pour renforcer les fonctions de traitement de SQL Server.
Le flux de données SSIS réserve des mémoires tampon pour le traitement des données. Lorsque vous
disposez de serveurs dédiés avec plusieurs cœurs et une mémoire accrue, souvent les paramètres de
mémoire par défaut pour SSIS peuvent être modifiés de façon à mieux exploiter les fonctionnalités du
serveur SSIS.
Voici les paramètres de mémoire SSIS que vous devez envisager de modifier.



37
DefaultBufferSize
DefaultBufferMaxRows
EngineThreads
DefaultBufferSize et DefaultBufferMaxRows sont liés l'un à l'autre. Le moteur de flux de données
tente d'estimer la taille de la ligne de données. Cette taille est multipliée par la valeur stockée dans
DefaultBufferMaxRows et le moteur de flux de données tente de réserver le segment de mémoire
approprié pour la mémoire tampon.
[Valeur de taille de mémoire tampon] = [taille d'une seule ligne de données] x [DefaultBufferMaxRows]
Si la valeur de taille de mémoire tampon est supérieure à la valeur du paramètre DefaultBufferSize,
le moteur de flux de données réduit le nombre de lignes de données.
Si la valeur de taille de mémoire tampon minimale calculée en interne est supérieure à la taille de la
mémoire tampon, le moteur de flux de données augmente le nombre de lignes de données. Toutefois,
le moteur ne dépasse pas la valeur DefaultBufferMaxRows.
Lorsque vous modifiez les paramètres DefaultBufferSize et DefaultBufferMaxRows, il est recommandé
de prêter attention aux valeurs qui seront obtenues dans le moteur de flux de données lors de l'écriture
des données sur les disques. La mémoire dépaginée sur le disque du serveur SSIS a un impact négatif sur
les performances de l'exécution du package SSIS. Le compteur Mémoires tampon spoulées permet de
déterminer si des tampons de données sont écrits temporairement sur le disque lors de l'exécution d'un
package.
Pour plus d'informations sur les compteurs de performances pour les packages SSIS et l'obtention de
statistiques de compteurs, consultez Compteurs de performance (http://msdn.microsoft.com/fr-fr/
library/ms137622.aspx).
38
Le paramètre EngineThreads suggère au moteur de flux de données le nombre de threads pouvant être
utilisés pour exécuter une tâche. Lorsque le serveur multicœur est utilisé, il est recommandé d'augmenter
la valeur par défaut 10. Toutefois, le moteur n'utilisera pas plus de threads qu'il n'en faut, quelle que
soit la valeur de cette propriété. Si besoin est, pour éviter des problèmes de concurrence, le moteur
peut également utiliser plus de threads que le nombre spécifié dans cette propriété. Un bon point de
départ pour les packages complexes consiste à utiliser au moins un thread de moteur par arborescence
d'exécution sans descendre sous la valeur par défaut 10.
Pour plus d'informations sur les paramètres, consultez Fonctionnalités de performances de flux de
données (http://msdn.microsoft.com/fr-fr/library/ms141031.aspx).
Considérations spéciales relatives aux données BLOB
Lorsque plus de données se trouvent dans un pipeline SSIS défini qu'il n'en tient dans la mémoire
tampon prédéfinie du pipeline, les données sont mises en attente. Il s'agit d'un problème de
performances, notamment lorsque vous traitez des données BLOB, telles que des données XML, des
données texte ou des données d'image. Lorsque les données BLOB sont dans le pipeline, SSIS laisse la
moitié d'une mémoire tampon de côté pour les données de ligne, et la moitié de côté pour les données
BLOB. Les données BLOB ne tiennent pas dans la moitié de la mémoire tampon, par conséquent elles
sont mises en attente. Vous devez donc prendre les mesures suivantes pour paramétrer les packages
SSIS lorsque des données BLOB doivent être dans le pipeline :
1. Modifiez la valeur de BLOBTempStoragePath et de BufferTempStoragePath de façon à ce qu'ils
pointent vers un lecteur hautes performances. Par défaut, les objets mis en attente sont écrits
dans des fichiers temporaires dans le répertoire défini par les variables d'environnement TEMP
et TMP. Par défaut, ce répertoire se trouve sur le lecteur du système d'exploitation. En général,
le lecteur du système d'exploitation n'est pas un lecteur hautes performances. Pour vous
assurer que les fichiers spouleurs de données temporaires sont écrits dans un lecteur hautes
performances, modifiez la valeur de BLOBTempStoragePath dans les propriétés de la tâche de
flux de données SSIS de façon à ce qu'elle pointe vers un répertoire sur un stockage hautes
performances. Comme avec toutes les propriétés de SSIS, utilisez des expressions pour définir
cette valeur.
2. Dimensionnez DefaultBufferSize et DefaultBufferMaxRows de façon à réduire l'occurrence de
mise en file d'attente. Étant donné que les disques constituent généralement le composant le
plus lent sur le serveur, et leur vitesse est généralement bien plus faible que la vitesse du
processeur ou de la mémoire, l'utilisation inefficace de l'espace de mémoire tampon peut
s'avérer plus efficace que la mise en file d'attente. Pour réduire la mise en file d'attente en
raison des données BLOB, utilisez une méthode telle que la suivante afin de déterminer
DefaultBufferSize et DefaultBufferMaxRows lorsque vous avez des données BLOB dans votre
flux de données.
39
a. Déterminez MaxBufferSize. Étant donné que vous avez des données BLOB dans le flux
de données, un bon point de départ peut être la valeur maximale autorisée qui est
100 Mo ou 104 857 600 octets.
b. Divisez ce nombre par 2. Dans l'exemple, cela donne 52 428 800 octets. Il s'agit de la
moitié de la mémoire tampon qui peut contenir des données BLOB.
c. Sélectionnez une taille que vous allez utiliser pour la taille estimée des données BLOB
que vous allez traiter dans ce flux de données. Un bon point de départ pour cette taille
est la longueur moyenne + 2 écarts type de la longueur moyenne de toutes les données
BLOB qui figurent dans une mémoire tampon. Cette valeur contiendra environ 98 % de
toutes les données BLOB. Étant donné qu'il est probable que plusieurs lignes vont être
contenues dans une seule mémoire tampon SSIS, cela garantit qu'il n'y aura
pratiquement aucune mise en file d'attente.
 Si la source est SQL Server, utilisez une requête comme celle mentionnée cidessous pour obtenir la longueur.
SELECT CAST
(
AVG(DATALENGTH(ColName))
+ (2 * STDEV(DATALENGTH(Demographics)))
AS INT
) AS Length FROM SchemaName.TableName
 Si la table est trop volumineuse pour pemettre l'interrogation du jeu de
données complet de façon à obtenir la moyenne et l'écart type, utilisez une
méthode telle que celle qui est décrite dans Échantillonnage aléatoire dans T-SQL
(http://msdn.microsoft.com/fr-fr/library/aa175776(v=SQL.80).aspx) pour
rechercher un exemple à partir duquel déterminer la longueur à utiliser.
d. Divisez le nombre que vous avez obtenu à l'étape b par le nombre que vous avez obtenu
à l'étape c. Utilisez ce nombre ou un nombre légèrement plus petit en tant que valeur
de DefaultBufferMaxRows pour la tâche de flux de données.
CONSEIL : DefaultBufferMaxRows et MaxBufferSize sont configurables au moyen d'expressions. Tirez
parti de cela pour les jeux de données où la nature statistique de la longueur des données BLOB peut
changer souvent, ou pour la création de packages de modèle en vue de définir ces valeurs lors de
l'exécution. Pour rendre ces propriétés dynamiques, procédez comme suit.
1. Créez une variable au niveau du package. Nommez la nouvelle variable
DefaultMaxRowsInBuffer. Conservez le type de données Int32. Créez une variable similaire si
vous souhaitez définir la propriété MaxBufferSize de façon dynamique.
2. Utilisez une tâche d'exécution SQL ou une tâche de script pour rechercher la valeur que vous
allez utiliser pour DefaultBufferMaxRows. Enregistrez la valeur calculée dans la variable
DefaultMaxRowsInBuffer que vous avez créée à l'étape 1.
REMARQUE : Pour plus d'informations sur l'utilisation d'une tâche d'exécution SQL pour
récupérer une valeur unique dans une variable SSIS, consultez Jeux de résultats de la tâche
d'exécution SQL (http://technet.microsoft.com/fr-fr/library/cc280492.aspx).
40
3. Dans la zone de propriétés de la tâche de flux de données dans laquelle vous souhaitez définir
DefaultBufferMaxRows, sélectionnez Expressions pour ouvrir la boîte de dialogue Éditeur
d'expressions de la propriété.
4. Dans l'Éditeur d'expressions de la propriété, choisissez DefaultBufferMaxRows dans le menu
déroulant Propriété, puis cliquez sur le bouton (...) pour ouvrir le Générateur d'expressions.
5. Faites glisser la variable que vous avez créée à l'étape 1 de la liste Variables et paramètres dans
le coin supérieur gauche dans la zone Expression, puis cliquez sur Évaluer l'expression pour
afficher la valeur par défaut de la variable dans la zone Valeur évaluée.
6. Cliquez sur OK dans les boîtes de dialogue Générateur d'expressions et Éditeur d'expressions de
la propriété pour enregistrer la configuration. Dans cette configuration, la valeur des propriétés
est définie au moment de l'exécution pour réduire les possibilités de mise en file d'attente des
données BLOB sur les disques.
Utilisation des nouvelles fonctionnalités de SSIS 2012 pour analyser les
performances sur un système distribué
Il existe de nouvelles fonctionnalités disponibles dans SQL Server 2012 pour surveiller les performances
des projets Integration Services (SSIS) que vous déployez sur le serveur SSIS. Stockez les informations de
performance d'exécution pour une exécution de package, affichez les statistiques d'exécution de package
et surveillez le flux de données du package.
41
Enregistrer les statistiques de performances
Pour spécifier l'étendue des informations enregistrées lors d'une exécution de package, sélectionnez un
des niveaux d'enregistrement suivants. Pour enregistrer les statistiques de performances, sélectionnez
le niveau de journalisation Performance ou Commentaires.
Niveau de
journalisation
Aucun
Valeur
Description
0
Basic
1
La journalisation est désactivée. Seul l'état d'exécution du package est
enregistré.
Tous les événements sont enregistrés, sauf les événements personnalisés
et de diagnostic. Ceci est la valeur par défaut.
Performance
2
Seules les statistiques de performances, et les événements OnError et
OnWarning, sont enregistrés.
Commentaires
3
Tous les événements sont enregistrés, y compris les événements
personnalisés et de diagnostic.
Integration Services fournit un ensemble complet d'événements
personnalisés permettant d'écrire des entrées de journal pour des
packages et bon nombre de tâches. Utilisez ces entrées pour enregistrer
des informations détaillées sur l'avancement, les résultats et les problèmes
d'exécution en enregistrant des événements prédéfinis ou des messages
définis par l'utilisateur en vue d'une analyse ultérieure.
Pour plus d'informations, consultez Messages personnalisés pour la
journalisation (http://msdn.microsoft.com/fr-fr/library/ms345174.aspx )
Spécifiez le niveau de journalisation en effectuant une ou plusieurs des opérations suivantes pour une
instance d'exécution de package.



Définissez les paramètres d'une instance d'exécution de packages à l'aide de la procédure
stockée catalog.set_execution_parameter_value (http://msdn.microsoft.com/fr-fr/library/
ff877990.aspx)
Configurer une instance d'exécution de package à l'aide de la boîte de dialogue Exécuter
le package
Configurer un travail SQL Server Agent pour une exécution de package à l'aide de la boîte de
dialogue Nouvelle étape de travail
Pour définir le niveau de journalisation à l'aide de la boîte de dialogue Exécuter le package
1.
2.
3.
4.
42
Dans SQL Server Management Studio, accédez au package dans l'Explorateur d'objets.
Cliquez avec le bouton droit sur le package, puis sélectionnez Exécuter.
Sélectionnez l'onglet Avancé.
Sous Niveau de journalisation, sélectionnez le niveau de journalisation.
Pour définir le niveau de journalisation à l'aide de la boîte de dialogue Nouvelle étape de travail
1. Créez un travail en développant le nœud SQL Server Agent dans l'Explorateur d'objets,
cliquez avec le bouton droit sur Travaux, puis en cliquez sur Nouveau travail.
ou
Modifiez un travail existant en développant le nœud SQL Server Agent, en cliquant avec le
bouton droit sur un travail existant, puis en cliquant sur Propriétés.
2. Cliquez sur Étapes dans le volet gauche, puis cliquez sur Nouvelle pour ouvrir la boîte de
dialogue Nouvelle étape du travail.
3. Sélectionnez Package SQL Server Integration Services dans la zone de liste Type.
4. Dans l'onglet Package, sélectionnez Catalogue SSIS dans la zone de liste Source du package,
spécifiez le serveur, puis entrez le chemin d'accès au package dans la zone Package.
5. Sous l'onglet Configuration, cliquez sur Avancé, puis sélectionnez un niveau de journalisation
dans la zone de liste Niveau de journalisation.
6. Terminez la configuration de l'étape de travail et enregistrez vos modifications.
Pour définir le niveau de journalisation à l'aide de la procédure stockée catalog.set_execution_parameter_value,
définissez parameter_name à LOGGING_LEVEL et parameter_value à la valeur correspondant à Performance
ou à Commentaires. L'exemple suivant crée une instance d'exécution du package Package.dtsx et définit le
niveau de journalisation à 2. Le package est contenu dans le projet SSISPackages, et le projet se trouve dans
le dossier 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
Afficher les statistiques d'exécution
Les vues de base de données SSISDB et les procédures stockées, ainsi que les rapports standard disponibles
dans SQL Server Management Studio, sont riches en informations sur les exécutions de package et en
informations concernant les exécutions. Une exécution est une instance d'une exécution de package.
Parmi les rapports standard, les rapports Tableau de bord Integration Services, Toutes les exécutions et
Toutes les connexions sont particulièrement utiles pour afficher des informations relatives à l'exécution
de package.
Le rapport Tableau de bord Integration Service fournit les informations suivantes pour les packages en
cours de exécution ou dont l'exécution s'est terminée au cours des dernières 24 heures.
43
Rapport Tableau de bord Integration Services
Section du rapport
Informations sur
l'exécution
Informations sur le
package
Informations de
connexion
Informations
détaillées sur les
packages
Description
Affiche le nombre d'exécutions à différents états (échec, en cours
d'exécution, réussi, autres).
Affiche le nombre total de packages exécutés.
Affiche les connexions utilisées durant les exécutions qui se sont soldées
par un échec.
Pour chaque package, affiche les détails des exécutions achevées. Par
exemple, vous verrez le nombre d'exécutions qui se sont soldées par un
échec par rapport au nombre total d'exécutions, à la durée des exécutions
(en secondes) et à la durée moyenne des exécutions au cours des trois
derniers mois.
Pour afficher des informations d'exécution supplémentaires pour un
package, cliquez sur Performances de l'exécution, Vue d'ensemble et Tous
les messages.
Le rapport Performances de l'exécution illustre la durée des 10 dernières
exécutions réussies et le Temps d’activité et le Temps total des composants
de flux de données du package. Le temps d'activité fait référence au temps
total d'exécution d'un composant au cours de toutes les phases, et le temps
total fait référence au temps total écoulé pour un composant. Le rapport
affiche ces informations pour les composants de flux de données uniquement
lorsque le niveau de journalisation de la dernière exécution du package est
défini sur Performances ou Commentaires.
Le rapport Vue d'ensemble indique l'état des tâches du package. Le rapport
Messages indique les messages d'événements et les messages d'erreur du
package et les tâches, par exemple les heures de début et de fin et le
nombre de lignes écrites.
44
Le rapport Toutes les exécutions fournit les informations suivantes pour les exécutions effectuées sur
l'instance SQL Server connectée. Il peut y avoir plusieurs exécutions du même package. Contrairement
au rapport Tableau de bord Integration Services, vous pouvez configurer le rapport Toutes les
exécutions pour afficher les exécutions qui ont démarré au cours d'une plage de dates. Les dates
peuvent couvrir plusieurs jours, mois ou années.
Rapport Toutes les exécutions
Section du rapport
Filtre
Informations sur l'exécution
Description
Indique le filtre actif appliqué au rapport, tel que la
plage d'heures de début.
Indique l'heure de début, l'heure de fin et la durée
pour chaque exécution du package.
Affichez la liste des valeurs de paramètre utilisées
avec une exécution de package, telles que les
valeurs qui ont été passées à un package enfant
à l'aide de la tâche d'exécution de package. Pour
afficher la liste des paramètres, cliquez sur Vue
d'ensemble.
45
Le rapport Toutes les connexions fournit les informations suivantes pour les connexions qui ont échoué,
pour les exécutions qui se sont produites sur l'instance de SQL Server.
Section du rapport
Filtre
Détails
46
Description
Indique le filtre actif appliqué au rapport, tel que
les connexions avec une chaîne spécifiée et la
plage Heure du dernier échec.
Vous définissez la plage Heure du dernier échec
pour afficher uniquement les échecs de connexion
qui se sont produits pendant une plage de dates.
La plage peut couvrir plusieurs jours, mois ou
années.
Affiche la chaîne de connexion, le nombre
d'exécutions pendant lesquelles une connexion
a échoué et la date du dernier échec de connexion.
En plus d'afficher les rapports standard disponibles dans SQL Server Management Studio, interrogez
également les vues de base de données SSISDB pour obtenir des informations similaires sur les
exécutions de packages. Le tableau suivant décrit les colonnes les vues clés.
Vue de base de données SSISDB
catalog.executable_statistics
(http://msdn.microsoft.com/fr-fr/
library/hh479592.aspx)
Description
Affiche une ligne pour chaque fichier exécutable exécuté,
y compris chaque itération d'un fichier exécutable. Un exécutable
est une tâche ou un conteneur que vous ajoutez au flux de
contrôle d'un package.
Par exemple, la vue affiche la durée d'exécution du fichier
exécutable, les heures auxquelles le fichier exécutable entre
dans les phases de préexécution et de postexécution et le
résultat de l'exécution de l'exécutable, tel que la réussite ou
l'échec.
catalog.executions
(http://msdn.microsoft.com/fr-fr/
library/ff878089.aspx)
Un exemple de résultat d'exécution est le code de retour de la
procédure stockée qui est exécutée par une tâche d'exécution
SQL. Pour plus d'informations, consultez Paramètres et codes
de retour dans la tâche d'exécution SQL.
Affiche des informations sur les exécutions de packages.
Packages exécutés avec la tâche d'exécution du package dans
la même exécution que le package parent.
Par exemple, la vue affiche l'état d'une exécution (en cours
d'exécution, échec, réussite, etc.), la quantité totale de mémoire
physique et la mémoire physique disponible sur le serveur au
démarrage de l'exécution, et la mémoire de pages totale et la
mémoire de pages disponible au démarrage de l'exécution.
catalog.execution_component_phases Affiche les heures de début et de fin des composants de flux de
(http://msdn.microsoft.com/fr-fr/
données, pour chaque phase d'exécution. Ces informations
library/hh230981.aspx)
sont affichées pour plusieurs exécutions de packages.
catalog.event_messages
(http://msdn.microsoft.com/fr-fr/
library/hh479594.aspx)
La vue affiche ces informations pour les composants de flux de
données uniquement lorsque le niveau de journalisation de
l'exécution est défini sur Performances ou sur Commentaires.
Affiche des informations sur les messages qui ont été enregistrés
pendant les opérations, telles que la création et l'exécution
d'un package. La valeur de ce type d'opération est 200.
Par exemple, la vue affiche le texte du message, le composant
de package et le composant de flux de données qui sont la
source du message, et l'événement associé au message,
Les messages affichés par la vue pour une exécution de
package dépendent du niveau de journalisation de l'exécution.
47
Vue de base de données SSISDB
catalog.event_message_context
(http://msdn.microsoft.com/fr-fr/
library/hh479590.aspx)
Description
Affiche des informations sur les conditions associées aux
messages d'événements d'exécution.
Par exemple, la vue affiche l'objet associé au message
d'événement tel qu'une valeur de la variable ou une tâche,
le nom de la propriété et la valeur associées au message
d'événement.
La vue affiche également l'ID de chaque message d'événement.
Vous trouverez des informations supplémentaires sur un
message d'événement spécifique en interrogeant la vue
catalog.event_messages.
Utilisez la vue catalog.execution_component_phases pour calculer le temps d'exécution au cours de
toutes les phases (temps d'activité), et le temps total écoulé (temps total) pour des composants d'un
package. Cela peut permettre d'identifier les composants qui s'exécutent plus lentement que prévu.
Cette vue est remplie si le niveau de journalisation de l'exécution du package est défini sur
Performances ou Commentaires. Pour plus d'informations, consultez Enregistrer les statistiques de
performances dans cet article.
Dans l'exemple suivant, le temps d'activité et le temps total sont calculés pour les composants de
l'exécution avec un ID de 33. Les fonctions sum et DATEDIFF sont utilisées dans le calcul.
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
48
Enfin, utilisez la fonction dm_execution_performance_counters pour obtenir des statistiques sur les
compteurs de performance, telles que le nombre de mémoires tampons utilisées et le nombre de lignes
lues et écrites pour une exécution en cours.
Dans l'exemple suivant, la fonction retourne des statistiques pour une exécution en cours ayant l'ID 34.
select * from [catalog].[dm_execution_performance_counters] (34)
Dans l'exemple suivant, la fonction retourne des statistiques pour toutes les exécutions en cours.
select * from [catalog].[dm_execution_performance_counters] (NULL)
Analyser le flux de données
Integration Services comprend des fonctionnalités et des outils que vous pouvez utiliser pour dépanner
le flux de données dans un package pendant une exécution.
Drainage du flux de données pendant l'exécution
SQL Server Integration Services (SSIS) dans SQL Server 2012 introduit une nouvelle fonctionnalité qui
vous permet d'ajouter un drainage de données sur un chemin d'accès de flux de données d'un package
au moment de l'exécution et de diriger la sortie de la collecte de données dans un fichier externe. Pour
utiliser cette fonctionnalité, vous devez déployer votre projet SSIS à l'aide du modèle de déploiement de
projet sur un serveur SSIS. Après avoir déployé le package sur le serveur, vous devez exécuter le script
T-SQL sur la base de données SSISDB pour ajouter des drainages de données avant d'exécuter le
package. Voici un exemple de scénario :
49
1. Créez une instance d'exécution de packages à l'aide de la procédure stockée
catalog.create_execution (http://msdn.microsoft.com/fr-fr/library/ff878034).
2. Ajoutez un drainage de données à l'aide de la procédure stockée catalog.add_data_tap
(http://msdn.microsoft.com/fr-fr/library/hh230989) ou catalog.add_data_tap_by_guid
(http://msdn.microsoft.com/fr-fr/library/hh230991)
3. Démarrez l'instance d'exécution du package à l'aide de la procédure stockée
catalog.start_execution (http://msdn.microsoft.com/fr-fr/library/ff878160).
Voici un exemple de script SQL qui exécute les étapes décrites dans le scénario ci-dessus :
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
Les paramètres nom du dossier, nom du projet et nom du package de la procédure stockée
create_execution correspondent aux noms de dossier, de projet et de package dans le catalogue
Integration Services. Obtenez les noms de dossier, de projet et de package à utiliser dans l'appel de
create_execution dans SQL Server Management Studio comme le montre l'image suivante. Si vous ne
voyez pas le projet SSIS ici, vous n'avez peut-être pas encore déployé le projet sur le serveur SSIS.
Cliquez avec le bouton droit sur le projet SSIS dans Visual Studio et cliquez sur Déployer pour déployer le
projet sur le serveur SSIS prévu.
50
Au lieu de taper les instructions SQL, générez le script d'exécution de package comme suit :
1. Cliquez avec le bouton droit sur Package.dtsx, puis cliquez sur Exécuter.
2. Cliquez sur le bouton de la barre d'outils Script pour générer le script.
3. Maintenant, ajoutez l'instruction add_data_tap avant l'appel de start_execution.
Le paramètre task_package_path de la procédure stockée add_data_tap correspond à la propriété
PackagePath de la tâche de flux de données dans Visual Studio. Dans Visual Studio, cliquez avec le
bouton droit sur la tâche de flux de données, puis cliquez sur Propriétés pour ouvrir la fenêtre
Propriétés. Notez la valeur de la propriété PackagePath pour l'utiliser comme valeur du paramètre
task_package_path pour l'appel de procédure stockée add_data_tap.
Le paramètre dataflow_path_id_string de la procédure stockée add_data_tap correspond à la propriété
IdentificationString du chemin d'accès de flux de données auquel vous voulez ajouter un drainage de
données. Pour obtenir dataflow_path_id_string, cliquez sur le chemin d'accès de flux de données et
notez la valeur de la propriété IdentificationString dans la fenêtre Propriétés.
51
Lorsque vous exécutez le script, le fichier de sortie est stocké dans le dossier <Program Files>\Microsoft
SQL Server\110\DTS\DataDumps. Si un fichier portant le même nom existe déjà, un nouveau fichier avec
un suffixe (par exemple : output[1].txt) est créé.
Comme indiqué précédemment, utilisez également la procédure stockée catalog.add_data_tap_by_guid
(http://msdn.microsoft.com/fr-fr/library/hh230991) plutôt que la procédure stockée add_data_tap.
Cette procédure stockée accepte l'ID de la tâche de flux de données comme paramètre au lieu de
task_package_path. Vous pouvez obtenir l'ID de la tâche de flux de données dans la fenêtre Propriétés
de Visual Studio.
Suppression d'un drainage de données
Pour supprimer un drainage de données avant de lancer l'exécution, utilisez la procédure stockée
catalog.remove_add_data_tap. Cette procédure stockée accepte l'ID de drainage de données comme
paramètre, que vous pouvez obtenir en tant que résultat de la procédure stockée 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
52
Création de la liste de tous les drainages de données
Vous pouvez également afficher tous les drainages de données à l'aide de la vue
catalog.execution_data_taps. L'exemple suivant permet d'extraire des drainages de données pour une
instance de spécification d'exécution de spécification (ID : 54).
select * from [SSISDB].[catalog].execution_data_taps where
execution_id=@execid
Considérations relatives aux performances
Le fait d'activer le niveau de journalisation Commentaires et d'ajouter des drainages de données
augmente les opérations d'E/S effectuées par votre solution d'intégration de données. Par conséquent,
il est recommandé d'ajouter des drainages de données uniquement à des fins de dépannage.
Analyse du flux de données pendant l'exécution
Utilisez la vue de base de données SSISDB catalog.execution_data_statistics (http://msdn.microsoft.com/
fr-fr/library/hh230986.aspx) pour analyser le flux de données des packages. Cette vue affiche une ligne
chaque fois qu'un composant de flux de données envoie des données à un composant en aval. Les
informations peuvent être utilisées pour mieux comprendre les lignes envoyées à chaque composant.
Remarque : Le niveau de journalisation doit avoir la valeur Commentaires afin de capturer des
informations avec la vue catalog.execution_data_statistics.
L'exemple suivant affiche le nombre de lignes transmises entre les composants d'un package.
Execution_id est l'ID d'une instance d'exécution que vous pouvez obtenir en tant que valeur de retour
de la procédure stockée create_execution ou de la vue 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
53
L'exemple suivant calcule le nombre de lignes par milliseconde envoyées par chaque composant pour
une exécution spécifique. Les valeurs calculées sont les suivantes :



total_rows - Somme de toutes les lignes envoyées par le composant
wall_clock_time_ms – Durée d'exécution écoulée totale, en millisecondes, pour chaque
composant
num_rows_per_millisecond – Nombre de lignes par milliseconde envoyées par chaque
composant
La clause HAVING est utilisée pour éviter une erreur de division par zéro dans les calculs.
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
Conclusion
SQL Server Integration Services (SSIS) peut être utilisé efficacement comme outil pour déplacer des
données dans et à partir de la Base de données SQL Windows Azure, dans le cadre de la solution
d'extraction, de transformation et de chargement (ETL, Extract, Transform and Load) et de la solution
de déplacement des données. SSIS peut être utilisé efficacement pour déplacer des données entre
les sources et les destinations dans le cloud, et dans un scénario hybride entre le cloud et le site.
Ce document a présenté les meilleures pratiques SSIS pour les sources et les destinations du cloud,
a abordé la planification de projets SSIS si le projet se trouve entièrement dans le cloud ou comporte
des déplacements de données hybrides et vous a guidé tout au long d'un exemple d'optimisation des
performances sur un déplacement hybride en mettant à l'échelle le déplacement des données.
54
Pour plus d'informations :
http://www.microsoft.com/sqlserver/ : Site Web SQL Server
http://technet.microsoft.com/fr-fr/sqlserver/ : TechCenter SQL Server
http://msdn.microsoft.com/fr-fr/sqlserver/ : Centre de développement SQL Server
Avez-vous trouvé ce document utile ? Nous apprécions vos commentaires. Sur une échelle
de 1 (faible) à 5 (excellent), quelle note donneriez-vous à ce document ? Expliquez pourquoi.
Par exemple :


Avez-vous attribué une bonne note car le document fournit de bons exemples, contient
des captures d'écran très utiles, est clairement rédigé, ou pour d'autres raisons ?
Avez-vous attribué une mauvaise note car le document fournit de mauvais exemples,
contient des captures d'écran pas claires ou est mal rédigé ?
Vos commentaires nous aident à améliorer la qualité des livres blancs que nous publions.
Envoyez vos commentaires.
55
Téléchargement