Gestion et déploiement de SQL Server Integration Services : Microsoft TechNet SQL Server TechCenter

Paru le 30 avril 2005

Par Michael Otey, TECA, Inc et Denielle Otey, TECA, Inc

SQL Server Integration Services (SSIS) est une plate-forme d’intégration de données au niveau entreprise. SSIS est un système totalement nouveau dans SQL Server 2005. Ce document explique comment tirer parti des fonctionnalités de gestion et de déploiement de SSIS.

Sur cette page

Introduction
Gestion du service SSIS
Gestion des packages
Déploiement de packages SSIS
Résumé
Ressources supplémentaires (en anglais)

Introduction

Dans les entreprises actuelles, le processus de prise de décision et les opérations quotidiennes dépendent souvent en grande partie de données stockées sur différents systèmes de stockage, dans différents formats en dans différents emplacements. Afin que ces données soient converties en informations métier utiles, elles doivent généralement être combinées, nettoyées, normalisées et synthétisées. Par exemple, il peut être nécessaire de convertir les informations dans un type de données différent, ou les serveurs de base de données hétérogènes peuvent stocker les données nécessaires avec différents schémas. De telles différences doivent être résolues avant le chargement des données dans un système cible. Par le passé, la tâche d’extraction et de transformation des données était principalement affectée à l'administrateur de base de données. Cependant, pour pouvoir accéder plus rapidement aux données essentielles, il a fallu que d'autres membres du personnel puissent effectuer ces opérations. Ce document présente aux administrateurs et développeurs de base de données, aux responsables informatiques et autres professionnels de l'informatique les fonctionnalités de gestion et de déploiement de Microsoft® SQL Server™ 2005 Integration Services (SSIS).

Microsoft SQL Server 2005 Integration Services (SSIS) est une plate-forme complète d'intégration de données que vous pouvez utiliser pour transférer, extraire, transformer et consolider les informations de sources hétérogènes et les charger dans différents systèmes. Il est important de comprendre que SSIS n’est pas simplement un outil d’extraction, de transformation et de chargement (ETL). SSIS est une plate-forme complète d’intégration de données, offrant un certain nombre d’outils graphiques de développement et de gestion, de services, d’objets programmables et d’interfaces API (Application Programming Interfaces). SSIS contient un moteur de workflow prenant en charge une logique complexe et peut être utilisé pour un large éventail d’opérations de maintenance de base de données et d’opérations sophistiquées de transfert de données. Vous en trouverez un bon exemple dans l’Assistant Plan de maintenance de SQL Server 2005. En coulisses, l’Assistant Plan de maintenance utilise des projets SSIS afin d’effectuer toutes les opérations de maintenance de base de données prises en charge par l’assistant.

Dans ce document, vous apprendrez à gérer et à déployer des projets SSIS. Dans la première section de ce document, vous découvrirez une présentation de l’architecture SSIS. Ensuite, vous découvrirez les nouveaux outils de SQL Server 2005 pour la gestion de SQL Server Integration Services. Ce document aborde ensuite plus en détail certaines méthodes recommandées pour la création, le déploiement et la gestion des packages SSIS.

Brève présentation d’Integration Services

Dans les versions antérieures de SQL Server, DTS (Data Transformation Services) était le principal outil ETL de Microsoft. Bien que DTS était un outil extrêmement utile, il présentait certaines limites en termes d’évolutivité et de simplicité de déploiement de packages sur différents systèmes SQL Server. SSIS est un système totalement nouveau. Comme DTS, SSIS inclut des outils graphiques et des objets programmables, ce qui facilite l’utilisation de l’outil par les employés de l’entreprise. En revanche, SSIS a adopté une nouvelle voie en fractionnant le flux de contrôle et le flux de données des packages en composants distincts, ce qui permet la création de packages beaucoup plus complexes et robustes.

La nouvelle architecture SSIS est essentiellement composée de deux parties : le moteur d’exécution de transformation de données, qui gère le flux de contrôle d’un package, et le moteur de flux de données ou moteur de pipeline de transformation de données, qui gère le flux des données à partir des sources de données, via les transformations et jusqu’aux cibles de destination. Vous pouvez voir une présentation de l’architecture SSIS dans la figure 1.

Figure 1 : Présentation de SQL Server Integration Services

Le concepteur SSIS est un ensemble d’outils graphiques vous permettant de créer et de gérer les packages SSIS en faisant glisser les objets dans l’interface utilisateur. Les assistants sont des outils graphiques SSIS qui vous guident dans l’importation de données ou la création de packages simples. SSIS offre également des outils de ligne de commande pour la création et la gestion de packages. Ces outils sont étudiés plus en détail dans la section suivante.

Le moteur d’exécution de transformation de données offre des services aux packages et à leur contenu. Par exemple, il gère le stockage des packages, leur exécution, la journalisation, le débogage, la gestion des événements, le déploiement des packages, ainsi que la gestion des variables, transactions et connexions.

Le package est le composant principal de SSIS. Les packages SSIS sont l’unité de base du déploiement et de l’exécution. Vous pouvez créer des packages à l’aide des outils graphiques de SSIS, ou vous pouvez les créer par programmation. Un package est constitué d’un ensemble d’éléments tels que des flux de contrôle et des flux de données, des connexions, des variables et des gestionnaires d’événements. Vous pouvez configurer les propriétés de vos packages afin d’incorporer des transactions ou d’implémenter la sécurité, et ces objets de configuration sont également stockés dans le package.

Lorsque vous créez un package, vous définissez trois éléments importants : le flux de contrôle, le flux de données et les contraintes de priorité qui relient les différentes tâches d’un package. Vous pouvez voir la relation entre les éléments du package SSIS dans la figure 2.

Figure 2 : éléments du package SSIS

Le flux de contrôle du package définit les actions exécutées lors de l’exécution du package. SSIS offre trois types d’objets de flux de contrôle : conteneurs, tâches et contraintes de priorité.

• Les conteneurs fournissent la structure de vos packages. Ils regroupement les tâches et autres conteneurs en unités de travail logique pertinentes.

• Les tâches effectuent un large éventail de fonctions dans vos packages. Les packages SSIS contiennent deux types de tâches : les tâches de flux de contrôle et les tâches de flux de données. Les tâches de flux de contrôle effectuent un large éventail d’actions de workflow. Par exemple, la tâche d'exécution de requêtes SQL exécute les instructions SQL sur la plate-forme de la base de données cible, la tâche système de fichiers peut lire et écrire dans le système de fichiers du serveur hôte, la tâche FTP (File Transfer Protocol) utilise le protocole FTP pour transférer des fichiers vers des systèmes distants, et la tâche d’envoi de courrier utilise le protocole SMTP pour envoyer des messages électroniques. En outre, les tâches de plan de mainntenance effectuent un ensemble d’opérations de maintenance de la base de données. Les tâches de plan de maintenance incluent la tâche de sauvegarde de la base de données, la tâche de nettoyage de l’historique, la tâche de recréation d’index et la tâche de réduction de la base de données. Les tâches les plus importantes et spécialisées sont les tâches de flux de données qui déplacent des données entre différentes sources de données. Avec une tâche de flux de données, vous pouvez définir une source et une destination cible, par exemple un fichier plat, Excel, OLE DB ou SQL Server.

• Les contraintes de priorité relient les éléments de votre package en un flux logique et spécifient les conditions dans lesquelles les éléments sont exécutés. Les trois contraintes de priorité par défaut vous permettent de contrôler le flux de contrôle d’exécution des packages en fonction de l’exécution, de la réussite ou de l’échec d’une tâche. Par exemple, vous pouvez créer une contrainte de priorité qui lie une tâche d’exécution SQL et une tâche de flux de données, dans laquelle le package n’exécutera la tâche de flux de données qu'en cas de réussite de la tâche d'exécution SQL. SSIS prend en charge la création de contraintes de priorité en fonction d’un opérateur d’évaluation ou des résultats de l’exécution d’une tâche. Voici un exemple de contrainte de priorité basée sur un opérateur d’évaluation : utilisation du contenu d’une variable ou de l’évaluation d’une expression pour déterminer le flux vers la tâche suivante.

Les tâches du flux de données, qui utilisent le moteur de pipeline de transformation de données, gèrent le flux des données et les transformations des adaptateurs de source de données vers les adaptateurs de destination des données. Integration Services offre trois types d’éléments de flux de données : adaptateurs source, transformations et adaptateurs de destination. Les adaptateurs source extraient les données de sources telles que des tables, des vues et des fichiers, les transformations modifient et synthétisent les données, et les adaptateurs de destination chargent les données dans les banques de données cible.

