使用SSIS脚本组件读取Azure数据湖存储文件 [英] Azure Data Lake Store File read using SSIS Script Component

问题描述

感谢您的建议.
我的要求是，使用SSIS从ADLS读取json文件并加载到SQL表中

Appreciate your suggestions.
My Requirement is, Read json file from ADLS using SSIS and load into SQL table

实施: 我已经实现了在.Net Console应用程序中读取json文件内容的代码.这在控制台应用程序中工作正常.我在SSIS脚本组件中复制了相同的代码，但是在 AdlsClient.CreateClient.

Implementation: I have implemented the code to read json file content in .Net Console app. This is working fine in Console app. I copied the same code in SSIS Script component, but it throws "The type initializer for 'Microsoft.Azure.DataLake.Store.AdlsClient' threw an exception" exception in AdlsClient.CreateClient.

using Microsoft.Rest;
using Microsoft.Rest.Azure.Authentication;
using Microsoft.Azure.Management.DataLake.Store;
using Microsoft.Azure.DataLake.Store;
using Microsoft.Azure.DataLake.Store.AclTools;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

JObject results = new JObject();
        string applicationId = "<appid>;
        string secretKey = <secretekey>;
        string tenantId = <tenantid>;
        string adlsAccountName = "<ADLSNAME>.azuredatalakestore.net";
        ServiceClientCredentials creds = ApplicationTokenProvider.LoginSilentAsync(tenantId, applicationId, secretKey).Result;

AdlsClient adlsClient = AdlsClient.CreateClient(adlsAccountName, creds);
string srcPath = @"/InputFiles/1636274001230002_20180621_104427.json";
using (StreamReader readStream = new 
StreamReader(adlsClient.GetReadStream(srcPath)))
        {
            var p2Object = JsonConvert.DeserializeObject(readStream.ReadToEnd());
            results = JObject.Parse(p2Object.ToString());
        }

        date = ((string)results["eeData"][0]["startDate"]);
        machine = ((string)results["eeData"][0]["machineName"]);
        ppl = ((string)results["eeData"][0]["ppl"]);

推荐答案

问题出在第三方DLL的SSIS脚本组件中缺少引用路径.在控制台应用程序中，我可以安装NuGet软件包管理器.但是在SSIS脚本组件中，NuGet软件包安装失败，并且SSIS组件缺少参考.下面的代码将强制脚本组件编译器从给定路径引用DLL.

The issue is with the reference path missing in SSIS Script component for the 3rd party DLLs. In Console App I am able to install NuGet package manager. But in SSIS Script component, the NuGet package installation is failed and SSIS component is missing the reference. The below code will force the script component compiler to refer the DLLs from the given path.

将此代码添加到PreExecute()/Main()方法上方.

Add this code above PreExecute() / Main() method.

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

    static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    {
        if (args.Name.Contains("Newtonsoft.Json"))
        {
            return System.Reflection.Assembly.LoadFile(@"C:\Program Files\Microsoft SDKs\Azure\.NET SDK\v2.9\ToolsRef\Newtonsoft.Json.dll");
        }

        if (args.Name.Contains("Microsoft.Azure.DataLake.Store"))
        {
            return System.Reflection.Assembly.LoadFile(@"C:\Program Files\WindowsPowerShell\Modules\AzureRM.DataLakeStore\5.2.0\Microsoft.Azure.DataLake.Store.dll");
        }

        if (args.Name.Contains("Microsoft.Rest.ClientRuntime.Azure.Authentication"))
        {
            return System.Reflection.Assembly.LoadFile(@"C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.2\StorSimple\Microsoft.Rest.ClientRuntime.Azure.Authentication.dll");
        }

        if (args.Name.Contains("Microsoft.Rest.ClientRuntime"))
        {
            return System.Reflection.Assembly.LoadFile(@"C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.2\Services\Microsoft.Rest.ClientRuntime.dll");
        }
       if (args.Name.Contains("NLog"))
        {
        return System.Reflection.Assembly.LoadFile(@"C:\Users\<user>\source\repos\Integration Services Project2\NLog.dll");
        }

        return null;

       }

这篇关于使用SSIS脚本组件读取Azure数据湖存储文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！