SSIS Script Task : Charger des « custom assemblies » sans devoir les déployer & SSIS JSON API Datasource Script Component

Devant le constat qu’il n’existe toujours pas de DataSource #REST et #JSON dans SSIS, me voici partie à faire une source de données en script task. Si cela ne pose pas de problème pour la connexion à l’API Rest, ce n’est pas la même histoire en ce qui concerne le traitement du JSON.

Pour récupérer les données de l’API je peux utiliser la référence : System.Net.Http qui fait partie du framework et ne nécessite donc pas de déploiement sur le serveur SSIS. Pour le Json, il est beaucoup plus simple d’utiliser Newtonsoft.Json.Linq qu’il faudrait donc normalement déployer sur le serveur SSIS (enregistrement dans la GAC). Or, j’essaie toujours d’éviter de livrer des dépendances avec mes packages SSIS, en effet cela crée des complications en cas de migration de serveur (« tiens les packages ne marchent plus… il fallait installer quoi déjà ? »), c’est pourquoi mon #Dev-.net préféré Fabrice Michellonet  m’a proposé de charger la dll de Newtonsoft.Json en « Embedded Resource » dans mon projet ce qui permet de ne plus avoir à l’installer sur le serveur de destination. Voici donc la marche à suivre basée sur la création d’une source de données API REST JSON.

A – Intégration d’un DDL sans besoin de déploiement dans la GAC en 5 étapes:

1. Première étape convertir la DDL en base64 … Mais pourquoi diable me direz-vous, simplement car SSIS stocke le code de votre script task dans le fichier XML de votre package, vous avez peut-être remarqué cela lorsque vous affichez le code de votre package.
   Il est donc nécessaire d’encapsuler uniquement de l’ASCII, d’où l’encodage en base64 … Pour effectuer cette conversion, vous pouvez utiliser n’importe quel utilitaire qui permet de prendre un fichier et de le convertir en base64. Dans mon exemple, il s’agit de Newtonsoft.dll que j’ai donc chargé sur le site suivant, j’ai ensuite copié/collé le résultat dans un fichier texte que j’ai enregistré en Newtonsoft.dll
   
   /!\ Sans cette étape vous aurez un florilège d’erreurs : « visual studio hexadecimal value 0x00 is an invalid character »
2. Importez le fichier crée en étape 1 dans notre script task ou script component, dans mon exemple il s’agit d’un script component en source de données.
   
   
3. Chargez la DLL avec le code suivant (merci infiniment Fabrice !)

   static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    {
    // retrieve just a name of this assembly
    var assemblyName = args.Name.Split(',')[0];
    
    var executingAssembly = Assembly.GetExecutingAssembly();
    var resourceName =
    executingAssembly.GetManifestResourceNames()
    .FirstOrDefault(mr => mr.Contains(assemblyName));
    if (!string.IsNullOrWhiteSpace(resourceName))
    {
    var base64StringStream = executingAssembly.GetManifestResourceStream(resourceName);
    var base64String = new StreamReader(base64StringStream).ReadToEnd();
    var dll = Convert.FromBase64String(base64String);
    return Assembly.Load(dll);
    }
    
    return null;
    }

   Dans le cas d’un script component, il est nécessaire d’effectuer l’appel de la façon suivante :

    static ScriptMain()
    {
      AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;
    
    }

   Le tour est joué, votre DLL sera chargée depuis la version encapsulée dans votre script task / component.

4. Pensez bien aux conséquences de vos actes, en effet, cette DLL sera mise à jour uniquement si vous refaites les étapes 1 et 2 avec une nouvelle DLL, cela sera à effectuer sur chaque script task/component que vous aurez développé avec cette méthode. Cela ne convient donc pas à des DLL qui seraient fortement évolutives.

N’hésitez pas à consulter cet article en anglais qui reprend (avec quelques différences sur le stockage de la DLL) ce qui a été fait ci-dessus.

B – Il ne REST plus qu’à se connecter à l’API et à parser les résultats.

Ci-dessous le code utilisé dans mon « datasource script component ».

public override void CreateNewOutputRows()
 {
 var credentials = new NetworkCredential(this.Variables.ServiceNowUserName, this.Variables.ServiceNowPassword);
 var handler = new HttpClientHandler { Credentials = credentials };
 HttpClient cli = new HttpClient(handler);

 string url = this.Variables.RootServiceNowURL + this.Variables.UrlParams + this.Variables.FilterDate;
 var res = cli.GetAsync(url)
 .Result;
 res.EnsureSuccessStatusCode();
 var json = res.Content.ReadAsStringAsync().Result;

JObject jobj = JObject.Parse(json);

foreach (var item in jobj.SelectTokens("result[*]"))
 {
 Output0Buffer.AddRow();
 try { Output0Buffer.sysid = item.SelectToken("sys_id").Value<string>(); } catch (Exception e) { Output0Buffer.sysid = null; }
 try { Output0Buffer.active = item.SelectToken("active").Value<bool>(); } catch(Exception e) { Output0Buffer.active = false; }
//... et ainsi de suite sur toutes les colonnes à récupérer, il y a un peu de travail à ajouter pour la gestion des Exceptions

}
}