Outils de développement et de déploiement SSIS

Les nouveaux outils de productivité et de gestion de SSIS sont constitués d’environnements studio afin de vous offrir des outils graphiques pour la conception, la création, le débogage et la gestion de vos solutions de transformations de données. SSIS inclut également des utilitaires de ligne de commande qui vous permettent d’exécuter et de gérer vos packages SSIS.

Assistants SSIS

L’Assistant d’importation et d’exportation est la méthode la plus simple pour créer des packages SSIS. Il peut être démarré à partir des emplacements suivants :

• Business Intelligence Development Studio Ouvrez l’explorateur de solutions, cliquez avec le bouton droit de la souris sur le noeud Package, puis sélectionnez l'option Importer et exporter.

• SQL Server Management Studio. Ouvrez le noeud SQL Server, cliquez avec le bouton droit de la souris sur une base de données, puis sélectionnez l’option Importer et exporter.

• Exécutez DTSWizard.exe. DTSWizard.exe est un utilitaire de ligne de commande qui démarre l’Assistant Importation et exportation.

L’Assistant Importation et exportation

L’Assistant Importation et exportation vous guide dans le processus de copie de données à partir d'un large éventail de sources, notamment SQL Server, les fichiers plats, Microsoft Access (.mdb), Microsoft Excel (.xls) et d’autres fournisseurs OLE DB. Grâce à l’Assistant Importation et exportation, le package est créé avec une quantité minimale d’outils de transformation. Vous pouvez l’enregistrer vers le système de fichiers en tant que fichier DTSX, puis l’ouvrir et le modifier dans le Concepteur, afin d’ajouter des tâches plus complexes et un workflow de package.

L’Assistant Configuration

L’Assistant Configuration vous guide dans la création de configurations pouvant être déployées avec vos packages. L’Assistant Configuration est démarré uniquement via le Concepteur SSIS.

Pour démarrer l’Assistant Configuration :

1. Dans le menu SSIS, sélectionnez Configuration du package.

2. Sélectionnez Activer PackageConfiguration.

3. Sélectionnez l'option Ajouter.

L’Assistant Configuration vous permet de créer des configurations SSIS qui modifient de manière dynamique les variables et les propriétés des objets lors de l’exécution, ce qui rend vos packages plus flexibles et plus faciles à déplacer de votre environnement de développement vers des environnements de production. Par exemple, l’utilisation de configurations de package vous permet de transmettre des paramètres tels que le nom du système et les informations de connexion à vos packages lors de l’exécution, afin de permettre leur portabilité sur plusieurs serveurs.

L’Assistant Installation de package.

L’Assistant Installation de package est exécuté pour installer des packages sur le système de fichiers ou sur une base de données SQL Server. Vous pouvez déployer des packages en créant un utilitaire de déploiement à l’aide de Business Intelligence (BI) Development Studio pour votre SSIS contenant le package que vous souhaitez déployer. Vous pouvez ensuite exécuter l’Assistant Installation de package sur le système de destination, lequel vous guide dans l’installation de votre package et la modification d’objets de configuration.

L’Assistant Installation de package est démarré via l’exécution de l’utilitaire de ligne de commande DTSInstall.exe. Cet utilitaire est inclus lorsque vous créez un package à l’aide du concepteur SSIS.

L’Assistant Migration

L’Assistant Migration vous aide à migrer vos packages DTS SQL Server 2000 existants vers SQL Server 2005. L’Assistant Migration peut convertir automatiquement la plupart des packages DTS SQL Server 2000 existants qui utilisent les tâches et transformations standard, mais ne peut pas convertir les packages DTS qui utilisent des tâches et transformations personnalisées. Pour démarrer l’Assistant Migration, dans BI Development Studio, sélectionnez Projet, puis sélectionnez Migrer package DTS 2000.

Outil de déploiement SSIS	Site
Assistant Importation et exportation	Menu Démarrer | Tous les programmes | Integration Services
Assistant Configuration	SSIS Designer | Package Designer | Configuration de package SSIS | Activer la configuration du package | Ajouter
Installeur de package	DTSInstall.exe
Assistant Migration	BI Development Studio | Projet | Migrer le package DTS 2000

Tableau 1 : outils SSIS

SSIS Designer

SSIS Designer est un ensemble d’outils graphiques qui vous permettent de créer, d’exécuter et de déboguer des packages SSIS. Il est inclus dans Business Intelligence Development Studio. Pour démarrer SSIS Designer :

1. Ouvrez BI Development Studio.

2. Sélectionnez Fichier, puis l’option Nouveau projet afin d’afficher la boîte de dialogue Nouveau projet BI Development Studio.

3. Sélectionnez Projet Integration Service afin d’afficher SSIS Designer.

SSIS Designer offre une surface de développement par glisser-déplacer qui vous permet de créer des solutions robustes et complexes de transformation de données, contenant de multiples connexions à des sources de données hétérogènes, des workflows complexes, des transformations et une logique pilotée par les événements. SSIS Designer est un tout nouvel outil avec lequel même les utilisateurs DTS expérimentés devront se familiariser. Un moyen rapide de découvrir SSIS Designer consiste à l'utiliser pour ouvrir et modifier des packages simples créés à l’aide de l’Assistant Importation et exportation de SSIS. SSIS Designer est illustré figure 3.

Figure 3 : SSIS Designer

Lorsque vous créez une nouvelle solution SSIS dans BI Development Studio, un package vide est automatiquement créé et ajouté au projet. L’interface graphique de SSIS Designer affiche des fenêtres distinctes vous permettant de créer des flux de contrôle, des flux de données et des gestionnaires d’événements pour votre package. SSIS Designer vous permet d'ajouter des fonctions telles que la journalisation, l’implémentation de points de contrôle pour le redémarrage de packages, l’intégration de transactions afin de gérer l’annulation, la mise en correspondance de variables, ainsi que la définition de points d’arrêt pour le débogage. SSIS Designer inclut également un volet Explorateur de package offrant une vue hiérarchique des éléments du package.

SQL Server Management Studio

SQL Server Management Studio remplace Enterprise Manager dans SQL Server 2000. Le nouvel outil SQL Server Management Studio effectue toutes les fonctions qui étaient effectuées par SQL Server Enterprise Manager. En outre, il vous permet d’afficher et de gérer de manière graphique l’exécution de vos packages. Management Studio affiche une vue sous forme de dossiers des packages stockés dans la base de données SQL Server ou le système de fichiers. Cette vue peut être personnalisée, ce qui vous permet d’ajouter, de supprimer ou de renommer vos propres dossiers. Une fonctionnalité Importation et exportation vous permet de copier des packages d’un format de stockage vers un autre. Pour plus d’informations, reportez-vous à la section Gestion de packages SSIS avec SQL Server Management Studio, plus loin dans ce document.

SQL Server 2005 inclut un nouveau service SSIS, un service Microsoft Windows® qui vous permet d’utiliser SQL Server Management Studio pour gérer les packages SSIS et surveiller les packages en cours d’exécution.

Utilitaire de gestion des packages SSIS (dtutil)

SSIS contient l’utilitaire de gestion des packages (dtutil), que vous pouvez exécuter à partir d’une ligne de commande. L'invite de commande dtutil vous permet de gérer les packages SSIS stockés dans la base de données ou le système de fichiers. Vous pouvez choisir de copier, déplacer, supprimer ou vérifier l’existence d’un package à l’aide des options appropriées de l’invite de commande.

Utilitaires d’exécution de packages SSIS (dtexec & dtexecui)

Outre l’utilitaire de gestion des packages, deux utilitaires d’exécution de packages sont fournis avec SSIS : dtexec et dtexecui.

Vous pouvez exécuter l’utilitaire dtexec à partir de la ligne de commande à l’aide de l’option d’invite de commande appropriée. Vous pouvez utiliser l’outil dtexec afin de définir toutes les fonctionnalités de configuration et d'exécution de vos packages, notamment les connexions, les propriétés, les variables et la journalisation. Le nouvel outil dtexec est le principal outil permettant d'exécuter vos packages SSIS à partir de la ligne de commande ou de vos propres scripts de shell de commande.

L'utilitaire dtexecui affiche une interface graphique qui vous permet de charger et d’exécuter des packages. Vous pouvez utiliser cette interface pour définir de manière interactive la configuration du package et les attributs d’exécution avant d’exécuter le package. L’utilitaire dtexecui offre une fonctionnalité très pratique en vous permettant de créer des commandes d’exécution complètes pour dtexec, avec les paramètres requis. Vous pouvez ensuite copier et coller ces commandes dans vos propres fichiers batch d’exécution de packages SSIS.

