J'ai été confronté ce matin à un problème sur l'éditeur de transformation « Recherche ».

En effet pour un de nos clients nous récupérons, sur plusieurs bases ACCESS crées par le logiciel de comptabilité « QUADRA », l'ensemble des données comptables que nous réinjectons toutes les nuits dans un DataWarehouse. Pour ce faire nous récupérons la liste des comptes de toutes les bases, que nous insérons dans une table commune « PlanComptable » du DataWarehouse, en testant si le compte n'existe pas déjà. Nous avons une clé primaire sur le numéro de compte.

Voici le schéma SSIS en image qui vaut mieux que 3heures de discours !

Seulement voilà, malgré une conception optimale(Et oui parfois faut bien s'envoyer des roses!), on peut rencontrer un problème du genre « Violation de clé primaire » lors de l'insertion des données.

En effet, lorsque l'on a sur une base un numéro de compte = 'AAEEEEAA' (inséré dans le DataWarehouse) et sur une autre base le compte = 'AaeeeeAA', et bien, oh la mauvaise blague!! Le lookup les considère différents alors que SQL SERVER les considère identiques. D'où la fameuse Violation de clé primaire !!

Plusieurs solutions se présentent à nous, soit on passe tous les numéros de comptes entièrement en majuscules ou entièrement en minuscules dans la requête directement, soit dans le paquet par le biais d'une colonne dérivée. 

Solution 1 :

select UCASE(Numero) as Numero, Type, Intitule from Compte

UCASE(ACCESS) est l'équivalent de UPPER en SQL SERVER

Solution 2 :

Il existe 2 méthodes pour planifier l'exécution d'un paquet SSIS:

1. Par le biais du serveur Intégration Services dans le répertoire MSDB.

2. Par le biais du système de fichiers

METHODE 1

Depuis Visual Studio 2005 ou 2008, sur le paquet SSIS, faire Fichier/Enregistrer une copie de « Nom du Paquet SSIS » en tant que... Puis renseigner: l'emplacement du package(dans notre cas Magasin de packages SSIS), le serveur(ici le serveur local) et le chemin d'accès au package « /MSDB/Nom du Package » :

Nom de l'étape : Mettre un nom évocateur,

Type: Dans notre cas mettre « Package SQL Server Integration Services »

Exécuter en tant que : Compte de service SQL Agent

Dans l'onglet Planification, vous pouvez planifier l'exécution journalière, hebdomadaire ou mensuelle sur les horaires et les dates voulues.

METHODE 2

Cette méthode ne nécessite pas l'enregistrement du paquet SSIS sur le serveur d'Intégration Services! Lors, de la création de l'étape du job, vous pouvez sélectionner dans source du package : Système de fichiers comme suit:

Quelle utilité me direz-vous?

Nous nous sommes posé la question lorsqu'un de nos clients a voulu lancer des mises à jour du DataWarehouse selon son grès. En effet, le client avait notamment besoin lors des périodes comptables, de faire de fréquentes mises à jour du DataWarehouse depuis ses bases SAP, afin d'intégrer les nouvelles écritures.

La solution dans notre cas, présentée ci-dessous, est envisageable car le remplissage SSIS du DataWarehouse est de très courte durée(de l'ordre de la minute). Les bases de données sources SAP et celles du Datawarehouse sont peu sollicitées, l'utilisateur lambda n'y voit que du feu. 

Fini les nombreux appels téléphoniques, afin de mettre à jour le DataWarehouse!

Première étape, il faut créer un job sur SQL SERVER afin de planifier le lancement du paquet SSIS de remplissage du DataWarehouse. Je ne m'étendrai pas sur la création d'un job exécutant un paquet SSIS qui fait l'objet d'un autre billet.

Une fois le job créé, que je nommerai dans notre exemple «DWRemplissage FR», je développe une procédure stockée sur le DataWarehouse, ici nommée «Update_bdd_reporting_FR», qui fait appel à ce job comme suit:

Maintenant la dernière étape consiste à créer un report qui, au niveau des données, appelle la procédure stockée «Update_bdd_reporting_FR» précédemment créée, puis de publier le report sur le portail web.

Il faut bien sûr restreindre l'accès à ce report, aux personnes susceptibles de lancer la procédure stockée.

C'est tout pour aujourd'hui!

En migrant un package SSIS d'un serveur 32 bits vers un serveur 64 bits vous vous rendrez très vite compte, si celui-ci se connecte à une "base de données" Access qu'il y a un p'ti problème. 

En effet, par défaut un package SSIS est paramétré pour s'exécuter dans le runtime 64 bits. Là où ça bloque, c'est que les drivers permettant la connexion à la base de données Access, les fameux Microsoft Jet 4 ne sont pas compatible pour l'environnement 64 bits...

Heureusement, il est possible de forcer SSIS à exécuter le package dans l'environnement de runtime 32bits. Pour se faire, il suffit dans les propriétés du projet de Business Intelligence de Visual Studio de mettre "False" dans le paramètre "Run64BitRuntime"

Voilà c'est tout! Mais cela permet d'exécuter le package en débuggage dans Visual Studio... pour l'exécution via un job schedulé c'est une autre histoire! 

En fait, pour simplifier, SQLServer fait appel au composant DTExec pour l'exécution des packages SSIS auquel il faut passer les paramètres tels que "/SQL "\PackageName" /SERVER "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E". Pour les systèmes 64 bits, l'exe se trouve dans le répertoire "<Disque système>:\Program Files\Microsoft SQL Server\100\DTS\Binn". Dans notre cas, pour exécuter le package dans de bonne condition, il faut faire appel à l'exécutable spécifique de la version 32 bits se trouvant dans le répertoire suivant :"<Disque système>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn".

Bien entendu il est possible de lancer le package via notre job schedulé... Le pire c'est que c'est assez simple... Dans la fenêtre de paramétrage de l'exécution du package, il suffit d'éditer la chaîne de paramètre et d'y ajouter les 3 caractères suivants : "/X86". La preuve en image :

Il ne reste plus qu'à lancer le package!

Dans tout projet d’intégration de données, les données partent d’une source, cette source pouvant par exemple être la base de données de votre ERP, un fichier Excel, ou bien un fichier XML, pour se retrouver éventuellement dans une base de destination, la plupart du temps, dans un projet de Business Intelligence, dans le datawarehouse.

A ce titre, SSIS intègre de nombreux outils de transformation du flux de données qui vont vous permettre de filtrer, d’enrichir, ou de façon plus générique, de traiter vos données. Parmi ces outils figurent celui de « fractionnement conditionnel ».

Pour illustrer les fonctionnalités de celui-ci, vous verrez comment il est aisé de réserver à des écritures comptables un traitement particulier selon qu’il s’agit d’une écriture de vente ou d’achat.

Les étapes suivantes décrivent un à un les composants qui illustrent cet exemple.

Cet exemple s’appuie sur une base de données ACCESS qui stocke les données du logiciel comptable, bien évidemment, il s’agit de connaître la structure des données pour savoir où collecter l’information.

Les propriétés du composant font état notamment de la requête SQL qui permet de ne sélectionner que les colonnes qui nous intéressent (Numéro de compte, Code Journal, Date d’écriture, Libellé, Débit et Crédit), et de ne prendre en compte que les écritures d’achat et de vente (seulement celui dont le premier chiffre du numéro de compte est 6 ou 7).

Cette étape est le cœur du sujet abordé dans ce billet, l’objectif de ce composant est de prendre en entrée un flux de lignes d’écritures, et de générer deux flux en sortie, le premier contenant les achats (écritures dont le numéro de compte commence par 6), le second contenant les ventes (numéro de compte qui commence par 7).

En sortie de cet élément, existent donc autant de flux de que conditions fixées dans la fenêtre de propriétés du fractionnement conditionnel. L’exemple montre ici que les flux d’enregistrements vont suivre des routes désormais totalement différentes, écriture dans un fichier texte.

Ceci étant, dans notre exemple, les flux des écritures d’achat et vente suivent des routes différentes mais il peut être concevable qu’après différents traitements spécifiques (par exemple  recherche dans une table annexe du nom du fournisseur pour les achats), ces flux aient besoin de se retrouver à nouveau dans un même flux pour bénéficier ainsi des mêmes opérations.

Cette fonctionnalité (Union) est intégrée à SSIS et fera l’objet d’un billet spécifique, avec bien sûr un exemple concret …

cet article se veut le premier d'une série consacrée à SQL Server 2005 et particulièrement la plateforme Integration Services. Ce billet est basé sur SQL Server 2005, mais bien évidemment, SQL Server 2008 reste au rendez-vous.

Juste pour mémoire, Integration Services inclus un outil graphique de construction de packages qui vont avoir pour vocation de mener des opérations d'intégration et de transformation de données. Le sujet du jour s'intéresse à un élément de transformation du flux de données qu'est "l'extraction de terme".

Si on travaille par exemple sur une application de gestion d'incidents, l'équipe support va saisir au fur et à mesure de ses interventions, des informations non structurées concernant les incidents. Le manager de cette équipe, va être intéressé dans ses attributions, par les éléments qui reviennent le plus souvent dans ces incidents (les produits, les modules, les personnes, etc ...). Pour aider notre manager dans son travail d'analyse, SSIS dispose d'un outil adapté, le fameux "extracteur de termes".

Sur différents critères, l'extracteur de terme va fournir une liste de termes, de phrases, avec pour chacun, un score d'occurence. Le manager pourra par exemple ainsi savoir que le terme "Microsoft Office" apparaît en tête de liste des termes les plus utilisés par les opérateurs du support.

alors comment ça marche ?

Pour l'exemple, utilisons simplement une table qui contient un champ "description" dans lequel les opérateurs ont saisi de manière non structurée les informations de support.

Notre package SSIS est très simple, il ressemble à ceci :

Le package est bien sûr composé d'une source de données, dont le flux va se déverser dans notre fameux "extracteur de termes". A noter au passage la conversion de données, due au fait que notre extracteur ne fonctionne qu'avec des types de données DT_WSTR ou DT_NTEXT.

Le paramétrage de l'extracteur de terme est relativement simple, il suffit de cocher le champ sur lequel va fonctionner l'extracteur. On peut par ailleurs enrichir notre extracteur en spécifiant dans l'onglet "exclusion" les termes qui devront être ignorés, ceux-ci étant positionné dans une autre source de données (base SQL, Excel, etc).

le flux est ensuite déversé dans notre exemple dans un fichier texte, mais on peut imaginer d'enchaîner des traitements beaucoup plus complexes de réaction par rapport à des niveaux n'occurence critique ou autre.

Pour plus de détails sur cet outil, n'oubliez pas MSDN : http://msdn.microsoft.com/en-us/library/ms141809(SQL.90).aspx