[SSIS] Data Transformation : chargement de données de SQL Server vers Oracle

Ce billet montre comment charger des données issues d’une table SQL Server vers une table Oracle (via Attunity). Il reprend les grandes lignes du billet relatif au chargement de données d’une table Oracle vers une table SQL Server.

Le modus operandi de notre exemple d’implémentation sera effectué avec l’outil de développement SQL Server Data Tools (à partir de SQL Server 2012). Mais toutefois, il reste valable dans le cas d’une utilisation de Visual Studio ou de Business Intelligence Development Studio (BIDS) dont l’interface graphique est sensiblement la même.

Avant de commencer…

Si vous avez déjà été faire un tour ici, vous pouvez ignorer cette section (mis à part éventuellement Présentation des besoins).

Présentation des besoins

Admettons que l’on dispose d’une table Emp située au sein d’une base de données SQL Server appelée MaBase et que l’on souhaite pouvoir charger tout son contenu dans une table homonyme stockée au sein d’une base de données Oracle XE.

Voici le contenu de la table SQL Server:


Et sa structure de données :


Du côté de la base de données Oracle, la table Emp, quant à elle, possède la structure suivante :


On suppose qu’elle est vierge de tout enregistrement.

    Au sujet de la connexion à Oracle

Avant de se lancer :

  • Un connecteur Attunity – particulièrement plus performant que les connecteurs traditionnels à Oracle (Microsoft DB Provider for Oracle, Oracle Provider for OLEDB…) – sera utilisé pour l’établissement de la connexion au niveau SSIS lors du chargement des données. Ledit connecteur est téléchargeable ici
    (seule la version 64 bits suffit pour tout environnement 64 bits).
  • Les drivers pour les clients Oracle seront installés sur la machine au sein de laquelle sera dévéloppé/lancé le package SSIS. A noter que si l’OS est en 64 bits, les versions 32 et 64 bits des drivers doivent être installées.
  • L’ajout de clés de registre permettant aux connecteurs SSIS de retrouver les clients Oracle sera réalisé, via l’interface de la base de registre (en tapant regedit dans Exécuter, la barre d’exploration du menu Démarrer ou l’invite de commandes MS DOS)
    :
    • Pour la version 32 bits du client Oracle, en modifiant, dans le registre HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI, les clés suivantes :
      • OracleOciLib (avec pour valeur « oci.dll »).
      • OracleSqlLib (« orasql11.dll » à la place de « SQLLib80.dll »).
      • OracleXaLib (« oraclient11.dll » au lieu de « xa80.dll »).

    En guise d’illustration :


  • Pour la version 64 bits du client Oracle, en faisant la même chose que pour le client 32 bits, mais dans le registre HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
La version des modules DLL utilisés correspondent à Oracle 11g(R2). A adapter en fonction de la version d’Oracle utilisée, sachant qu’Attunity ne supporte que les clients de version Oracle 10.x minimum.
  • La configuration du fichier TNSNAMES.ora doit être faite pour chaque client Oracle (32 bits comme 64 bits) afin de pouvoir permettre aux drivers Oracle de savoir où et comment se connecter à la base de données Oracle cible.
Vous pouvez utiliser, dans l’invite de commandes MS DOS, l’utilitaire tnsping pour tester la validité de la connexion à Oracle, dont la syntaxe est tnsping <Nom_Service_Oracle>.

Modus operandi

  • Au sein de l’onglet Control Flow :
    • Faites glisser un composant Data Flow Task :


  • Après avoir renommé (pour le principe) le composant Load data from SQL Server (en cliquant dessus, ou en effectuant un clic-droit>Rename), double-cliquez sur ledit composant pour accéder à la définition de ses flux de données (onglet Data Flow).


  • Au sein du Data Flow de Load data from SQL Server :
    • Faites glisser (ou double-cliquez sur) le composant OLE DB Source d’Attunity, situé dans la SSIS Toolbox, dans la zone de design :


    • Renommez le composant OLE DB Source en SQL Server Source – Emp :


  • Au sein des propriétés du composant SQL Server Source – Emp (accessible via clic-droit>Edit… ou double-clic) :
    • Spécifiez le nouveau gestionnaire de connexion SQL Server :