Outil de ligne de commande SSIS	Description
Dtutil	Gestion des packages SSIS
Dtexec	Exécution des packages SSIS
dtexecui	Affiche une interface graphique pour charger et exécuter des packages SSIS
Dtswizard	Lancement de l’Assistant Importation/exportation

Tableau 2 : Outils de ligne de commande SSIS

Flux de développement

Microsoft a facilité l’utilisation et la compréhension de l’extraction, de la manipulation et du chargement de données avec la plate-forme de solution de données SSIS. Commencez par concevoir un package SSIS qui gère à la fois le flux de contrôle et le flux de données. Vous pouvez concevoir des packages en utilisant l’outil graphique BI Development Studio, ou utiliser l’Assistant Importation et exportation afin de créer rapidement des packages simples. Une fois les packages créés, vous pouvez les stocker dans le système de fichiers ou dans la base de données SQL Server. L’utilitaire de ligne de commande dtutil permet de déplacer des packages entre des systèmes SQL Server et de la base de données msdb vers le système de fichiers. Vous pouvez ensuite exécuter un package à l’aide du planificateur de tâches SQL Agent ou à l’aide des utilitaires dtexec ou dtexecui. Vous pouvez surveiller et gérer vos packages à l’aide de SQL Server Management Studio. Reportez-vous à la figure 4 pour une illustration du flux de ces packages.

Figure 4 : Flux de développement SSIS

Gestion du service SSIS

Vous pouvez surveiller l’exécution des packages SSIS à l’aide de SQL Server Management Studio. SQL Server Management Studio inclut un nouveau noeud SSIS Server qui répertorie les packages SSIS enregistrés et en cours d’exécution. Le noeud de gestion SSIS apparaît uniquement après le démarrage du service SSIS. Le service SSIS est installé lorsque vous sélectionnez l’option d’installation de SQL Server Integration Services et son rôle est de permettre la gestion des packages SSIS. Le service SSIS est normalement démarré par défaut lors de l’installation de SSIS sur le système. S’il n’est pas démarré, vous pouvez utiliser la procédure suivante pour le démarrer manuellement.

Pour démarrer manuellement le service SSIS :

1. Dans le menu Démarrer, cliquez sur Tous les programmes.

2. Sélectionnez Microsoft SQL Server 2005, puis sélectionnez Gestionnaire d’ordinateurs SQL.

3. Faites défiler jusqu’à la section Services et applications et développez Gestionnaire d’ordinateurs SQL. Développez ensuite le noeud Services SQL Server 2005.

4. Sélectionnez SSIS Server.

5. Cliquez avec le bouton droit de la souris sur l’entrée du service dans le volet de droite et sélectionnez Démarrer dans le menu contextuel afin de démarrer le service, comme illustré figure 5.

Figure 5 : démarrage du serveur SSIS

Si vous souhaitez que le service SSIS s’exécute en permanence, vous pouvez remplacer le type de démarrage par Automatique. Cela permet de démarrer automatiquement le service SSIS à chaque démarrage du serveur.

Il est important de comprendre que le service SSIS est conçu pour permettre la surveillance des packages SSIS ; il n’est pas nécessaire qu’il soit en cours d’exécution pour exécuter un package. De la même façon, l’arrêt du service SSIS ne vous empêche pas d’exécuter les packages SSIS. En revanche, si le service SSIS est en cours d’exécution, SSIS Designer peut l’utiliser pour mettre en cache les objets utilisés dans le concepteur, ce qui permet d’améliorer les performances de ce dernier.

Gestion des packages SSIS avec SQL Server Management Studio

Une fois le service SSIS démarré, vous pouvez l’utiliser pour surveiller l’exécution des packages SSIS dans SQL Server Management Studio. L’un des principaux avantages du service SSIS est le fait qu’il vous permet de surveiller les packages qui s’exécutent sur le serveur SQL Server local, ainsi que sur les systèmes SQL Server distants enregistrés dans SQL Server Management Studio. Il est important de noter que, si SQL Server Management Studio vous permet de gérer les packages SSIS existants, il ne vous permet pas de les créer. Les packages sont créés à l’aide de BI Development Studio, de l’Assistant Importation et exportation, ou par programmation à l’aide des API SSIS.

Pour gérer les packages SSIS à l’aide de SQL Server Management Studio :

• Ouvrez SQL Server Management Studio.

• Dans la boîte de dialogue Se connecter à un serveur, sélectionnez Integration Services dans la liste Type de serveur.

• Indiquez le nom du serveur SQL Server à l’invite Nom du serveur et fournissez vos informations d’authentification.

SQL Server Management Studio s’ouvre et l’explorateur d’objets affiche les informations de gestion de SSIS (figure 6).

Figure 6 : Gestion des packages SSIS avec SQL Server Management Studio

Par défaut, le noeud serveur Integration Services présente deux dossiers pour l’utilisation de packages SSIS : le dossier Packages en cours d’exécution et le dossier Packages stockés. Le dossier Packages en cours d’exécution affiche les packages SSIS actuellement en cours d’exécution sur le serveur local. Bien entendu, le contenu de ce dossier change en permanence afin de refléter l’activité actuelle du système. Le contenu du dossier Packages en cours d’exécution doit être actualisé manuellement afin que l'affichage soit mis à jour avec les packages en cours d'exécution.

Le dossier Packages stockés répertorie les packages SSIS enregistrés sur le serveur local. Par défaut, ce dossier contient deux sous-dossiers : le dossier Système de fichiers et le dossier MSDB. Le dossier Système de fichiers répertorie les packages SSIS enregistrés dans le système de fichiers, tandis que le dossier MSDB répertorie les packages stockés dans la table sysdtspackages90 de la base de données msdb. Il est important de noter que le serveur SSIS ne connaît pas les packages stockés dans le système de fichiers tant que ces packages n’ont pas été importés dans le dossier Système de fichiers du service SSIS. En plus de répertorier les packages SSIS enregistrés, SQL Server Management Studio vous permet également de les utiliser. Un clic avec le bouton droit de la souris sur un package affiche un menu contextuel qui vous permet d’effectuer un certain nombre de tâches, telles que :

• Nouveau dossier. Crée un nouveau dossier dans l’explorateur d’objets pour l’affichage des packages enregistrés dans le système de fichiers ou dans la table sysdtapackages90.

• Importer le package. Importe le package du système de fichiers vers la base de données msdb.

• Exporter le package. Exporte le package de la base de données msdb vers le système de fichiers.

• Exécuter le package. Exécute le package avec dtexecui.

• Supprimer. Supprime le package.

• Renommer. Renomme le package.

Bien que SQL Server Management Studio soit installé avec les emplacements de dossiers par défaut de MSDB et Système de fichiers, vous pouvez facilement ajouter des dossiers à cette structure à l’aide de l’option Créer nouveau dossier. Lorsque vous créez un dossier sous le dossier système Système de fichiers, un nouveau répertoire est créé dans le système de fichiers. Par défaut, ces répertoires se trouvent dans le répertoire c:\Program Files\SQL Server\90\Packages. L’importation de packages vers un dossier Système de fichiers entraîne la copie du package vers le répertoire portant le même nom dans le système de fichiers. Pour les dossiers créés sous le dossier MSDB, une nouvelle entrée est ajoutée à la table sysdtspackackefolder90 qui effectue le suivi de la structure des dossiers. Il est toutefois important de comprendre que les packages proprement dits sont toujours stockés dans la table msdb sysdtspackaes90. L’option Dossiers de SQL Server Management Studio vous donne un moyen d’appliquer une structure à vos packages, ce qui vous permet de regrouper les packages semblables.

Modification des dossiers par défaut des packages SSIS

Les deux dossiers par défaut fournis par SQL Server Management Studio, à savoir les dossiers Système de fichiers et MSDB, peuvent eux-mêmes être configurés. Les définitions de ces dossiers sont stockées dans le fichier XML lu par le service SSIS au démarrage. Le service SSIS extrait l’emplacement de ce fichier à partir de l’emplacement suivant du registre : HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile.

Pour personnaliser les dossiers de démarrage SSIS, vous pouvez créer un nouveau fichier XML au format requis, puis pointer le service SSIS vers ce fichier en mettant à jour la clé de registre ServiceConfigFile. Voici un exemple de fichier de configuration du service SSIS :

