Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Un exemple pratique, trouver le pays d’un utilisateur par son IP (2 sur 3)

Dans cette seconde partie nous allons faire un usage intensif de SQL Server Integration Services (SSIS). On pourrait intégrer les données de bien d'autres manières, mais le fait est que les fichiers sources n'ont pas un format très standard pour les sources de données fichiers plats à la sauce Microsoft et que j'ai besoin de transformer les IP des fichiers en données binaires pour plus tard.

D'autre part SSIS, permet nativement la connexion sur un serveur FTP ce qui va nous servir à télécharger les fichiers source avant de les intégrer.

Etape 1 : Charger les fichiers sources via FTP

Cette étape est assez simple, on a une tâche FTP par fichier chargé, ces 5 tâches se partagent la même source. La conséquence de ce partage de connexion, est que l'on ne peut pas les exécuter simultanément… Les temps de téléchargement sont court, çà ne devrait pas poser de problèmes.

 

Etape 2 : Sélection des lignes de données

Pour cette opération, étant donné que les données n'ont pas un nombre uniforme de colonne sur chaque ligne, vous aurez généralement 2 choix pour traiter la source.

La première solution consiste à mettre une source de fichier plat avec une colonne unique et de l'éclater via une transformation en script (.net). Personnellement je n'aime pas trop cette méthode, car assez peu souple et elle souffre du manque de possibilité de débogage de .Net au sein de SSIS.

La seconde solution repose sur un pré-découpage des fichiers. Généralement on prend un fichier plat on définit une ou 2 colonnes sources, et on effectue un split sur la valeur de ces colonnes. Enfin on remet le tout dans un fichier plat qui sera traité par la suite. L'intérêt de la solution d'utiliser les tâches standard de SSIS, on pourra dire que c'est un peu plus lent, certes, mais le temps de traitement qui se fait intégralement en mémoire est assez rapide ! D'autre part on pourra on passant intégrer les 5 fichiers sources en 1 seul (boucle for each).

 

Etape 3 : Intégration avec conversion des données binaires

Dernière étape, on lit notre unique fichier source en réalisant au passage quelques conversions et on pousse le résultat dans la ou les tables SQL Server de destination.

Ici le choix des conversions s'est porté sur .Net, côté SQL Server l'appel d'une fonction scalaire, qui plus est, écrite en T-SQL serait assez inefficace. De plus côté .Net il y a une classe qui réalise la conversion des IPv4 et IPv6 de manière très efficace, sans plus de code… Autant en profiter !

 

Au niveau des tables, la destination peut être de 2 types : SQL Server ou OLEDB (je passe volontairement sur les autres).

  • Le type SQL Server, permet de mettre en œuvre le chargement en bloc (qui permet de limiter l'impact sur le journal de transaction en fonction du mode récupération de la base de données). Mais ce type de destination nécessite d'être administrateur local de la machine sur laquelle s'exécute SSIS. Cette limitation assez stricte fait que ce type de destination est rarement utilisable. En cas d'erreur sur le chargement la seule actions que vous pouvez prendre et le nombre maximum de lignes en erreur que vous acceptez.
  • Côté OLEDB, ce type de limitation n'est pas présent, mais du coup adieu au chargement en bloc (sauf en magouillant avec le table lock et un traceflag sur le serveur). Ce type permet aussi de récupérer les erreurs de chargement dans la destination (erreurs liées à la présence de contraintes, etc.) et de pourquoi pas les charger dans une table de log.

Dans le cas présent le risque d'erreur est quasi inexistant dans l'insertion et je fais des tests locaux, donc pas de soucis. Dans votre environnement n'hésitez pas à changer les destinations, la structure de table restant la même.

Dernier point je réalise un autre split à partir de la source, je cherche à isoler les IPv6 et IPv4 dans des flux séparés.

Le package

Rien de bien compliquer pour paramétrer ce fichier DTSX, généralement j'ai l'habitude de mettre toutes les répertoires de travail de SSIS dans des variables. Vous n'avez qu'à les modifier avec vos propre valeurs et à modifier la source de données

Le package SSIS (dtsx) est joint à ce billet. Attention il n'est pas parfait, je ne prétends pas être un expert SSIS.

La suite

Ce qui reste à faire, est de loin la partie la plus compliqué, car maintenant il faut requêter la table qui contient la localisation des IP…

Soit la table de la dernière fois:

  http://blogs.codes-sources.com/christian/archive/2010/06/10/sql-server-un-exemple-pratique-trouver-le-pays-d-un-utilisateur-par-son-ip-1-sur-3.aspx

Et les 2 types de requêtes que je peux vouloir faire :

  • Déterminer le pays, d'une IP par exemple au travers d'une fonction scalaire
  • Mettre à jour une table existante avec les IP de login avec leur pays respectifs

De quels index je vais avoir besoin et comment dois-je écrire mes requêtes ?

Bon chargement…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié jeudi 17 juin 2010 14:24 par christian
Attachment(s): Package.zip

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01