Articles Tagués ‘SSIS’

Déplacer une arborescence de packages SSIS entre serveurs

26 octobre 2012

Le problème que j’ai rencontré aujourd’hui, c’est : “comment migrer tout un projet SSIS d’un serveur à un autre”.

Je suis en SQL Server 2008 R2 et donc je ne bénéficie pas de la notion de « projet » dans le catalogue SSIS. J’ai donc plus de 200 packages répartis dans une arborescence à déplacer.

Ces packages ont été déployés tout au long de la vie du projet et en tant qu’admin, je ne souhaite pas solliciter l’équipe de développement pour me packager un script de livraison qui reprendrait tous les packages.
Après tout, le changement d’un serveur pour un autre, c’est une opération de DBA, pas de développeur donc je dois trouver une solution.

Il y a un utilitaire fourni avec SQL Server qui permet de manipuler des packages : DTUTIL.exe

Il permet de batcher des opérations sur un catalogue SSIS (2008 / 2008R2) donc cela semble une bonne piste.

Pour déplacer un package, la ligne de commande ressemble à cela :

dtutil /sql \chemin\package /SourceServer SERVSOURCE /copy SQL;\ chemin\package /DestServer SERVDEST

Le problème, c’est qu’il faut que j’écrive plus de 200 fois cette ligne de commande, avec les bons chemins.

Comme je suis faignant, je vais passer par les méta-données de MSDB et en particulier avec les tables sysssispackages et sysssispackagefolders.

Il va falloir faire du hiérarchique pour reconstruire l’arborescence, pour cela, un bonne CTE des familles (cc Philippe).

Voici le script :