<?xml version="1.0" encoding="UTF-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="FileSystemFolder">
<Name>Fsystem__SQL2005-SSIS</Name>
<StorePath>C:\_work\VisualStudioProjects\DTS</StorePath>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>Fsystem__SQL2005-SSIS MSN Money Projects</Name><StorePath>
C:\_MoneyChartingRebuild\MoneyChartingRebuild2</StorePath>
</Folder>

<Folder xsi:type="FileSystemFolder">
<Name>Fsystem__SQL2005-SSISdts01 test packages</Name>
<StorePath>\\SQL2005-SSISdts01\c$\_work\testPackages</StorePath>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>SQL__SQL2005-SSIS</Name>
<ServerName>SQL2005-SSIS</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>SQL__SQL2005-SSISdts01</Name>
<ServerName>SQL2005-SSISdts01</ServerName>
</Folder>

</TopLevelFolders>
</DtsServiceConfiguration>

La figure 7 illustre les résultats de l’utilisation de ce fichier de configuration du service SSIS.

Figure 7 : Personnalisation des dossiers du service SSIS

Un moyen d’utiliser les possibilités de configuration du service SSIS consiste à créer une structure de dossiers de gestion commune pour plusieurs serveurs. Pour cela, vous pouvez stocker le fichier de configuration du service dans un partage de fichiers central, puis pointer plusieurs serveurs vers le fichier de configuration partagé. Cela permet à tous les serveurs de présenter la même structure de dossiers SSIS.

Gestion des packages DTS 2000 avec SQL Server Management Studio

Outre la gestion des packages SSIS natifs, SQL Server Management Studio peut également gérer les packages DTS créés dans SQL Server 2000 et stockés dans la table dtspackages de la base de données msdb. Pour gérer les packages DTS SQL Server 2000 hérités avec SQL Server Management Studio, ouvrez l’explorateur d’objets à l’aide du type de serveur Serveur de base de données dans la fenêtre Se connecter au serveur. L’explorateur d’objets affiche un noeud Packages DTS 2000 semblable à celui illustré figure 8.

Figure 8 : Gestion des packages DTS 2000 avec SQL Server Management Studio

Le dossier Packages DTS 2000 répertorie les packages SQL Server 2000 présents dans la table sysdtspackages de la base de données msdb. Vous pouvez gérer DTS en cliquant avec le bouton droit de la souris sur un package qui affiche un menu contextuel que vous pouvez utiliser pour effectuer un certain nombre de tâches, telles que :

• Ouvrir. Démarre SQL Server 2000 DTS Designer.

• Migrer un package. Ouvre l’Assistant Migration afin de migrer le package DTS vers un package SSIS.

• Exporter. Exporte le package de la base de données msdb vers le système de fichiers.

• Supprimer. Supprime le package de la base de données msdb.

• Renommer. Renomme le package.

Il est important de noter que pour utiliser l’option Ouvrir, SQL Server 2000 DTS Designer doit être installé sur le système SQL Server 2005. SQL Server 2000 DTS Designer est présent si une installation SQL Server existante a été mise à niveau vers SQL Server 2005 ou si l’outil de gestion de SQL Server 2000 a été installé sur le système SQL Server 2005. Vous trouverez davantage d’informations sur l’utilisation des packages DTS dans la section Migration de packages DTS SQL Server 2000 de ce document.

Gestion des packages

L’association de SQL Server Management Studio et du service SSIS vous permet de gérer l’exécution des packages SSIS du point de vue du système. Cependant, en tirant parti de certaines de nouvelles fonctionnalités de SSIS, vous pouvez intégrer à vos packages SSIS un niveau de gestion et de robustesse beaucoup plus granulaire.

Redémarrage de point de contrôle

La récupération suite à la défaillance d’un package était l’une des limites de DTS dans SQL Server 2000. Si l'exécution d'un package échoue, le package entier doit être réexécuté à partir du début. Cette tâche peut prendre beaucoup de temps pour les packages complexes ou les packages qui effectuent des transferts de données très volumineux. Dans SQL Server 2005, la prise en charge des points de contrôle par SSIS répond à cette limitation de DTS. Lorsqu'un package SSIS est configuré pour utiliser des points de contrôle, les informations sur l’exécution et l'état des packages sont écrites dans un fichier de point de contrôle. Si un package utilisant des points de contrôle échoue, il n’est pas nécessaire de réexécuter le package depuis le début. Le moteur d’exécution de transformation de données peut traiter le fichier de point de contrôle et le package peut être redémarré à partir du conteneur en cours de traitement lors de l’échec. Le redémarrage des points de contrôle permet de simplifier de manière significative la récupération des packages contenant des opérations complexes et peut permettre un gain de temps significatif pour les packages contenant des tâches longues, car le package n’a pas besoin de retraiter toutes les tâches avant le point de contrôle.

Il est important de comprendre que les points de contrôle s’appliquent au flux de contrôle du package et non au flux de données du package. Les conteneurs de flux de contrôle sont l’unité de base du la possibilité de redémarrage des points de contrôle. Lorsque l’exécution d’un package utilisant des points de contrôle est reprise, elle commence par la tâche de flux de contrôle ayant échoué. Tout le flux de données de cette tâche doit être réexécuté à partir du début de la tâche du flux de contrôle. Autrement dit, si le package a été interrompu lors de l’exécution d’une tâche du flux de données, celle-ci est exécutée de nouveau à partir du début (la tâche ne reprend pas à partir de la dernière ligne transférée).

Les points de contrôle sont activés par configuration de la propriété SaveCheckpoints du package sur True dans les propriétés du package SSIS. Si vous activez des points de contrôle, vous devez également indiquer à SSIS où enregistrer les données de point de contrôle en affectant un fichier au point de contrôle dans la propriété CheckpointFileName du fichier. En outre, le traitement du fichier de point de contrôle est contrôlé par la propriété CheckpointUsage. La propriété CheckpointUsage prend en charge les valeurs suivantes.

• Jamais. Le fichier de point de contrôle n’est pas utilisé et le package s’exécute à partir du début du flux de contrôle du package.

• Toujours. Le fichier de point de contrôle est toujours utilisé et le package redémarre à partir du point d’échec de l’exécution précédente. L’exécution du package échoue si le fichier de point de contrôle est introuvable.

• IfExists. Si le fichier de point de contrôle existe, le package redémarre à partir de l’échec de l’exécution précédente ; sinon, il s’exécute à partir du début du flux de contrôle du package.

Vous pouvez voir un exemple des propriétés du point de contrôle d’un package dans la figure 9.

Figure 9 : activation de points de contrôle

Une fois les points de contrôle activés pour un package, SSIS effectue le suivi de l’exécution du package en écrivant les données dans le fichier de point de contrôle. SSIS utilise les données de ce fichier pour déterminer les tâches du flux de contrôle du package exécutées. En outre, si un package utilise un journal de fournisseur de journaux, SSIS écrit les événements vers le fournisseur en détaillant l’utilisation des points de contrôle du package. Pour plus d’informations sur l’utilisation de fournisseurs de journaux, reportez-vous à la section Journalisation des packages SSIS de ce document.

Les conteneurs incluent deux propriétés supplémentaires qui contrôlent la façon dont le package répondra aux échecs : FailPackageOnFailure et FailParentOnFailure. Si FailPackageOnFailure est activé et qu’il existe une erreur d’exécution du conteneur, l’exécution du package prend fin et le package est redémarré à partir de ce conteneur. Si FailParentOnFailure est activé et que le package s’exécute en tant que package enfant à partir d’un conteneur parent et qu’une erreur se produit, le package enfant et le package parent prennent fin et le redémarrage reprend avec le package enfant.

Prise en charge des transactions

Pour garantir que la base de données reste dans un état cohérent même en cas d’échec d’un package, SSIS offre une prise en charge complète des transactions de base de données. Comme une transaction de base de données standard, les modifications apportées par un package à la base de données peuvent être validées en tant qu’unité en cas de réussite d’une tâche d’un package, ou peuvent être annulées en tant qu’unité si la tâche échoue, ce qui permet de préserver l'intégrité de la base de données. Par exemple, si la prise en charge des transactions est activée et que le package contenant plusieurs tâches de flux de données échoue, toutes les actions de mise à jour de la base de données effectuées par les deux tâches de flux de données sont annulées, ce qui permet de garantir l’état cohérent de la base de données. Lorsque la condition d’erreur est corrigée et que le package est redémarré ultérieurement, les deux tâches du flux de données sont réexécutées. Les transactions SSIS peuvent également utiliser MS DTC (Microsoft Distributed Transaction Coordinator) avec les connexions prenant en charge MS DTC. De la même façon pour les connexions SQL Server, les transactions SSIS prennent également en charge MARS (Multiple Active Results Sets).

