如何合并两个SSIS包，实现首次加载当前+历史表、后续仅加载当前表？

Absolutely! You can combine both package behaviors into one data flow by adding conditional logic to control history table inclusion, plus a way to track whether the initial full load has completed. Here's a practical, step-by-step breakdown:

Step 1: Create a Control Table to Track Run Status #

First, you need to persist whether the first full load (current + history tables) has finished. Create a simple control table in your database:

CREATE TABLE PackageRunControl (
    PackageName VARCHAR(100) PRIMARY KEY,
    IsFirstRunCompleted BIT DEFAULT 0,
    LastRunDate DATETIME
);

-- Initialize for your merged package
INSERT INTO PackageRunControl (PackageName) VALUES ('YourCombinedSSISPackage');

Step 2: Fetch Run Status into a Package Variable #

Add an Execute SQL Task at the start of your package to retrieve the run status:

• Set the SQL statement to: SELECT IsFirstRunCompleted FROM PackageRunControl WHERE PackageName = 'YourCombinedSSISPackage';
• Configure the result set to "Single Row" and map the output to a package variable (e.g., @IsFirstRun, type Boolean).

Step 3: Build the Conditional Data Flow #

Now set up your data flow to conditionally include history table data only on the first run:

1. Add Two Sources: Include OLE DB Sources for both your Current Table and History Table.
2. Filter History Table Rows: For the History Table source, modify its query to include a condition tied to your @IsFirstRun variable:

   SELECT * FROM HistoryTable WHERE ? = 0;
   

   Map the parameter (?) to your @IsFirstRun variable. On non-first runs, this will make the History Table source return no rows automatically—no extra processing needed.
3. Combine Data: Use a Union All transformation to merge rows from the Current Table source and the filtered History Table source.
4. Load to Destination: Connect the Union All output to your target table destination.

Step 4: Update Run Status After Successful Load #

Add another Execute SQL Task after the Data Flow Task (set the precedence constraint to "Success") to mark the first run as completed:

UPDATE PackageRunControl 
SET IsFirstRunCompleted = 1, LastRunDate = GETDATE()
WHERE PackageName = 'YourCombinedSSISPackage';

Key Notes #

• Error Safety: If the first run fails, the IsFirstRunCompleted flag won’t update, so the package will retry loading both tables on the next run—ensuring your initial full load completes successfully before switching to incremental runs.
• Efficiency Tip: Filtering the History Table at the source (via the query condition) is more performant than pulling all rows and filtering them later in the data flow, as it reduces the number of rows transferred into SSIS.

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