Dans notre cas, l’instance locale, le login de connexion (et son mot-de-passe), ainsi que la base de données MaBase ont été spécifiés.

  • Choisissez la table SQL Server de la base MaBase à utiliser (Emp, dans notre cas), pour le mode d’accès aux données :


A ce stade, il est, bien sûr, possible d’utiliser un autre mode d’accès, selon les besoins (jeu de résultats filtré, utilisation d’une variable…) :

Un clic sur Preview permet de prévisualiser le contenu de la table SQL Server :


Si vous le souhaitez, vous pouvez (dans la section Columns)
consulter les colonnes (automatiquement) sélectionnées, et/ou éventuellement les renommer :


Dans tous les cas, validez les modifications effectuées pour le composant OLE DB Source en pressant sur OK.

  • Toujours sur la zone de design Data Flow de Load data from SQL Server, faites glisser (ou double-cliquez sur) un composant Oracle Destination :


  • Renommez le composant Oracle Destination en Oracle Destination – Emp :


  • Placez une contrainte de précédence positive allant de SQL Server Source – Emp vers Oracle Destination – Emp :


  • Au sein des propriétés du composant Oracle Destination – Emp…
    • Dans la section Connection Manager,
      spécifiez le nouveau gestionnaire de connexion OLE DB pour accéder à la table Emp de la base MaBase de l’instance SQL Server située sur la machine MAC-ROG :


    Après configuration de la connexion, sélectionnez la table Emp comme table de destination, avec mode d’accès en fast load :


A cette étape, si vous utilisez le mode d’accès en fast load (comme ci-dessus), vous pouvez choisir d’activer ou non le chargement parallèle (très utile lors qu’un très gros volume de données est à traiter) et le mode d’archivage (No logging permet de ne pas journaliser les opérations transactionnelles). Il est également possible d’indiquer le nombre d’erreurs maximum à ne pas dépasser pour que le data flow aille jusqu’au bout (par défaut, 0, ce qui signifie qu’il n’y a pas de limite d’erreurs), ainsi que la taille maximale du tampon de transfert (64 Ko, par défaut).

L’autre mode d’accès, Table Name, permet de ne pas effectuer d’insertions en bloc (BULK-INSERT), contrairement à un fast load (qui est basé sur le protocole OCI avec chemin direct), avec notamment la possibilité de contrôler la taille de la transaction, ainsi que du batch.

A noter qu’en cas d’utilisation du mode d’accès fast load, quelques restrictions seront à observer, à commencer par le non-support des tables en cluster, les contraintes d’intégrité référentielles et les triggers.

  • Dans la section Mappings, faites les correspondances entre chaque colonne de la table Oracle et chaque colonne de la table SQL Server :


  • Validez pour arriver au résultat suivant :


Tests

Dans l’onglet Control Flow ou Load data from SQL Server, pressez sur F5 pour exécuter les flux de travaux.

Si tout va bien, le résultat sera le suivant :


On peut noter que 5 lignes (soient toutes les lignes de la table SQL Server) ont été insérées. Pour vérifier cela (du côté de SQL*Plus, par exemple) :


Pour aller plus loin…

Vous pouvez garder un œil ici, pour consulter les autres billets relatifs à la transformation de données.

Publicités