Les transactions peuvent être activées pour tous les types de conteneur SSIS, notamment les packages, les conteneurs, la boucle For, la boucle Foreach et les conteneurs de séquence. Vous pouvez activer la prise en charge des transactions à l’aide de la propriété TransactionOption du conteneur, laquelle peut être définie dans la fenêtre des propriétés de SSIS Designer ou par programmation. La propriété TransactionOption prend en charge les valeurs suivantes :

• Non pris en charge. Le conteneur ne démarre pas de transaction et ne joint pas une transaction existante démarrée par un conteneur parent.

• Pris en charge. Le conteneur ne démarre pas de transaction mais joint une transaction existante démarrée par un conteneur parent.

• Requis. Le conteneur démarre une transaction. Si une transaction existante a déjà été démarrée par le conteneur parent, le conteneur la joint.

Traitement des erreurs de transformation

Les packages SSIS incluent la possibilité de transformer des données lorsqu’elles sont déplacées des adaptateurs de données source vers les adaptateurs de données de destination. Pour gérer les erreurs pouvant survenir dans le processus de transformation, SSIS permet de décider, colonne par colonne, comment traiter les données ne pouvant pas être transformées. Vous pouvez choisir d’ignorer les données incorrectes pour un champ donné, ou de rediriger la ligne vers une table réservée en vue d’un retraitement ultérieur. L’utilisation d’une base de données centrale pour le stockage des lignes redirigées permet l’audit et l’examen centralisés des erreurs de transformation de package. Pour identifier les packages source, le processus de flux d’erreur proprement dit peut transformer la ligne d'erreur en ajoutant le nom du package et les informations d'identification d'exécution aux données écrites dans la base de données centrale de journalisation des erreurs.

Gestionnaires d’événements

La possibilité de générer et de traiter les événements est une autre fonctionnalité nouvelle de SQL Server 2005, qui renforce la simplicité de gestion de SSIS. Les gestionnaires d’événements permettent aux packages SSIS de répondre par programmation aux événements générés lors de l’exécution par les conteneurs et les tâches.

Lors de l’exécution, les événements sont déclenchés par les exécutables des packages, tels que les tâches, les conteneurs et les packages, afin de signaler un certain nombre d'états différents, notamment : conditions d’erreur, instant de démarrage d’une tâche, instant de fin d’une tâche ou changement d’état d’une variable. Vous pouvez étendre les fonctionnalités d’un package en tirant parti de la capacité de SSIS à ajouter des gestionnaires d’événements personnalisés pour les différents éléments du package. L’utilisation de gestionnaires d’événements personnalisés permet à votre package d’effectuer automatiquement un certain nombre de tâches de gestion différentes, notamment le nettoyage d’un fichier temporaire et des objets de base de données, l’envoi de courrier électronique ou autres notifications, ou encore la création d’informations de journalisation. Les gestionnaires d’événements sont créés via l’onglet Gestionnaire d’événements de SSIS Designer, dans BI Development Studio.

Tâches d’exécution de packages

Il peut arriver que vous souhaitiez fractionner un workflow de package complexe en plusieurs packages ou unités de travail distincts. Les tâches d’exécution de packages permettent ce comportement en autorisant l’exécution d’un package à partir d’un autre package. Par exemple, si vous disposez d’un package parent qui envoie du courrier électronique, vous pouvez créer un package enfant qui se connecte au serveur SMTP et qui transmet simplement l’adresse de messagerie du package parent au package enfant.

La création de packages enfants plus petits et ciblés, puis l’appel de ces packages à partir de plusieurs packages parents, simplifie le processus de développement et facilite la réutilisation et la gestion des packages.

Planification de l’exécution de packages

Vous pouvez planifier l’exécution de packages SSIS à l’aide de l’agent SQL Server. L’agent SQL Server est l’outil de planification de tâches intégré à SQL Server 2005. Comme le serveur SSIS, l’agent SQL Server est implémenté en tant que service Windows et ce service doit s’exécuter afin de prendre en charge la planification de tâches. En outre, comme le serveur SSIS, le service de l’agent SQL Server est géré via le Gestionnaire d’ordinateurs SQL intégré à la console MMC Gestion de l’ordinateur.

Pour créer une nouvelle tâche de l’agent SQL Server afin de planifier un package avec SQL Server Management Studio :

1. Ouvrez l'explorateur d'objets.

2. Développez le noeud Agent SQL Server.

3. Cliquez avec le bouton droit de la souris sur le noeud Tâches.

4. Une tâche de l’agent SQL Server est constituée d’une série d’étapes. Pour exécuter un package SSIS, ajoutez une nouvelle étape à la tâche de l’agent SQL Server. Pour cela, sélectionnez la page Étapes, puis cliquez sur le bouton Nouveau afin d’afficher la boîte de dialogue Nouvelle étape illustrée dans la figure 10.

Figure 10 : Planification de packages SSIS

Lorsque vous créez une étape qui exécute un package SSIS, l'agent SQL Server vous permet de spécifier les mêmes propriétés d'exécution que celles utilisées lors de l’exécution du package à partir de SSIS Designer ou via l’utilitaire dtexec. Cela concerne la fourniture des fichiers de configuration, l’activation des points de contrôle et l’ajout de la journalisation. Si la tâche contient plusieurs packages ou des étapes successives, vous pouvez configurer des procédures entre chaque étape afin de contrôler l’exécution de la tâche en fonction de la fin, de la réussite ou de l’échec de chaque étape.

Exécution de packages distants

Pour exécuter des packages SSIS sur des systèmes SQL Server, vous pouvez utiliser SQL Server Management Studio afin de créer une tâche de l’agent SQL sur le serveur distant. Cette tâche peut ensuite effectuer une tâche d’exécution d’agent, qui appelle l’utilitaire dtexec afin d’exécuter le package SSIS sur le système distant.

En outre, vous pouvez concevoir des packages pouvant exécuter des packages SSIS sur des systèmes SQL Server distants en utilisant la tâche Exécuter l’agent SQL Server, dans la section des tâches Plan de maintenance de la boîte à outils SSIS Designer. Lorsque vous ajoutez la tâche Exécuter l’agent SQL Server à SSIS Designer, vous pouvez configurer ses propriétés de connexion afin de pointer vers le serveur distant. Lorsque la tâche est exécutée, l’agent SQL Server exécute un package sur l’ordinateur distant.

Sécurisation de packages SSIS

SQL Server 2005 offre plusieurs nouveaux mécanismes permettant de sécuriser des packages SSIS ; ceux-ci incluent le cryptage de packages et la signature numérique de packages. Le cryptage de packages SSIS permet la protection des données dans le package. Les packages SSIS sont cryptés lors de leur création ou lors de leur exportation. SSIS utilise l’algorithme de cryptage Triple DES (Data Encryption Standard) avec une longueur de clé de 192 bits. Le cryptage basé sur une clé utilisateur utilise les normes DPAPI (Data Protection API). Le cryptage d’un package est contrôlé via la propriété ProtectionLevel du package. SSIS prend en charge les valeurs suivantes pour la propriété ProtectionLevel.

• DontSaveSensitive. Les données sensibles, telles que les données d’authentification utilisateur, ne sont pas enregistrées dans le package. Lorsque le package est ouvert par la suite avec SSIS Designer, les données sensibles ne sont pas présentes et l’utilisateur doit les fournir. Comme pour toutes les propriétés ProtectionLevel sensibles, seules les données sensibles du package sont affectées. Le reste du package est inchangé.

• EncryptSensitiveWithUserKey. Les données sensibles sont enregistrées dans le cadre du package, mais ces données sont cryptées avec une clé basée sur l’utilisateur ayant créé ou exporté le package. Seul l’utilisateur ayant créé le package pourra l'exécuter. Si un autre utilisateur ouvre le package à l’aide de SSIS Designer, les données sensibles précédentes restent cryptées et SISS Designer ouvre le package sans les données sensibles.

• EncryptSensitiveWithPassword. Les données sensibles sont enregistrées dans le cadre du package, mais ces données sont cryptées avec un mot de passe fourni par l’utilisateur lors de la création ou de l’exportation du package. Si un autre utilisateur ouvre le package avec SSIS Designer, il doit fournir un mot de passe afin d’accéder aux données cryptées. S’il ne connaît pas le mot de passe, SISS Designer ouvre le package sans les données sensibles.

• EncryptAllWithPassword. L’ensemble du contenu du package est crypté avec un mot de passe fourni par l’utilisateur lors de la création ou de l’exportation du package. Lorsque le package est ouvert dans SSIS Designer, l’utilisateur doit fournir le mot de passe du package. Si l’utilisateur ne connaît pas le mot de passe, il ne pourra pas accéder au package.