;with PackagesHierarchy(folderId, ParentFolderId, FolderPath) as 
(
  select folderid, cast(null as uniqueidentifier), 
    cast('\'+foldername as varchar(max)) as FolderPath       
  from sysssispackagefolders
  where foldername = 'ProjectFolder'

  union all
  
  select sysssispackagefolders.folderid, sysssispackagefolders.parentfolderid,
   CAST(PackagesHierarchy.FolderPath +'\'+ sysssispackagefolders.foldername as varchar(max)) as FolderPath
  From sysssispackagefolders
  Inner Join PackagesHierarchy
    on sysssispackagefolders.parentfolderid = PackagesHierarchy.folderid 
)

Select 'dtutil /sql ' + FolderPath + '\' + name + ' /SourceServer ServSRC /copy SQL;' + FolderPath + '\' + name + ' /DestServer ServDEST'
From sysssispackages
Inner Join PackagesHierarchy
  On PackagesHierarchy.folderId = sysssispackages.folderid

Vous copiez tout cela dans un fichier BAT et let’s go !

Conclusion

Certains diront qu’il est préférable de déployer en mode Fichier. Moi, je ne prends pas parti Clignement d'œil

Enjoy.

Tags:,
Publié dans Integration Services | 2 Commentaires »

Paramétrer à l’exécution le logging des packages parent et enfant

24 janvier 2012

–merci à Stéphane de mon équipe qui a pris le temps de troubleshooter le problème remonté par un lecteur et surtout d’en faire une KB–

Introduction

Un package parent appelle un package enfant. Chacun déclare son LogProvider avec sa configuration. La configuration du package enfant est récupérée dans le package Parent.

Etant donné que l’initialisation du logging SSIS s’effectue avant les événements de validation et de chargement de la configuration, comment peut-on faire pour paramétrer à l’exécution le provider utilisé par le logging parent et enfant évidemment ?

Situation

Soit un package « parent.dtsx » qui appelle via une tâche « Execute package Task » un package « child.dtsx ».

clip_image001clip_image002

Nous souhaitons logguer les événements parents et enfants dans un fichier définit à l’exécution.

Solution

Pour faire court, il ne faut pas mettre les Log Provider au design-time mais à l’exécution via une option de DTExec (ou du SQL Agent, c’est pareil). Par contre, la chaine de connexion du log doit exister dans le package.

Configuration des packages

Désactiver le logging du package parent et des packages enfants (Menu SSIS > Logging)

Dans le package parent, ajouter un gestionnaire de connexion (Connection Manager) correspondant au type de log souhaité : File Connection pour logguer dans un fichier, OLEDB connection pour logguer dans une base de données, etc.

Dans notre exemple, le File Connection Manager « logging.txt » est ajouté. Il sera la destination de nos logs.

clip_image004

Une variable nommée « loggingVar » est ajoutée dans le namespace du package parent.

clip_image005

Dans les propriétés du connection manager logging.txt, une expression est créée mappant la propriété « connection string » du connection manager vers la variable loggingVar.

clip_image006

Exécution

L’exécution du package se fait avec la commande dtexec standard en ajoutant le flag de logging et en précisant son provider.

 dtexec /f parent.dtsx /L "DTS.LogProviderTextFile;logging.txt" /SET \package.Variables["User::loggingVar"].Value;parent_and_child.log

Dans cet exemple, le log est placé dans le fichier "parent_and_child.log" situé dans le même répertoire que le package parent.

La commande correspond à :

  • /f: le package (du système de fichier) à exécuter : parent.dtsx
  • /L : active le logging. L’argument suivant détermine le provider et le Connection Manager utilisé pour le logging séparé par un point-virgule. Dans notre exemple, il s’agit du provider pour fichier DTS.LogProviderTextFile et du Connection Manager logging.txt.
  • /SET : pour affecter une variable de package. La variable affectée est loggingVar.

L’affectation de la variable par le SET se fait avant l’initialisation du logging dans ce cas. Le Connection Manager est bien affecté au fichier parent_and_child.log, puis le logging s’initialise sur le provider fichier texte sur le Connection Manager.

Le LogProvider fourni à DTExec est bien transmis aux enfants.

Le fichier de log obtenu est le suivant :

clip_image008

Pour logguer dans une base de données, il faut changer le connection manager « logging.txt » par un connection manager de type adéquat (oledb connection manager, par exemple) et changer dans la commande dtexec le provider par DTS.LogProviderSQLServer.

Encore une fois, tous les honneurs à Stéphane.

Tags:
Publié dans Integration Services | 2 Commentaires »

Les nouveautés SSIS dans SQL Server 2012 : le webcast

23 janvier 2012

Et voila, après plus d’un mois, la vidéo de ma session SSIS 2012 avec Thomas aux Journées SQL Server est enfin en ligne .

http://www.microsoft.com/fr-fr/showcase/details.aspx?uuid=637027bd-4a9b-4f4b-b7e4-faab17894ed3

image

Merci à Hugues de ne pas avoir renoncé !

Tags:, ,
Publié dans Integration Services | Poster un commentaire »

DTSConfig bug by design

27 novembre 2011

(ou “Le post de l’After Gameworks Party”)

configuration dtsconfig ssis

 

Un post qui est bloqué dans ma stack depuis quelques semaines déjà.

 

Le sujet est simple, il traite de l’ordre de prise en compte de la configuration d’un package SSIS.

Si vous voulez configurer une variable dynamiquement sur votre environnement d’exécution, plusieurs choix s’offrent à vous :

  • le mettre en dur dans votre package avant déploiement (beurk)
  • utiliser un fichier de configuration (.dtsconfig)
  • utiliser une configuration qui vient d’une base de données
  • utiliser une directive /SET à l’exécution (disponible dans SQL Agent et en ligne de commande)

Note : Personnellement, j’utilise principalement le dernier choix.

Le problème vient de l’utilisation d’un DTSconfig. Prenons un exemple :

  • dans votre package vous référencez un fichier de configuration qui se trouve dans c:\config-dev.dtsconfig
  • en passant en production, vous indiquez dans le SQL Agent votre configuration de production c:\config-prod.dtsconfig
  • en production vous avez quand même un fichier config-dev.dtsconfig à la racine de C:
  • A l’exécution, c’est le fichier de configuration de DEV qui est pris en compte !

Mais pourquoi ? Regardons un peu la documentation :

Using the /ConfigFile option to load run-time configurations for the same property values that are also set by design-time configurations : Has no effect. The new values are overwritten when the utility reloads the design-time configurations after it applies the run-time options.

Ce paragraphe est tiré d’une page appelée Behavior Changes to Integration Services Features in SQL Server 2008 R2.

En fait, depuis 2008 R2, dtexec recharge le fichier se trouvant dans le chemin initial (celui utilisé pendant le développement dans BIDS) après avoir chargé tous ceux qu’on lui donne explicitement.

Oui, c’est absurde mais c’est By Design.

Evidemment, si le fichier config-dev.dtsconfig ne se trouve pas en production, aucun problème mais si vous travaillez avec la même arborescence (lecteur compris) ou si vous travaillez avec des UNC, soyez très vigilants.

Ni moi ni aucun de mes clients n’ont rencontré de soucis mais Christophe y a été confronté chez un de ses clients.

Annexe :

Dans SQL Server 2012, si vous migrez vos package sous le nouveau mode d’exécution, il n’y a plus de DTSConfig, donc plus de souci.

Oui, j’ai bien dit qu’en 2012 il n’y a plus de DTSConfig. Venez voir ma session sur SSIS 2012 aux Journées SQL Server pour en savoir plus.

 

Pour l’anecdote, ce poste vient d’une discussion très tard après une soirée du SQL PASS Summit dans la chambre d’hôtel que je partageai avec Christophe. Cette discussion s’est vite transformée en débat animé mais se termine sur la conclusion suivante : ce n’est pas un bug puisque c’est documenté, c’est donc une feature…by design Sourire

 

PS : tu vois Christophe que j’ai blogué sur le sujet.

Tags:, , ,
Publié dans Integration Services | 5 Commentaires »

Les nouveautés “méconnues” de SSIS 2012

14 octobre 2011

Speaker : Matt Masson (équipe de développement SSIS et camarade d’ATE)

Je suis arrivé et Matt présentait déjà un Data Flow. Je n’ai pas tout compris car il a fallu accrocher les wagons mais en vrac :

  • Facebook Source : nouveau composant, 3rd party ou développement custom ?
  • composant StreamInsight : utilisation de StreamInsight pour détecter des patterns directement dans un flux SSIS; j’adore mais je ne sais pas d’où il l’a sorti
  • composant MDS :  utilisation des informations de vos référentiels MDS (ici un Lookup)

La session a ensuite démarré… c’était juste pour occuper l’audience en attendant. Ce qui fait que je ne sais pas si ce sont des futures fonctionnalités, de la RTM, du SP1, de la prochaine version…

Place aux fonctionnalités annoncées pour la RTM

Je ne vais pas revenir sur les grandes améliorations de SSIS sauf par ce court résumé :

  • amélioration du développement : corrections des bugs, nouveaux designers, nouveaux composants, etc.
  • amélioration de la production : serveur SSIS, notion de projets, logs automatiques, etc.

Je vous laisse consulter mes précédents posts et les articles complets que je vais faire sur le sujet.

Je vais en revanche pointer les fonctionnalités présentées en session et il y a de quoi faire. Tout n’est pas dans la CTP3 donc je ne peux pas vous faire de démo.

 

Change Data Capture

Pas besoin de présenter le CDC apparu en 2008 (j’avais fait un article sur le sujet, il est encore dispo ici) ? En 2 mots, c’est un moyen d’obtenir rapidement les modifications d’une table dans un intervalle de temps. Les modifications sont stockées dans des tables systèmes et, à la différence de la vieille méthode des triggers, c’est le moteur qui gère tout.

Tout cela est très utile pour un ETL qui doit transférer ces modifications dans un DataWarehouse (ou tout autre système qui en aurait besoin).

Avec SQL Server 2012 arrive le CDC pour Oracle et le support du CDC dans SSIS

  • CDC Oracle : un service Windows (et une application de gestion) s’occupe de récupérer les changements depuis une base Oracle
  • SSIS CDC Control Task : permet d’effectuer des opérations sur le CDC comme récupérer les bons LSN
  • SSIS CDC Source : permet de récupérer sous la forme d’un flux de données les modifications de données
  • SSIS CDC Splitter Transformation : comme le composant SCD, il permet de différencier dans des flux différents les ajouts, les modifications et les suppressions

Tout cela rend le CDC encore plus attrayant et je suis content de l’ajout de ces composants.

 

ODBC source/destination

Avec l’arrêt d’OLEDB et un revirement vers ODBC, il est évident que les composants sources et destinations allaient aussi suivre le mouvement.

Microsoft annonce de meilleures performances sans grandes explications. J’attends de voir car ça ressemble plus à la stratégie “je m’accroche aux branches”.

En tout cas, les composants sont là et ils fonctionnent Sourire.

 

Shared Connection Manager

J’en avais déjà parlé, la gestion des connexions a été grandement améliorée. On peut promouvoir une connexion au niveau projet (et inversement), mettre offline une connexion, etc.

Lors de la session, j’ai pu voir des petits icones apparaitre pour indiquer si une connexion utilise une expression ou si elle est offline.
Cela ne marche que pour les connexions malheureusement. Il vous reste BIDSHelper qui apporte ce genre de confort de façon générale.

 

Flat Files

Je n’ai pas encore testé et la démo a été extrêmement rapide mais 2 améliorations arrivent sur les fichiers plats :

  • Meilleure gestion des Text qualifier (quand ils sont imbriqués)
  • Nombre de colonnes variable (ie. meilleure gestion des séparateurs)

 

Code XML

Il est maintenant beaucoup plus propre et plus court. Il est enfin lisible ; des nœuds ont été transformés en attributs et le positionnement a été revu et placé à la fin.

Cela permet une chose : le Merge de packages

Enfin on va pouvoir travailler à plusieurs sur le même DTSX, en multiple checkout dans TFS avec un merge de fichiers XML lisible.

Attention, ce n’est pas non plus trivial et des erreurs sont encore possibles.

 

Debug des Script Components dans le Data Flow

Je crois que tout est dit, sauf peut-être : merci Sourire

 

Pilotage via Poweshell

La gestion du serveur (création, exécution, administration) se fait entièrement avec des procédures T-SQL. Cependant, l’équipe SSIS a développé une API en .NET au dessus (qui lance donc du T-SQL avec ADO.NET) et cette API est Powershell-isable.

Donc vous pouvez administrer, déployer, gérer votre serveur SSIS en PowerShell.

 

Data Viewers –> Data Taps

Les Data Viewers que vous mettez en développement pour voir ce qui passe entre les composants sont maintenant disponibles à l’exécution sur le serveur et s’appellent des Data Taps.

En plus des logs automatiquement gérés par le serveur, il est possible (pour l’administrateur uniquement) de placer ces Data Taps sur les Paths (ex : \Package\aDFTtask\Paths[SourceSQL.ConversionDate]) de vos Data Flow.

Les données sont dumpées dans un fichier CSV et rassurez vous, il est possible de limiter les colonnes et le nombre de lignes exportées.

 

Integration Services Dashboard

Dans Management Studio, il y a depuis quelques temps déjà des rapports sur les différents éléments aidant grandement à l’administration (mon préféré est Disk Usage Sourire).

Avec le logging automatique du serveur SSIS, SSMS nous offre des tableaux de bord qui exploitent ces données.

On peut donc en quelques clics voir, entre autres :

  • les packages exécutés
  • les packages en erreur
  • les historiques d’exécution
  • les temps d’exécution des différentes partie (avec historisation)
  • etc.

 

Correlated Logging

Afin de profiter des logs automatiques avec votre propre log métier (custom logging), vous avez à disposition une variable (je ne me souviens plus du nom mais c’est du genre @User::BatchID) pour pouvoir corréler par la suite les différents logs.

Dependency Services // Projet Barcelona

Pour finir, je vais parler brièvement du projet Barcelona.

J’ai pu faire des démos (MS Days Toulouse) et parler de Dependency Services sorti avec de la CTP1. Dependency Services est sorti de Denali avec la CTP3 pour vivre sa propre vie.

L’idée reste la même : tracer toutes les dépendances des données entre les composants d’une solution BI (sources, SQL, packages SSIS, rapports, modèles PowerPivot, etc.).

L’objectif est de pouvoir répondre à des questions comme : que ce passe t-il si je supprime cette colonne ?

Je suis maintenant en relation avec l’équipe de développement car j’adore le projet et donc je ne vais pas tarder à vous en parler plus en détail.

Le projet est prévu pour sortir juste après SQL Server 2012 et en attendant, suivez ce blog ou celui de l’équipe : http://blogs.msdn.com/b/project_barcelona_team_blog/

Tags:, , ,
Publié dans Integration Services | Poster un commentaire »

BIDS 2010

22 juillet 2011

Une feature très attendue pas les développeurs décisionnels, c’est l’alignement des outils de développement avec Visual Studio 2010 ;  plus besoin d’installer les 2 versions.

Avec cet alignement, on voit quelques améliorations notoires du côté de SSIS. Je suis pourtant fan de cet ETL mais je dois reconnaitre que certains assistants ne sont pas des plus aboutis ou compliqués à utiliser.

La première amélioration est très visuelle. On la remarque dès l’ouverture d’un package : le rendu graphique est grandement amélioré.

image

Cela est dû au designer développé en WPF dans cette version. C’est plus joli (les icônes ont été un peu relookés) et il est possible de zoomer (car le rendu est vectoriel).

 

Autre amélioration que j’ai grandement apprécié dès l’ouverture d’un package, c’est une meilleure gestion des connexions.

On a tous un jour maudit SSIS parce qu’il teste les connexions à l’ouverture d’un package. 
Le comportement est normal sauf que SSIS teste chaque connexion de chaque composant et attend la fin du LoginTimeout pour s’apercevoir que la base ou le serveur n’est pas accessible.
Sur un gros package, cela peut prendre plusieurs minutes.

Les développeurs SSIS connaissent donc le mode offline mais qui désactive toute la connectivité du projet.

Avec Business Intelligence Developement Studio  2010, la connectivité est testée une bonne fois pour toute à l’ouverture (si le serveur ne ping pas, la main est rendue aussitôt) et il est possible de mettre Offline juste une connexion.

image[7]

Autres améliorations que j’ai noté en vrac dans le développement de package DTSX :

  • le Ctrl+Z enfin implémenté
  • les annotations avec un retour à la ligne automatique
  • les tooltips précisant les types dans le composant de Lookup
  • l’ajout de “groupes” dans les Data Flow Task
  • la possibilité de promouvoir une connexion au niveau projet

Je ferai un post (voire un article complet) sur le moteur SSIS qui voit de nombreuses améliorations importantes.

 

Migration

Avant de terminer, quelques mots sur la migration de package.

J’ai converti 2 projets SSIS 2008 sans problème. J’ai rencontré un effet de bord dans quelques Data Flow Task où le designer n’arrive pas à positionner les composants (donc tout se cale en haut à gauche).

Tags:, , ,
Publié dans Integration Services | 2 Commentaires »

Mon Lookup ne matche plus

24 juin 2011

Un nouveau post rapide pour une problématique que vous pouvez rencontrer avec les Lookup Transformations dans SSIS.

Problème : les données semblent égales et pourtant le Lookup n’arrive pas à matcher. Fait perturbant, si vous passez le mode de cache en No Cache, ça marche…

image

Solution :

Le problème vient des types de données. Dans mon exemple, j’ai un NUMERIC(6,0) d’un côté et un NUMERIC(18,2) de l’autre.

En mode cache, c’est SSIS qui fait la comparaison et sa représentation interne dans son cache et ses buffers est fortement liée par les types. Ainsi la comparaison “binaire” (certes optimisée) échoue et les données ne matchent pas.

En mode “No Cache”, c’est SQL Server qui fait la comparaison et là intervient un transtypage (CAST) implicite. Dans notre exemple, la comparaison d’un NUMERIC(6,0) et d’un NUMERIC(18,2) fonctionne et c’est pour cela que le Lookup matche les données.

Mon exemple utilise des types numériques mais on a le même problème avec les chaines de caractère en fonction de la COLLATION (et du Codepage sur SSIS).

PS : notez également que le Lookup est Case Sensitive !

Conclusion :

Soyez vigilant sur les types et essayez de coller au mieux (pour faire simple, utilisez exactement les mêmes).

Tags:,
Publié dans Integration Services | Poster un commentaire »

SSIS – Erreur –1071607685 – No Status Available

29 juin 2010

 

Dans vos packages Integration Services, si vous êtes confrontés à cette erreur… :

Error Code : –1071607685
Error Column : 0
Error Desc : No Status Available

…ne cherchez pas pendant des heures. Regardez en premier lieu dans les composants destination et désactivez les options Fast Load.

FastLoad

Cette option fait une insertion en mode BULK et par défaut en batch de 10 000 enregistrements. Si au moins l’un de ceux là est en erreur (doublon, clé étrangère manquante, valeur invalide, etc.), c’est le batch qui part dans le flux d’erreur et aucune description d’erreur n’est disponible.

La première chose à faire pour déboguer est de désactiver l’option pour avoir des INSERT ligne à ligne. Vous verrez donc l’erreur qui fait planter le batch.

Mais alors comment faire dans la vraie vie. Plusieurs idées :

  1. Dans le cadre d’un ETL de DataWarehouse, il faut faire toute les vérifications AVANT l’insertion (c’est aussi une des raisons pourquoi on ne met pas de Foreign Key). Votre DataFlow doit garantir la validité des données (et prendre les actions correctrices nécessaires)
  2. Utiliser un paramètre DEBUG pour désactiver l’option à la volée en production (il faudra passer par une variable et un conditional split)
  3. Utiliser la technique du double-essai. On fait d’abord une première insertion en mode FastLoad, on redirige la sortie d’erreur sur un INSERT normal. Ainsi, dans (normalement) la majorité des cas, vous ferez du Bulk et le deuxième essai permet de limiter les dégats d’un batch qui part à la poubelle (et surtout vous donnera les détails de l’erreur).

Tags:, ,
Publié dans Integration Services | Poster un commentaire »

Remplir un champ varbinary(max) avec des fichiers d’un répertoire

2 avril 2010

Petit challenge personnel il y a quelques jours, on me demande comment mettre le contenu d’un répertoire dans une table en base de données (pour préparer une démo à la BDC).

Evidemment, étant entouré de développeurs, tout le monde a pensé à une application console avec de l’Entity Framework ou du Linq2SQL. Mais le temps de se décider, j’avais implémenté la solution avec SSIS, outil parfaitement adapté pour l’intégration de données ;) .

La piste suivie est une tâche de DataFlow qui s’appelle Import Column qui sert à injecter des fichiers dans un flux de données.

image

Pour le faire fonctionner, il nous faut dans le flux d’entrée une colonne avec le nom du fichier. Donc ma première tâche dans le Control Flow est de remplir une table avec les chemins complets. Pour cela, j’utilise un Foreach Loop qui itère sur les fichiers du répertoire et fait un Insert SQL.

image

Ensuite, dans le DataFlow Task, je récupère ces noms de fichiers, je les envoie dans la tâche Import Column et j’enregistre cela dans ma base avec une Destination SQL.

Petit truc pour configurer le composant Import Column (qui n’est pas du tout intuitif), il faut :

  • Créer manuellement l’Output Column qui va recevoir le binaire. Elle se mettra automatiquement au bon type (DT_IMAGE, l’ancien nom de varbinary(max) :( )
  • Récupérer l’ID de cette colonne

image 

  • Lier cette ID à la colonne d’entrée

image

Et voila, une fois ce tips connu, tout est OK, vous obtenez un lot pour intégrer des fichiers dans une base très rapidement.

 

Temps de dev : 5 minutes

Le code source est disponible sur demande.

 

PS : il faut juste avoir une instance SSIS sur la machine qui exécutera le package.

Tags:,
Publié dans Integration Services | Poster un commentaire »

Suivre

Recevez les nouvelles publications par mail.

Joignez-vous à 325 followers