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

Ce billet montre comment charger des données issues d’une table Oracle vers une table SQL Server (via Attunity).

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…

Présentation des besoins

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

Voici le contenu de la table Oracle :


Et sa structure de données :


Du côté de la base de données SQL Server, la table Emp, quant à elle, possède une structure assez simple :


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
    (seul 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 Oracle (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 Oracle :
    • Faites glisser (ou double-cliquez sur) le composant Oracle Source d’Attunity, situé dans la SSIS Toolbox, dans la zone de design :


    • Renommez le composant Oracle Source en Oracle Source – XE_Emp :


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


Dans notre cas, l’instance XE a été spécifiée, ainsi qu’un nom d’utilisateur et un mot-de-passe préalablement créés pour accéder à la base de données Oracle.

  • Choisissez la table Oracle de la base XE à utiliser (Emp, sur le schéma Mac, dans notre cas), pour le mode d’accès aux données :


Remarquons que Table Name peut également être utilisée comme option d’accès si tout le contenu de la table (ou de la vue) est à charger :

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


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 Oracle Source en pressant sur OK.

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


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


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


  • Au sein des propriétés du composant SQL Server 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, vous pouvez choisir d’ignorer ou non les contraintes, les valeurs nulles, les identités. Le verrouillage de tables sera transactionnellement toujours présent pour les opérations de mises-à-jour afin d’assurer le respect des règles ACID. Vous pouvez également contrôler le nombre de lignes à charger par batch (par défaut toutes les lignes chargées seront traitées comme un simple batch) et la taille maximale des batchs d’insertion à valider.

  • 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 Oracle, 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 notre fidèle compagnon SQL Server Management Studio) :


Pour aller plus loin…

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

Publicités

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

  1. Serigne Diop dit :

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

    dans ma machine x86 windows 7 voici le chemin que j’ai trouvai
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v8.O

    que doit je faire?

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