• EncryptAllWithUserKey. L’ensemble du contenu du package est crypté avec une clé basée sur l’utilisateur ayant créé ou exporté le package. Seul l’utilisateur ayant créé le package pourra l’ouvrir.

• ServerStorage. Aucun cryptage n’est ajouté au package. Le contenu du package est sécurisé en fonction de la sécurité d’accès aux objets de la base de données. Si la valeur ServerStorage est utilisée, le package doit être enregistré dans SQL Server, dans la table sysdtspackages90 de la base de données msdb. Il ne peut pas être enregistré dans le système de fichiers.

Le modèle de sécurité du package SSIS est également extensible. Le schéma XML d’un package SSIS comporte un attribut Sensitive qui contrôle l’accès aux propriétés du package. Par exemple, si l’attribut Sensitive est configuré sur 1, toutes les données sensibles sont supprimées lorsque l’utilisateur ouvre le package. Les développeurs peuvent incorporer cet attribut Sensitive afin d’obtenir le même type de protection pour leurs composants SSIS personnalisés.

Si vous utilisez la propriété ServerStorage ProtectionLevel, la méthode de contrôle de l’accès aux packages enregistrés dans la base de données consiste à utiliser les rôles de la base de données SQL Server. Par défaut, SQL Server 2005 offre les rôles suivants pour la gestion des packages SSIS. Vous les trouverez en ouvrant SQL Server Management Studio, puis en développant le noeud base de données msdb, Rôles, Rôles de base de données :

• db_dtsadmin. Droits administrateur des packages SSIS.

• db_dtsltduser. Droits permettant d’exécuter uniquement les packages SSIS que l’utilisateur est autorisé à exécuter.

• db_dtsoperator. Droits d’exploitation des packages SSIS, incluant la possibilité d’exécuter, de sauvegarder et de restaurer les packages.

Vous pouvez également créer vos propres rôles de base de données pour la gestion des packages SSIS. Ajoutez les utilisateurs appropriés à ces rôles, puis affectez ces rôles à vos propres packages SSIS.

Vous pouvez activer les rôles de package avec SQL Server Management Studio en cliquant avec le bouton droit de la souris sur un package enregistré, puis en sélectionnant l’option Rôles de package dans le menu contextuel, comme illustré par la figure 11.

Figure 11 : ajout de rôles de package

Les packages SSIS peuvent également être signés numériquement. La signature numérique de packages permet à SQL Server de vérifier l’authenticité d’un package lors de son exécution. Les packages peuvent être signés numériquement au cours du processus de conception à l’aide de SSIS Designer. Une fois qu’un package a été signé numériquement, ce package est en lecture seule et ne peut plus être modifié.

Journalisation des packages SSIS

La journalisation des événements des packages SSIS offre une vue utile des tâches exécutées lors de l’exécution du package. La journalisation des packages SSIS crée un enregistrement qui effectue le suivi de l’exécution des tâches et conteneurs d’un package SSIS.

L’auteur d’un package peut choisir, par package et par objet de flux de contrôle, quels objets doivent fournir des entrées de journal, ainsi que contrôler les détails fournis par l’objet. Les données de journalisation peuvent être envoyées à un ou plusieurs des divers « fournisseurs de journaux » que l’auteur d’un package peut configurer pour chaque package. Par exemple, le package peut envoyer des entrées de journaux à un fichier texte et au journal des événements de Windows.

Les entrées de journaux peuvent également être affichées et copiées vers le presse-papiers de Windows à partir de Business intelligence Studio, via l’ouverture de la fenêtre Événements du journal dans le menu SSIS.

La figure 12 présente un aperçu de la journalisation des packages SSIS, dans lequel les journaux sont stockés sur un serveur pour la gestion centrale.

Figure 12 : Journalisation des packages SSIS

Le tableau 3 répertorie les fournisseurs de journaux inclus dans SSIS.

Fournisseur de journaux	Description
Fournisseur de journaux du générateur de profils SQL	Écrit les fichiers journaux que vous pouvez afficher via le générateur de profils SQL. L’extension par défaut des fichiers est .trc.
Fournisseur de journaux de SQL Server	Écrit les entrées des journaux dans la table sysdtslog90 d’une base de données SQL Server 2005.
Fournisseur de journaux Événements Windows	Écrit les entrées dans le journal de l’application du journal des événements de Windows sur l’ordinateur local.
Fournisseur de journaux Fichier XML	Écrit les fichiers journaux vers un fichier XML. L’extension par défaut des fichiers est .xml.
Fournisseur de journaux Fichier texte	Écrit les entrées des journaux dans des fichiers texte ASCII au format CSV (valeurs séparées par des virgules). L’extension par défaut des fichiers est .log.

Tableau 3 : Fournisseurs de journaux

Fournisseur de journaux du générateur de profils SQL

Le fournisseur de journaux du générateur de profils SQL vous permet de créer des journaux pouvant être ouverts via l’interface graphique du générateur de profils SQL. L’utilisation du générateur de profils SQL pour combiner les données des journaux des Analyseurs de performances du système vous offre un puissant outil d’analyse des effets de l’exécution du package sur les performances du système, ainsi que de résolution des problèmes tels que l’exécution trop longue de packages ou de tâches.

Fournisseur de journaux de SQL Server

L’utilisation du fournisseur de journaux SQL Server vous permet de capturer les données d’exécution du package SSIS dans la table sysdtslog90 de la base de données. Vous pouvez interroger par la suite les données à l’aide d’instructions SQL. L’écriture des données des journaux SSIS dans SQL Server vous permet de stocker de manière centralisée toutes les données de journaux générées par plusieurs packages SSIS qui s’exécutent à partir d’un ou plusieurs systèmes. L’utilisation du fournisseur de journaux SQL Server est un bon choix si vous souhaitez créer des rapports et surveiller les opérations de plusieurs packages SSIS.

Fournisseur de journaux Événements Windows

Le fournisseur de journaux Événements Windows est un bon choix de journalisation si vous utilisez un logiciel de gestion des opérations tel que Microsoft Operations Manager (MOM) pour surveiller vos serveurs. L’utilisation de MOM et du pack de gestion de SQL Server vous permet de configurer des alertes et d’exécuter d’autres actions à partir des entrées de journaux de vos packages.

Fournisseur de journaux Fichier XML

Le fournisseur de journaux Fichier XML est un bon choix si vous souhaitez pouvoir parcourir le journal graphiquement. Vous pouvez écrire des transformations XSLT qui formatent les données des journaux afin de les afficher sous forme de page Web. XML est également un format intéressant si vous devez partager les résultats des packages avec des personnes extérieures, ou si vous souhaitez consolider des fichiers journaux à partir de différentes sources de données, notamment SQL Server.

Fournisseur de journaux Fichier texte

L’utilisation du fournisseur de journaux Fichier texte vous permet de créer des journaux faciles à utiliser. Les fichiers texte sont faciles à consulter, car aisément transportables, ce qui les rend particulièrement utiles lors de la phase de test élémentaire d’un package.

Fournisseur de journaux personnalisé

Vous pouvez créer votre propre fournisseur de journaux personnalisé afin d’obtenir des informations adaptées à vos besoins. L’utilisation d’un fournisseur de journaux personnalisé vous permet d’intégrer des journaux SSIS à vos applications.

Pour faciliter la gestion des opérations, les packages SSIS écrivent toujours des informations de journalisation de base dans le journal des événements de Windows, même pour les packages qui n’intègrent pas la journalisation. Les packages SSIS écrivent des événements pour le lancement et la fin des packages, et peuvent être identifiés via les sources d’événements SQLISPackage ou SQLISService.

Compatibilité descendante des packages DTS SQL Server

Si vous utilisez actuellement des packages DTS SQL Server 2000 dans votre entreprise, vous pouvez continuer à utiliser nombre de vos fonctions de package actuelles lors de la mise à niveau vers SSIS. SSIS offre plusieurs options pour l’exécution ou la migration de vos packages DTS, notamment l’utilisation de l’Assistant Migration pour migrer les packages simples, l’exécution manuelle de la migration de packages, ou encore l’exécution de vos packages existants à l’aide de la tâche Exécuter le package.

