如何基于SSIS包实现文本文件与SQL Server的日期文本比对及处理

Alright, let's walk through building this SSIS package with the validation logic you need. I’ve built similar date/text pair validation workflows before, so here’s a practical, step-by-step approach:

First, make sure your sources and target are set up correctly:

• Text File: Confirm the file format (delimiter, encoding, date format). For example, if it’s a pipe-separated file like 2024-05-20|SampleData, note that down for later configuration.
• SQL Server Table: Create the single-row table (if it doesn’t exist) to store your date/text pair. Use this script as a starting point:

CREATE TABLE dbo.SourceControl (
    LastProcessedDate DATE NOT NULL,
    ProcessedText NVARCHAR(255) NOT NULL,
    -- Ensure only one row exists
    CONSTRAINT PK_SourceControl PRIMARY KEY (LastProcessedDate)
);

Note: If you need to enforce a single row strictly, you can add a check constraint like CHECK (LastProcessedDate IS NOT NULL) or use a singleton table pattern with a fixed ID column.

Start with a Data Flow Task in your SSIS package:

• Drag a Flat File Source into the Data Flow. Configure it to point to your text file, then map the two columns (date and text) with the correct data types (e.g., DATE for the date column, NVARCHAR(255) for the text).
• If your date format isn’t the default SQL Server format, go to the Advanced Editor of the Flat File Source, select the date column, and set the DateFormat property (e.g., yyyy-MM-dd) to avoid conversion errors.

Still in the same Data Flow:

• Add an OLE DB Source connected to your SQL Server database. Use this query to grab the single row (or nothing if it’s the first run):

SELECT TOP 1 LastProcessedDate, ProcessedText FROM dbo.SourceControl;

Now we need to check if the file’s data matches what’s in the table:

• Sort Both Datasets: Add a Sort component after both the Flat File Source and OLE DB Source. Sort each by their date column (e.g., FileDate for the file, LastProcessedDate for the table) — this is required for the Merge Join.
• Merge the Data: Drag a Merge Join component, connect both sorted outputs to it, and choose a Full Outer Join. This lets us handle the first-run scenario where the table is empty.
• Add a Match Flag: Use a Derived Column component to create a new column IsMatch that checks for matches. Choose the logic that fits your exact requirement:
  • If matching only the date:

    ISNULL(LastProcessedDate) ? FALSE : (FileDate == LastProcessedDate ? TRUE : FALSE)
    

  • If matching the full date/text pair:

    ISNULL(LastProcessedDate) ? FALSE : (FileDate == LastProcessedDate && FileText == ProcessedText ? TRUE : FALSE)
    

Use a Conditional Split component to route data to the right actions:

• Branch 1: IsMatch == TRUE (Date/Text Pair Matches)
  • This is where you run your "specific processing" — e.g., load the file data into a business table, trigger a stored procedure, or run data validation. Use an OLE DB Destination or Execute SQL Task to handle this.
• Branch 2: IsMatch == FALSE (No Match or First Run)
  • First, update the SQL Server table with the new date/text pair. Use an OLE DB Command component with this dynamic SQL to handle both insert (first run) and update (subsequent runs):

    IF EXISTS (SELECT 1 FROM dbo.SourceControl)
        UPDATE dbo.SourceControl SET LastProcessedDate = ?, ProcessedText = ?
    ELSE
        INSERT INTO dbo.SourceControl (LastProcessedDate, ProcessedText) VALUES (?, ?)
    

    Map the FileDate and FileText columns from the Flat File Source to the parameters in the SQL.
  • Then run your "other processing" — same as above, use the appropriate destination or task for your needs.
• Optional Branch: Handle Edge Cases
  • Add a branch for scenarios like empty text files or unexpected data mismatches. Route these to a log table or send an alert using an Execute SQL Task or Send Mail Task.

Once the validation and core actions are done, chain additional Data Flow Tasks or Control Flow Tasks to handle any post-processing (e.g., data cleaning, aggregation, loading to other targets). Use Precedence Constraints to ensure tasks run in the right order (e.g., only run post-processing after the validation completes successfully).

• Error Handling: Enable Error Output on all source and transform components to catch bad data (like invalid dates) and write errors to a log table.
• Variables: Use SSIS variables (e.g., @FilePath, @DBConnectionString) to make the package configurable — no need to re-edit components when paths change.
• Testing: Test three key scenarios: first run (empty table), matching date/text pair, and non-matching pair. Verify each branch executes the correct actions.

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