请教：SSIS能否读取SharePoint Online的Excel文件？如何每日导入SQL Server 2014？

1. Does SSIS support using Excel files in a SharePoint Online (SPO) document library as a data source? #

Short answer: SSIS doesn’t have a built-in, out-of-the-box component to directly connect to SPO document libraries for reading Excel files. But don’t worry—there are several reliable workarounds to make this happen:

• Use PowerShell to download files first: You can write a PowerShell script (using the PnP PowerShell module, designed specifically for SharePoint Online) to authenticate to your SPO site, download Excel files to a local folder or network share, then use SSIS’s standard Excel Source component to read those local files. This is the most common and low-cost approach.
• Leverage SSIS Script Tasks with SPO REST API: If you prefer not to store files locally, create a Script Task in SSIS that calls the SPO REST API to fetch Excel file content, stream it into memory, and parse it using libraries like EPPlus (for .xlsx files) to extract data. This is more code-heavy but avoids local storage.
• Third-party SSIS components: Some commercial component libraries offer pre-built connectors for SPO. These simplify the process but come with a cost.

2. Best solution for daily recurring import of Excel files from SPO to SQL Server #

For a reliable, automated daily workflow tailored to your SQL Server 2014 environment, follow this step-by-step approach:

Step 1: Automate SPO file sync to a local/network location #

• Write a PnP PowerShell script that:
  • Authenticates to your SPO site.
  • Filters Excel files by their Modified property to only download new/updated files since the last import (avoids reprocessing unchanged data).
  • Saves files to a dedicated folder accessible by the SQL Server Agent service account.
  • Adds error handling: logs failures, sends email alerts if downloads fail, and cleans up old files if needed.

Step 2: Build the SSIS package for data import #

• Create an SSIS package with these elements:
  • A Foreach Loop Container to iterate over all synced Excel files.
  • An Excel Source component to read data (configure the connection manager for your file type—.xls vs .xlsx).
  • Data Conversion/Derived Column transformations to clean and format data to match your SQL table schema.
  • An OLE DB Destination to load data into SQL. For incremental loads, add a Lookup transformation to check for existing records (via a unique key) and choose to insert new rows or update existing ones.
  • Enabled SSIS logging to track execution, errors, and row counts for troubleshooting.

Step 3: Schedule the workflow with SQL Server Agent #

• Set up a SQL Server Agent job:
  • First step: Run the PowerShell script to sync files from SPO.
  • Second step: Execute the SSIS package (ensure the Agent service account has permissions for the synced folder and SSIS execution).
  • Configure daily run times and set up email notifications for job successes/failures.

Step 4: Optimize reliability and performance #

• Stick to incremental logic: Always filter files by Modified date and use incremental loads in SSIS to reduce load on SPO and SQL Server.
• Avoid file locks: Schedule the job during off-hours to ensure no users have the SPO Excel files open when sync runs.
• Handle bad data: Use SSIS error outputs to redirect invalid rows to a staging table for review, instead of failing the entire package.

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