11 commentaires sur “[SSIS] Data Transformation : chargement de données de SQL Server vers Oracle

  1. Thomas dit :

    Bonjour.
    Je voudrais savoir comment gérer les mises à jour et ajout tant du côté de SQL Server que d’Oracle? par exemple on fait une modification sur le prénom de l’employé « Dupont »; son prénom devient Pierre Thomas au lieu de Pierre tout court; est ce que lors du chargement la mise à jour sera faite automatiquement dans la table de destination? ou bien il faut gérer soi même les changements (ajout, suppression, mise à jour); si oui comment? J’ai entendu parler des captures de données modifiées mais je n’ai pas encore exploré.
    Merci d’avance

  2. Thomas dit :

    Et apparemment la capture de données modifiées n’est fonctionnelle qu’à partir de SQL Server 2008 R2; j’ai toujours SQL Server 2005…

  3. Thomas dit :

    Bonjour,
    Merci; tout a fait; c’est hier que j’ai constaté qu’il y avait des articles sur ce sujet. Je vais lire et faire mes tests.
    A bientôt

  4. Thomas dit :

    Bonjour, J’ai réussi à développer un package avec Attunity for Oracle qui fait la synchronisation (avec Merge Join) entre SQL Server et Oracle. la synchro est unidirectionnelle de SQL Server vers Oracle. ça marche très bien. Merci pour vos articles qui sont toujours excellents.
    @ Bientôt

  5. Thomas dit :

    Bonjour,
    Comment est il possible de faire une non équi-jointure entre une table SQL SERVER et une table ORACLE pour localiser les lignes de la table ORACLE où les valeurs des colonnes diffèrent de la celles de la table SQL SERVER. Pour être plus clair, j’ai utilisé un composant merge join entre la table ORACLE ET SQL SERVER je fais insertion dans la table Oracle s’il y a de nouvelles lignes dans la table SQL SERVER et je fais une mise à jour sur la table ORACLE par rapport aux lignes existantes. Mais le problème est que pour la mise à jour j’utilise une requete paramétrée avec OLEDB Command mais c’est très lent lors de mise à jour puisqu’il fait un parcours séquentiel. Donc je voudrais faire une table intermédiaire qui stocke les lignes vraiment modifiées de SQL SERVER par rapport à ORACLE et que la requête ne se base que sur ces lignes au lieu de parcourir plus de 24 000 lignes….
    Merci d’avance

  6. Thomas dit :

    En d’autres termes je voudrais avoir un jeu de données résultant d’une requête du genre:
    SELECT TableSource.Col1, TableSource.Col2
    FROM TableSource INNE JOIN TableDestination ON TableSource.Cle=TableDestination.Cle
    WHERE TableSource.Col1TableDestination.Col1 OR TableSource.Col2TableDestination.Col2

    • Bonsoir Thomas,

      Pour répondre à la question, il existe diverses façons de procéder. Voici un exemple simple (en supposant que le but n’est pas de se priver de SSIS):
      1) Si ce n’est déjà fait, dans l’instance SQL Server, créer un linked server vers la base de données Oracle, et surtout sa table cible.
      2) Créer la staging table SQL Server qui ne contiendra que les données à mettre à jour à partir du Data Flow où sont réalisées les opérations d’UPSERT.
      3) Dans BIDS/SSDT, drag-droppez, au sein du Control Flow de la solution SSIS, un composant Execute SQL Task, et connectez le Data Flow d’UPSERT vers ce composant.
      3) Lancer la requête de mise-à-jour (UPDATE ad hoc classique ou procédure stockée) de la table Oracle (via linked server) à partir de la staging table SQL Server, en utilisant le composant Execute SQL Task.

      Avant chaque opération, la staging table peut être tronquée (via Execute SQL Task) afin de ne contenir que les lignes de données utiles, par la suite.

      Autre possibilité, et ne nécessitant pas de linked server, et offrant potentiellement de meilleures performances: créer la staging table au sein de la base de données Oracle cible, et lancer les opérations d’UPDATE, via le composant Execute SQL Task, en se connectant directement sur ladite base.

      Bon courage,

      M.

  7. Thomas dit :

    Bonjour M. Cherif,
    Content de vous lire; la solution linked server m’a en effet effleuré l’esprit mais je ne l’ai pas exploré. Je pense que je vais opter pour cette solution dans la mesure où pour certaines contraintes je n’ai pas l’autorisation de créer une table dans la base ORACLE. Juste pour savoir, y a t-il des dispositions particulières à prendre pour la création du serveur lié Oracle sur SQL Server? Je vais quand même faire une relecture par rapport à ca. Merci beaucoup et meilleures salutations

    • Bonjour Thomas,

      Concernant les linked servers, il faudra songer à installer et configurer le client Oracle idoine – qui contiendra toutes les bibliothèques inhérentes à une connexion à une instance Oracle – sur la machine où tourne SQL Server. Le client est téléchargeable au sein de l’OTN.

      Et côté configuration du linked server, choisir le bon provider (i.e., Oracle Provider for OLE DB) et les bonnes informations de connexion à la base Oracle (le contexte de sécurité Windows peut être utilisé si l’authentification à la base Oracle est aussi de type Windows NT).

      Bon courage,

      M.

  8. Thomas dit :

    Bonjour M. Cherif,
    Mission accomplie. Tout marche nickel.
    Merci beaucoup

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s