SSIS Script task for download excel from sharepoint online / register assemblies

Due to a request, I developed a simple task that downloads excel file from SharePoint.

The tricky part is registering DLLs. when you code console application you add SharePoint client DLLs via NuGet automatically. But for script task, you have to register them to GAC or add by code.

DownloadFile function simply downloads file with SharePoint online credentials. for running SharePoint client DLLs you need to register assemblies. CurrentDomain_AssemblyResolve function registers those two.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Security;
using System.Net;
#endregion

namespace ST_2301c09da66741b5a902b316ce92068c
{
    /// <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
	{

		/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>

            public void Main()
            {
                const string username = "ismail.tunca@arbistech.co.uk";
                const string password = "pass@word";
                const string url = @"https://arbistechuk.sharepoint.com/sites/Reports/Shared%20Documents/data.xlsm";
                var securedPassword = new SecureString();
                foreach (var c in password.ToCharArray()) securedPassword.AppendChar(c);
                var credentials = new Microsoft.SharePoint.Client.SharePointOnlineCredentials(username, securedPassword);


                AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);

                DownloadFile(url, credentials, @"c:\temp\s1.xlsm");


                Dts.TaskResult = (int)ScriptResults.Success;
            }
            private static void DownloadFile(string webUrl, ICredentials credentials, string fileRelativeUrl)
            {
                using (var client = new WebClient())
                {
                    client.Credentials = credentials;
                    client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f");
                    client.DownloadFile(webUrl, fileRelativeUrl);
                }
            }

        public System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            string path = @"c:\temp\";

            if (args.Name.Contains("Microsoft.SharePoint.Client"))
            {
                return System.Reflection.Assembly.UnsafeLoadFrom(System.IO.Path.Combine(path, "Microsoft.SharePoint.Client"));
            }

            if (args.Name.Contains("Microsoft.SharePoint.Client.Runtime"))
            {
                return System.Reflection.Assembly.UnsafeLoadFrom(System.IO.Path.Combine(path, "Microsoft.SharePoint.Client.Runtime"));
            }
            return null;
        }




        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}