带有 SSIS 的 Azure SDK

Question

我正在研究使用 SSIS 2019 脚本任务连接到 Azure 数据湖存储的不同选项，其中一个选项是使用 Azure .NET SDK 。但是，我很难在脚本任务中使用它。我正在尝试让它与下面的代码一起工作，但还没有成功。

C# 代码:

 static string path = null;
    
 static ScriptMain()
 {
     AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
 }
 
 static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
 {
     if (args.Name.Contains("dllname"))
     {
         return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "dllname.dll"));
     }
     return null;
 }

请指导我通过 SSIS 脚本任务连接到 Azure。

C# 代码:

static async Task Main(string[] args)
        {
            //Connect to Azure Storage Account
            var applicationId = "applicationId";
            var secretKey = "secretKey";
            var tenantId = "tenantId";
            var storageAccountName = "myStorageAccountName";
            var filesystem = "myFilesystemName";
            var mypath = "my/AzureFolder/Path";

            var client = DLStorageManagementClient.CreateClient(applicationId, secretKey, tenantId, storageAccountName);

            var isFileSystemCreated = await client.CreateFilesystemAsync(filesystem);

            var isDirectoryCreated = await client.CreateDirectoryAsync(filesystem, mypath);

            string tmpFile = Path.GetTempFileName();
            string fileName = HttpUtility.UrlEncode(Path.GetFileName(tmpFile));
            File.WriteAllText(tmpFile, $"this is sample file content for {tmpFile}");

            var isFileCreated = await client.CreateFileAsync(filesystem, mypath, fileName, new FileStream(tmpFile, FileMode.Open, FileAccess.Read));

            var stream = new MemoryStream();

            var isFileDownloaded = await client.DownloadFileAsync(filesystem, $"{mypath}/{fileName}", stream);

            if (isFileDownloaded.IsSuccessStatusCode)
            {
                var contentString = UTF8Encoding.UTF8.GetString(stream.ToArray());

                Console.WriteLine(contentString);
            }

            var isFileDeleted = await client.DeleteFileOrDirectoryAsync(filesystem, mypath, true);

            var isFileSystemDeleted = await client.DeleteFilesystemAsync(filesystem);
        }

错误:

Error   CS1983  The return type of an async method must be void, Task, Task<T>, a task-like type, IAsyncEnumerable<T>, or IAsyncEnumerator<T>   

Error   CS0161  'ScriptMain.Main(string[])': not all code paths return a value

Answer 1

Azure Feature Pack for SSIS旨在允许您在不使用 .NET 代码的情况下执行此操作。请记住，您始终可以使用 Blob Storage APIs 读取和写入 ADLS Gen2 .

为了使脚本任务正常工作，您需要下载 NuGet 包并将其解压到 SSIS 包可以在 AssemblyResolve 事件中读取的位置。如果您愿意，您可以直接从代码中执行此操作，但这确实是一种黑客行为。例如

using System.Net;
using System.IO.Compression;
using System.IO;
using System.Linq;
using System.Collections.Generic;
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    static WebClient wc = new WebClient();

    class NuGetEntry
    {
        public string AssemblyName { get; set; }
        public string PackageUri { get; set; }
        public string dllEntryPath { get; set; }
    }
    static ScriptMain()
    {
        var nugetPackageList = new List<NuGetEntry>()
            {
            new NuGetEntry()
                {
                AssemblyName= "Microsoft.Azure.Storage.Blob, Version=11.1.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35",
                PackageUri= @"https://www.nuget.org/api/v2/package/Microsoft.Azure.Storage.Blob/11.1.3",
                dllEntryPath= @"lib/net452/Microsoft.Azure.Storage.Blob.dll"
                },
            new NuGetEntry()
                {
                AssemblyName= "Microsoft.Azure.Storage.Common, Version=11.1.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35",
                PackageUri= @"https://www.nuget.org/api/v2/package/Microsoft.Azure.Storage.Common/11.1.3",
                dllEntryPath= @"lib/net452/Microsoft.Azure.Storage.Common.dll"
                }
            };

        var nugetPackages = nugetPackageList.ToDictionary(e => e.AssemblyName, e => e);

        AppDomain.CurrentDomain.AssemblyResolve += (s, a) =>
        {
            if (nugetPackages.ContainsKey(a.Name))
            {
                var pe = nugetPackages[a.Name];
                var dllName = Path.GetFileName(pe.dllEntryPath);
                var localFileName = Path.Combine(Path.GetTempPath(), dllName);

                if (File.Exists(localFileName))
                {
                    var asm = Assembly.LoadFile(localFileName);
                    return asm;
                }
                using (var pkg = wc.OpenRead(pe.PackageUri))
                {
                    using (var zip = new ZipArchive(pkg))
                    {
                        //var entries = zip.Entries;

                        var dllStream = zip.GetEntry(pe.dllEntryPath).Open();
                        using (var fs = File.OpenWrite(localFileName))
                        {
                            dllStream.CopyTo(fs);
                        }
                        var asm = Assembly.LoadFile(localFileName);
                        return asm;
                    }
                }
            }
            return null;
        };
    }