Azure SDK with SSIS

Question

I am looking at different options to connect to an Azure data lake storage using SSIS 2019 script task and one of the options would be to use the Azure .NET SDK. But, I am having a hard time to use this in the script task. I'm trying to make it work with below code but no luck yet.

C# Code:

 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;
 }

Please guide me to connect to Azure via SSIS script task.

C# Code:

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:

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

The Azure Feature Pack for SSIS is intended to allow you to do this without using .NET code. And remember you can always read and write to ADLS Gen2 using the Blob Storage APIs.

For your script task to work you would need to download and extract the NuGet packages to a location the SSIS package can read in the AssemblyResolve event. You can do this right from your code if you want, but it's really a hack. eg

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;
        };
    }