Lorsque vous choisissez de conserver vos packages DTS tels quels, au moins à court terme, les packages DTS existants sont exécutés à partir d’un package SSIS qui appelle la tâche Exécuter le package DTS SQL Server 2000. Sur un serveur sur lequel SQL Server 2000 Enterprise Manager a été installé précédemment, vous pouvez modifier votre package DTS dans l’éditeur SSIS de SQL Server 2005. Lorsque vous sélectionnez le bouton Modifier le package dans l’éditeur de tâches, l’éditeur DTS de SQL Server 2000 du package est chargé. Cette approche vous permet de procéder à la mise à niveau vers SQL Server 2005, tout en continuant d’utiliser vos packages DTS originaux. Vous pouvez ensuite migrer ou réécrire progressivement vos packages DTS en packages SSIS afin de tirer parti des nouvelles fonctionnalités de SSIS.

Migration de packages DTS SQL Server 2000

Bien que SSIS puisse continuer à exécuter vos packages DTS, vous pouvez tirer parti de la mise à jour de vos packages afin d’inclure les nouvelles possibilités des packages SSIS. Dans la mesure où SSIS bénéficie d’une conception et d’un modèle d’objet totalement nouveaux, certains packages DTS migrent facilement à l’aide de l’Assistant Migration. Les autres packages DTS ne pourront tirer pleinement parti de tous les avantages de SSIS que s’ils sont migrés manuellement ou réécrits intégralement.

Utilisation de l’Assistant Migration

L’Assistant Migration est bien adapté aux packages simples. Les packages DTS contenant des tâches présentant une corrélation un à un avec les tâches SSIS sont généralement migrés directement vers des packages SSIS sans autre opération. Ces tâches sont répertoriées dans le tableau 4.

Tâche	Description
Tâche Exécuter SQL	Exécute des instructions SQL ou des procédures stockées à partir d’un package
Tâche Insérer en masse	Copie de grandes quantités de données dans une table ou une vue
Tâche FTP (File Transfer Protocol)	Télécharge des fichiers et gère les répertoires
Tâche Exécuter processus	Exécute une application ou un fichier batch
Tâche Envoyer courrier électronique	Envoie un message électronique
Tâche Copier objets SQL Server	Copie une table entre deux serveurs
Tâche Exécuter package	Lance un autre package

Tableau 4 : Tâches SSIS simples

Outre ces sept tâches DTS permettant une migration directe vers SSIS, trois autres tâches permettent la migration vers un package SSIS mis à jour, mais risquent de ne pas fonctionner comme prévu après la migration. Il s’agit des tâches Script ActiveX, Propriétés dynamiques et Traitement DTS des services d'analyse. Ces tâches interagissent généralement avec le modèle d’objet DTS de SQL Server 2000 et dans la mesure où le modèle d’objet SSIS est totalement nouveau, vous devrez probablement redévelopper ces tâches.

Plusieurs autres tâches ne peuvent pas être migrées avec l’Assistant Migration, notamment les tâches personnalisées, Assistant Copie de base de données, Requête contrôlée par les données, Data Pump, Services Data Pump et Transformation de données. Vous devez les réécrire à l’aide de composants SSIS natifs ou exécuter le package DTS existant à l’aide de la tâche Exécuter package DTS 2000

Vous pouvez démarrer l’Assistant Migration de différentes façons. Dans SQL Server Management Studio, cliquez avec le bouton droit de la souris sur un package répertorié sous le noeud Package DTS 2000 et cliquez sur Migrer dans le menu contextuel. Vous pouvez également, dans BI Development Studio, sélectionner le dossier Packages DTS dans le volet Explorateur de solutions, puis cliquer sur l’option Migrer le package DTS 2000 dans le menu.

L’Assistant Migration vous guide dans la procédure requise pour migrer votre package DTS. Vous pourrez sélectionner votre package DTS original, désigner un emplacement pour le stockage du package migré, affecter un fichier journal pour capturer les spécificités des changements de l’assistant au cours de la migration, puis terminer en examinant le résumé de la migration. Il est important de noter que l’Assistant Migration crée un fichier journal qui enregistre les résultats de la migration. Une fois la migration terminée, vous pouvez examiner ce fichier journal afin de déterminer les avertissements ou erreurs qui se sont produits au cours du processus de migration. Une fois la migration terminée, fermez l’Assistant Migration et ouvrez le package SSIS migré dans l’éditeur BI Development Studio afin de corriger d’éventuelles erreurs ou d’ajouter des éléments à votre nouveau package.

Création de configurations de package

Les configurations de package vous permettent de fournir des données à vos packages SSIS lors de l’exécution. Par exemple, une utilisation courante de la configuration consiste à permettre l’application dynamique du nom du serveur et des informations de connexion utilisateur lors de l’exécution. Lorsqu’un package SSIS est configuré pour utiliser des configurations, il charge automatiquement les informations de configuration lors de l’exécution. Deux des utilisations courantes des configurations sont le passage de variables et d’informations de connexion au package lors de l’exécution. Dans le cas des variables, la valeur qui sera utilisée lors de l’exécution est affectée à la propriété Valeur. De la même façon, pour définir de manière dynamique les propriétés d’une connexion OLE DB, utilisez une configuration distincte afin d’affecter une valeur à chacune des propriétés du gestionnaire de connexion : ConnectionSting, ServerName et InitialCatalog. Lorsque le package est utilisé lors de l’exécution, chacune des valeurs de la configuration est utilisée pour créer la chaîne de connexion.

Les configurations des packages SSIS sont créées à l’aide de l’organiseur de package démarré via BI Development Studio.

Pour créer une configuration de package :

1. Sélectionnez l’option Configurations des packages DTS dans BI Development Studio. Cela permet de démarrer l’outil Organiseur des configurations de packages illustré figure 13.

   

   Figure 13 : Organiseur des configurations de packages SSIS

2. Lorsque l’organiseur des configurations de packages s’affiche, cliquez sur le lien Activer les configurations de package.

3. Cliquez sur le bouton Ajouter afin de démarrer l’Assistant de configuration SSIS.

4. L’Assistant de configuration vous guide dans la procédure de création d’une configuration de package. La première étape consiste à décider du type de configuration. Les données de configuration peuvent être chargées dans vos packages à partir des emplacements suivants :

   • Registre

   • Variables d’environnement

   • Package parent

   • Fichiers XML

   • SQL Server

   Vous pouvez créer plusieurs configurations pour un même package. Chaque configuration est appliquée au package dans l’ordre dans lequel elle s’affiche dans l’organiseur de package. Utilisez les boutons directionnels afin de déplacer une configuration vers le haut ou vers le bas dans la liste. Par exemple, dans les scénarios d’entreprise et de département, une configuration peut s'appliquer à tous les packages. Le fichier de configuration de l’entreprise est appliqué au niveau supérieur, puis le fichier de configuration unique de chaque département est appliqué.

   Vous pouvez également créer un objet de configuration unique que vous appliquez à plusieurs packages. Par exemple, si vous avez besoin de déplacer plusieurs packages vers plusieurs systèmes pour lesquels la seule différence dans les propriétés des packages est le nom du serveur, vous pouvez créer un objet de configuration qui présente le type variable d’environnement et inclure cette configuration avec chacun des packages. Lorsque vous choisissez un type de configuration XML, un fichier de configuration XML est créé avec l’extension .dtsConfig.

5. Ensuite, sélectionnez les propriétés ou variables du package dont les valeurs seront définies par la configuration lors de l’exécution du package. Les configurations de fichiers XML et les configurations SQL Server prennent en charge la sélection de plusieurs propriétés dans un même objet configuration. Les autres types de configuration permettent une propriété configurable par configuration.

6. Une fois les écrans de l’Assistant complétés, la nouvelle configuration est ajoutée à la liste dans la boîte de dialogue Organiseur des configurations de packages. Si vous souhaitez modifier une configuration, cliquez sur Modifier afin de réexécuter l’Assistant Configuration, puis sélectionnez différents objets et différentes propriétés.

Déploiement de packages SSIS

Le déplacement de packages DTS entre serveurs étaient l’une des principales difficultés de SQL Server 2000. Pour déplacer vos packages de votre environnement de développement vers vos systèmes de production, ou pour déplacer un package d’un système de production vers un autre, il était généralement nécessaire de modifier manuellement les packages afin de s’assurer que tous les objets de connexion pointaient vers le serveur et les bases de données appropriées. Inutile de préciser que la configuration manuelle de ces packages prenait du temps et était sujette aux erreurs. Dans la mesure où SQL Server Integration Services peut créer des objets de configuration de packages, la configuration manuelle des packages n’est plus utile. Les configurations de packages vous permettent de modifier dynamiquement les variables et les propriétés des objets lors de l’exécution, ce qui rend le déploiement des packages plus flexible et beaucoup plus facile à gérer.

