如何自动将Order系列日志文件每日导入SQL Server数据库表

Hey Neil, let's tackle this automation problem step by step. You already know how to use the Import Wizard manually, so we can build on that to create a hands-off daily workflow that imports your Order*.txt files and archives them afterward. Here are two reliable approaches you can choose from:

This is a great fit if you're comfortable with the SQL Server ecosystem, since it lets you reuse the logic you already built with the Import Wizard.

• Step 1: Save your Import Wizard flow as an SSIS package
  When you run the Import Wizard, on the final screen, select the option to Save SSIS Package instead of just running the import. You can save it to the SQL Server msdb database or a local .dtsx file. This turns your manual import steps into a reusable, editable package.

• Step 2: Add file archiving logic to the SSIS package
  Open the saved package in SQL Server Data Tools (SSDT):

  1. Add a Foreach Loop Container to the control flow. Configure it to iterate over all Order*.txt files in your source folder (use a file enumerator and set the filter to Order*.txt).
  2. Inside the loop, keep your existing import logic (the data flow task from the wizard).
  3. Add a File System Task right after the data flow task. Set its operation to Move file, map the source to the current file from the loop, and set the destination to your archive folder. This ensures each file is archived only after it's successfully imported.

• Step 3: Schedule the SSIS package with SQL Server Agent
  Open SQL Server Management Studio (SSMS), go to SQL Server Agent > Jobs, and create a new job:

  1. Add a job step with the type SQL Server Integration Services Package.
  2. Select your saved SSIS package (either from SQL Server or the file system).
  3. Set up a schedule to run the job once daily—pick a time when system load is low, like overnight.

If you prefer a script-based approach without needing SSIS tools, this is a straightforward alternative.

• Step 1: Write the PowerShell script
  Here's a template you can adapt to your environment. Make sure to adjust the paths, SQL Server details, and BULK INSERT settings to match your file format:

  # Define your paths and SQL Server details
  $sourceFolder = "C:\Path\To\Your\Log\Folder"
  $archiveFolder = "C:\Path\To\Your\Archive\Folder"
  $sqlInstance = "YourSQLServerName"
  $database = "YourTargetDatabase"
  $targetTable = "YourDestinationTable"
  
  # Get all Order*.txt files in the source folder
  $logFiles = Get-ChildItem -Path $sourceFolder -Filter "Order*.txt"
  
  foreach ($file in $logFiles) {
      try {
          # Build the BULK INSERT command (adjust delimiters/FIRSTROW to match your file)
          $bulkInsertQuery = @"
  BULK INSERT $targetTable
  FROM '$($file.FullName)'
  WITH (
      FIELDTERMINATOR = ',',       # Replace with your file's field separator
      ROWTERMINATOR = '\n',        # Replace with your file's line ending
      FIRSTROW = 2,                # Use this if your file has a header row
      CODEPAGE = '65001'           # Use UTF-8 if your files are encoded this way
  )
  "@
  
          # Execute the SQL command to import the file
          Invoke-SqlCmd -ServerInstance $sqlInstance -Database $database -Query $bulkInsertQuery
  
          # Move the file to the archive folder after successful import
          Move-Item -Path $file.FullName -Destination (Join-Path $archiveFolder $file.Name) -Force
          Write-Host "Successfully imported and archived: $($file.Name)"
      }
      catch {
          Write-Error "Failed to process $($file.Name): $_"
          # Leave the file in the source folder for troubleshooting
      }
  }
  

• Step 2: Schedule the script with Windows Task Scheduler

  1. Open Windows Task Scheduler and create a new task.
  2. Under Triggers, add a daily trigger for your desired time.
  3. Under Actions, add an action to Start a program. Set the program to powershell.exe and add the argument: -File "C:\Path\To\Your\Script.ps1".
  4. Make sure the task runs under an account that has:
     • Read/write permissions on both the source and archive folders
     • INSERT permissions on your target SQL Server table

• Permission Checks: Always verify the account running the automated task has the necessary access to both the file system and SQL Server.
• Error Handling: Add error logging (either in SSIS with event handlers or in PowerShell with try/catch) to track failures—this makes troubleshooting much easier.
• File Consistency: Ensure all Order*.txt files use the same format (same number of fields, delimiters, encoding). If formats vary, you'll need to adjust your import logic to handle different cases.

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