如何在.NET Core MVC中实现Excel导入Azure数据库？含SSIS方案咨询

Hey there! Let's break down your options for importing Excel files into an Azure database from a .NET Core MVC app, including the SSIS + SQL Agent Job approach you're considering, plus some alternative methods that might fit your workflow better.

First, let's cover how to tie this into your .NET Core MVC app. Since Azure SQL Database (single/elastic pool) doesn't have a built-in SQL Agent, you'll need either an Azure SQL Managed Instance or a SQL Server on Azure VM to host your job. Here's the step-by-step:

1. Prepare and deploy your SSIS package

   • Build your Excel-to-Azure-DB import logic in SQL Server Data Tools (SSDT).
   • Deploy the package to the Azure-SSIS Integration Runtime (IR) via Azure Data Factory (ADF) or SSDT. This is how you run SSIS workloads in Azure.

2. Create a SQL Agent Job

   • In your Azure SQL MI or VM-based SQL Server, create a new job. Add a step of type SQL Server Integration Services Package, then point it to your deployed SSIS package.

3. Trigger the job from .NET Core MVC
   Use Microsoft.Data.SqlClient to execute the system stored procedure that starts the job. Here's a code snippet for your controller:

   using Microsoft.Data.SqlClient;
   using Microsoft.AspNetCore.Mvc;
   
   public class ImportController : Controller
   {
       public async Task<IActionResult> StartExcelImportJob()
       {
           var sqlConnectionString = "Your SQL MI/VM Connection String";
           var jobName = "Excel_To_AzureDB_Import_Job";
   
           using (var conn = new SqlConnection(sqlConnectionString))
           {
               await conn.OpenAsync();
               using (var cmd = new SqlCommand("msdb.dbo.sp_start_job", conn))
               {
                   cmd.CommandType = System.Data.CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@job_name", jobName);
                   await cmd.ExecuteNonQueryAsync();
               }
           }
   
           return Ok("Import job has been started successfully!");
       }
   }
   

   • Permission note: Make sure the SQL user your app uses has the SQLAgentOperatorRole (or more granular permissions) in the msdb database to execute sp_start_job.

If you want a simpler, code-first approach without relying on SSIS or SQL Agent, this is a great fit for small-to-medium Excel files. Use popular libraries to read Excel data and bulk-insert it into Azure DB:

Tools to use: #

• EPPlus: Supports modern .xlsx/.xlsm files (free for non-commercial use; commercial license required otherwise).
• NPOI: Open-source, supports both old .xls and new .xlsx formats.

Example with EPPlus: #

1. Install the NuGet package:

   dotnet add package EPPlus
   

2. Controller code to handle upload and import:

   using OfficeOpenXml;
   using Microsoft.EntityFrameworkCore;
   using Microsoft.AspNetCore.Mvc;
   
   public class ImportController : Controller
   {
       private readonly YourDbContext _dbContext;
   
       public ImportController(YourDbContext dbContext)
       {
           _dbContext = dbContext;
       }
   
       [HttpPost]
       public async Task<IActionResult> UploadAndImport(IFormFile excelFile)
       {
           if (excelFile == null || excelFile.Length == 0)
               return BadRequest("Please upload a valid Excel file.");
   
           // Configure EPPlus license (non-commercial use)
           ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
   
           var importData = new List<YourDatabaseEntity>();
   
           using (var stream = new MemoryStream())
           {
               await excelFile.CopyToAsync(stream);
               using (var package = new ExcelPackage(stream))
               {
                   var worksheet = package.Workbook.Worksheets.First();
                   var rowCount = worksheet.Dimension.Rows;
   
                   // Skip header row (adjust if your header is on a different row)
                   for (int row = 2; row <= rowCount; row++)
                   {
                       importData.Add(new YourDatabaseEntity
                       {
                           FirstColumn = worksheet.Cells[row, 1].Text,
                           SecondColumn = int.TryParse(worksheet.Cells[row, 2].Text, out var val) ? val : 0,
                           // Map other columns as needed
                       });
                   }
               }
           }
   
           // Bulk insert for better performance
           await _dbContext.YourDatabaseEntities.AddRangeAsync(importData);
           await _dbContext.SaveChangesAsync();
   
           return Ok($"Successfully imported {importData.Count} records!");
       }
   }
   

   • For large files: Use SqlBulkCopy instead of AddRangeAsync to handle bulk inserts more efficiently. You'll need to convert your list to a DataTable first.

If you need enterprise-grade ETL features (like scheduling, data transformation, or monitoring), ADF is a cloud-native alternative to SSIS. You can trigger ADF pipelines directly from your .NET Core app via the Azure Management API.

1. Create an ADF Pipeline:

   • Add an Excel Source (point to your storage where Excel files are uploaded) and an Azure SQL Database Sink, then configure data mappings and any needed transformations.

2. Trigger the pipeline from .NET Core:
   You'll need an Azure AD service principal with permissions to trigger ADF pipelines. Here's a simplified example:

   using System.Net.Http;
   using System.Net.Http.Headers;
   using Newtonsoft.Json;
   using Microsoft.AspNetCore.Mvc;
   
   public class ImportController : Controller
   {
       public async Task<IActionResult> TriggerAdfImportPipeline()
       {
           var adfApiUrl = "https://management.azure.com/subscriptions/{YOUR_SUBSCRIPTION_ID}/resourceGroups/{YOUR_RG_NAME}/providers/Microsoft.DataFactory/factories/{YOUR_ADF_NAME}/pipelines/{YOUR_PIPELINE_NAME}/createRun?api-version=2018-06-01";
           var azureAdToken = "YOUR_AZURE_AD_ACCESS_TOKEN"; // Fetch this via Azure AD authentication
   
           using (var client = new HttpClient())
           {
               client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", azureAdToken);
               var response = await client.PostAsync(adfApiUrl, new StringContent("", System.Text.Encoding.UTF8, "application/json"));
   
               if (response.IsSuccessStatusCode)
               {
                   var result = await response.Content.ReadAsStringAsync();
                   var runId = JsonConvert.DeserializeObject<dynamic>(result).runId;
                   return Ok($"ADF pipeline triggered successfully. Run ID: {runId}");
               }
               return BadRequest("Failed to trigger the ADF pipeline.");
           }
       }
   }
   

• SSIS + SQL Agent: Best if you already have SSIS expertise, existing SSIS packages, or need complex on-prem-to-Azure ETL workflows. Requires a SQL MI/VM environment.
• Direct .NET Core Import: Ideal for quick development, small-to-medium files, and full control over the import logic without external services.
• Azure Data Factory: Perfect for enterprise-scale ETL, scheduling, monitoring, and integrating multiple data sources.

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