[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.