Les packages SSIS peuvent être déployés de quatre façons différentes :

• Utilitaire de déploiement de BI Development Studio.

• Fonctionnalités d’importation et d'exportation de package de SQL Server Management Studio.

• Enregistrement d’une copie du package dans le système de fichiers.

• Exécution de l’utilitaire de ligne de commande dtutil.

L’utilitaire de déploiement vous permet de déployer des packages SSIS et des dépendances de packages telles que des configurations de packages et le fichier DTSDeploymentManifest.xml. Une fois que vous avez créé l’utilitaire de déploiement pour votre projet, exécutez l’Assistant d’installation de package SSIS afin d’installer les packages dans le système de fichiers ou dans une instance de SQL Server 2005. La figure 14 offre une vue d’ensemble du processus de déploiement.

Figure 14 : flux de déploiement SSIS avec l’utilitaire de déploiement

Création de l’utilitaire de déploiement de package

SSIS contient une fonctionnalité appelée Utilitaire de déploiement de package, qui vous permet d’assembler vos packages SSIS, vos configurations de package et les fichiers correspondants dans un dossier de déploiement, puis de créer un fichier de configuration exécutable afin d'installer vos packages.

Une fois que vous avez conçu le package dans BI Development Studio et que vous avez ajouté des configurations à votre projet, cliquez avec le bouton droit de la souris sur les propriétés du projet dans le volet Explorateur de solutions. Sélectionnez ensuite l’option Propriétés afin d’afficher la boîte de dialogue Pages de propriétés, comme illustré dans la figure 15.

Figure 15 : Propriété de l’utilitaire de déploiement de package

Dans la boîte de dialogue Pages de propriétés, sélectionnez l'option Utilitaire de déploiement et définissez les valeurs des propriétés. Le tableau 5 répertorie les propriétés de l’utilitaire de déploiement.

Propriété	Description
AllowConfigurationChange	Valeur qui précise si les configurations peuvent être mises à jour au cours du déploiement. La valeur par défaut de cette propriété est True.
CreateDeploymentUtility	Valeur qui précise si un utilitaire de déploiement de package est créé lors de la création du projet. La valeur par défaut de cette propriété est False. La propriété doit avoir la valeur True pour qu’un utilitaire de déploiement soit créé.
DeploymentOutputPath	Emplacement des fichiers utilisés par le déploiement du projet, par rapport au projet SSIS.

Tableau 5 : propriétés de déploiement de packages

Pour créer un utilitaire de déploiement, affectez la valeur True à l’option CreateDeploymentUtility dans la page de propriétés du projet. Créez ensuite votre projet en sélectionnant l’option Créer la solution dans le menu BI Development Studio. La création du projet entraîne la création du fichier, DTSDeploymentManifest.xml, et la copie des packages du projet (ainsi que le fichier DTSInstall.exe) dans le dossier bin/Deployment, ou dans l’emplacement spécifié dans la propriété DeploymentOutputPath. Le fichier DTSDeploymentManifest.xml répertorie les packages et les configurations de package du projet. DTSInstall.exe est l’application qui exécute l’Assistant Installation de package.

Utilisation de l’Assistant Installation de package

Pour déployer le projet SSIS, créez un utilitaire de déploiement de package, puis exécutez le programme d'installation du package, à savoir DTSInstall.exe. Celui-ci est automatiquement copié dans votre dossier de déploiement.

Pour utiliser l’Assistant Installation de package :

• Pour démarrer l’Assistant Installation de package qui vous guidera dans le processus d’installation, exécutez le programme DTSInstall.exe.

  Sélectionnez un type de déploiement : déploiement du système de fichiers ou déploiement SQL Server.

  L’option déploiement du système de fichiers installe les packages dans le système de fichiers en tant que fichiers .dtsx. Vous pouvez ouvrir et modifier ultérieurement les fichiers .dtsx à l’aide de SSIS Designer. Si vous avez utilisé des dossiers personnalisés pour stocker vos packages de système de fichiers SSIS, sélectionnez le dossier cible approprié.

  L’option Déploiement SQL Server installe vos packages dans la table sysdtspackages90 de la base de données msdb de SQL Server 2005. Cette option copie également les fichiers de dépendance du package, tels qu’un fichier de configuration XML, vers un dossier désigné sur le système de fichiers.

  L’utilisation de l’une ou l’autre de ces options de déploiement enregistre le package et l’affiche dans le dossier Packages stockés de SQL Server Management Studio.

• Ensuite, l’Assistant vous invite à indiquer un dossier pour les installations du système de fichiers, ou un serveur SQL Server cible pour les déploiements SQL Server. Les déploiements SQL Server vous invitent également à indiquer le dossier du système de fichiers pour copier les packages et les fichiers de dépendance. Pour les packages contenant des configurations, vous pouvez modifier les valeurs de configuration pouvant être mises à jour. L’Assistant Installation de package affiche une invite semblable à celle illustrée figure 16.

  

  Figure 16 : Assistant Installation de package

• L’exécution de l’Assistant Installation de package vous invite à spécifier la propriété ProtectionLevel du package. Pour plus d’informations sur la propriété ProtectionLevel du package, reportez-vous à la section Sécurisation des packages SSIS dans ce document.

Déploiement manuel de packages

Outre les outils intégrés à BI Development Studio pour le déploiement de packages, vous pouvez également déployer des packages manuellement. La procédure initiale de création de packages est la même pour les deux types de déploiements. Vous pouvez créer le package en utilisant SSIS Designer dans BI Development Studio, ce qui entraîne la création d’un fichier de package .dtsx.

Une fois le fichier de package créé, vous pouvez le copier manuellement sur le système de destination, ou incorporer une routine de copie de fichier dans le cadre d’un script de déploiement personnalisé. Si vous déployez manuellement des packages SSIS, vous devez inclure explicitement les fichiers requis dans votre script de déploiement. Si vous souhaitez que le package soit installé sur SQL Server ou qu’il soit visible par le service SSIS dans le système de fichiers, incluez l’outil dtutil dans le cadre de vos processus de déploiement. Dtutil peut copier le package dans le système de fichiers ou vers la base de données msdb de SQL Server. Une fois le package déplacé vers sa destination, vous pouvez l’exécuter via les utilitaires dtexec ou dtexecui.

Les packages SSIS peuvent également être exécutés sur des systèmes sur lesquels SQL Server n’est pas installé. Cependant, le .NET Framework et le runtime SSIS doivent être installés pour permettre l’exécution des packages sur les systèmes sur lesquels SQL Server 2005 n’est pas installé.

Résumé

SQL Server Integration Services, une nouveauté de SQL Server 2005, est une plate-forme d’intégration de données au niveau entreprise. SSIS contient un moteur de développement de workflow permettant d’exécuter différentes tâches, du transfert de données aux opérations de maintenance de la base de données. Les nouvelles fonctionnalités de gestion et de déploiement de SSIS constituent des améliorations majeures des mêmes fonctionnalités de son prédécesseur DTS (Data Transformation Services). SSIS vous permet de créer des configurations pour les packages afin que ceux-ci puissent s’adapter de manière flexible à différentes conditions d’exécution. Le service SSIS vous permet de gérer le stockage des packages, ainsi que de surveiller leur exécution. L’ensemble des outils inclus dans SQL Server 2005 vous permet d’effectuer la gestion et le déploiement des packages à la fois à partir de l’environnement de développement intégré et via la ligne de commande.

Ressources supplémentaires (en anglais)

Pour plus d’informations sur SQL Server 2005 Integration Services, consultez les ressources suivantes :

https://msdn.microsoft.com/SQL/sqlwarehouse/SSIS/default.aspx

Michael Otey est directeur technique de Windows IT Pro Magazine et rédacteur senior pour SQL Server Magazine. Il est également président de TECA, Inc., une entreprise de développement et de conseil spécialisée dans l’interopérabilité et les applications de base de données. Michael a travaillé avec différentes versions de DB2 depuis 1983. Il est l’auteur du Guide des nouvelles fonctionnalités de SQL Server 2005 publié par Osborne McGraw Hill.

Denielle Otey est vice-présidente de TECA, Inc., ainsi que consultant logiciel avec une longue expérience dans la conception, l’implémentation, le test et le débogage de logiciels en C, VC++, VB et Visual Studio .NET. Elle a également développé plusieurs utilitaires SQL Server, elle a développé et distribué des applications DB2 via TECA, Inc, et elle est co-auteur de ADO.NET, la référence complète, publié par Osborne McGraw Hill.

Cet article a été rédigé en partenariat avec A23 Consulting.