求SSIS脚本任务：用Office365凭据从SharePoint Online下载Excel文件

Hey there! I get it—you’re comfortable with SSIS but don’t dive into Script Tasks often. Let’s build a reliable script that handles Office 365 authentication for downloading your SharePoint Online Excel file, so you can move it to your local machine and import it into SQL Server seamlessly.

Prerequisites #

First, make sure you have these set up:

• SharePoint Online CSOM Libraries: Download the Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll. Copy these DLLs to the SSIS Script Task’s reference directory—for example, if you’re using SQL Server 2019 (32-bit), that’s C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Tasks\Microsoft.SqlServer.Dts.Tasks.ScriptTask\. Adjust the version number (150) to match your SQL Server version.
• SSIS Variables: Create these package-level variables (mark O365Password as sensitive to encrypt it):
  • SharePointSiteUrl: Your SharePoint site base URL (e.g., https://company.sharepoint.com)
  • FileRelativePath: The server-relative path to your Excel file (e.g., /xyz/file1.xlsx)
  • LocalSavePath: Local path to save the downloaded file (e.g., C:\Temp\file1.xlsx)
  • O365Username: Your Office 365 account email (e.g., yourname@company.com)
  • O365Password: Your Office 365 account password

The Script Task Code (C#) #

Open your SSIS Script Task, select C# as the language, and replace the default code with this:

using System;
using System.IO;
using System.Security;
using Microsoft.SharePoint.Client;
using Microsoft.SqlServer.Dts.Runtime;

namespace ST_XXXXXXXXXXXXXXXX
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Fetch SSIS variables
            string siteUrl = Dts.Variables["User::SharePointSiteUrl"].Value.ToString();
            string fileRelativePath = Dts.Variables["User::FileRelativePath"].Value.ToString();
            string localSavePath = Dts.Variables["User::LocalSavePath"].Value.ToString();
            string o365Username = Dts.Variables["User::O365Username"].Value.ToString();
            string o365Password = Dts.Variables["User::O365Password"].Value.ToString();

            try
            {
                // Convert plaintext password to secure string
                SecureString securePassword = new SecureString();
                foreach (char c in o365Password)
                {
                    securePassword.AppendChar(c);
                }

                // Establish SharePoint Online connection
                using (ClientContext spContext = new ClientContext(siteUrl))
                {
                    spContext.Credentials = new SharePointOnlineCredentials(o365Username, securePassword);

                    // Retrieve the target file from SharePoint
                    Microsoft.SharePoint.Client.File spFile = spContext.Web.GetFileByServerRelativeUrl(fileRelativePath);
                    spContext.Load(spFile);
                    spContext.ExecuteQuery();

                    // Download file content
                    FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(spContext, fileRelativePath);

                    // Save file to local directory
                    using (FileStream localFileStream = new FileStream(localSavePath, FileMode.Create))
                    {
                        fileInfo.Stream.CopyTo(localFileStream);
                    }

                    // Mark task as successful
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            }
            catch (Exception ex)
            {
                // Fire error event for SSIS logging
                Dts.Events.FireError(0, "SharePoint File Download Task", 
                    $"Error downloading file: {ex.Message}\nStack Trace: {ex.StackTrace}", 
                    string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Key Notes #

• Permissions: Ensure the Office 365 account you’re using has read and download access to the target SharePoint file and site.
• MFA Consideration: This script works with non-MFA enabled accounts. If your account uses Multi-Factor Authentication, you’ll need to adjust the authentication method (e.g., using Azure AD app permissions or device code flow—feel free to ask for help with that!).
• Error Handling: The script includes basic error logging that integrates with SSIS’s built-in error handling, so you’ll see detailed errors in your package logs if something goes wrong.

Once the file is downloaded locally, you can use standard SSIS components (like the Excel Source) to import it into your SQL Server database.

内容的提问来源于stack exchange，提问作者Dhananjay Rele