如何通过SSIS+存储过程实现Oracle到SQL Server的数据转换与增量同步？

Alright, let's walk through building the stored procedure you need to move and transform data from your staging table to the production table, including handling date conversions, mapping lookups, and incremental syncs.

First, let's define placeholder names for your tables (replace these with your actual object names):

• Staging table: Staging.Oracle_Source_Data
• Production table: Production.Target_Data
• Feature mapping table: Reference.Feature_Map (holds SourceFeatureID ↔ DestinationFeatureID)
• PID mapping table: Reference.PID_Map (holds SourcePID ↔ DestinationPID)

Step 1: Complete Stored Procedure Code #

Here's a tailored stored procedure with detailed comments explaining each critical part:

CREATE PROCEDURE Production.Sync_Staging_To_Production
AS
BEGIN
    SET NOCOUNT ON;

    -- Variable to store the last successful sync date from production
    DECLARE @LastSyncDate DATE;

    -- Fetch the most recent update date from production (handle empty table scenario)
    SELECT @LastSyncDate = MAX(LastUpdatedDate)
    FROM Production.Target_Data;

    -- If production table is empty, pull all staging data by setting a very early date
    IF @LastSyncDate IS NULL
        SET @LastSyncDate = '1900-01-01';

    BEGIN TRY
        -- Use MERGE to handle both inserts (new records) and updates (changed existing records)
        MERGE INTO Production.Target_Data AS Target
        USING (
            -- Transform staging data: convert string date, resolve mappings
            SELECT
                -- Convert source string date to SQL Server date type (adjust format code as needed)
                TRY_CONVERT(DATE, s.SourceDate, 121) AS ConvertedSourceDate,
                -- Get mapped Feature ID from reference table
                fm.DestinationFeatureID,
                -- Get mapped PID from reference table
                pm.DestinationPID,
                -- Pass through the 4th column (replace with your actual column name)
                s.AdditionalColumn,
                -- Track when this record was synced
                GETDATE() AS LastUpdatedDate
            FROM Staging.Oracle_Source_Data AS s
            -- Join to feature mapping (use LEFT JOIN if you want to retain records without a match)
            INNER JOIN Reference.Feature_Map AS fm
                ON s.SourceFeatureID = fm.SourceFeatureID
            -- Join to PID mapping
            INNER JOIN Reference.PID_Map AS pm
                ON s.SourcePID = pm.SourcePID
            -- Filter for incremental data: only sync records newer than last sync
            WHERE TRY_CONVERT(DATE, s.SourceDate, 121) > @LastSyncDate
        ) AS Source
        -- Match on production table's unique identifier(s) (adjust to your actual key columns)
        ON Target.DestinationFeatureID = Source.DestinationFeatureID 
           AND Target.DestinationPID = Source.DestinationPID
        -- Update existing records if data has changed
        WHEN MATCHED THEN
            UPDATE SET
                Target.ConvertedSourceDate = Source.ConvertedSourceDate,
                Target.AdditionalColumn = Source.AdditionalColumn,
                Target.LastUpdatedDate = Source.LastUpdatedDate
        -- Insert records that don't exist in production yet
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (ConvertedSourceDate, DestinationFeatureID, DestinationPID, AdditionalColumn, LastUpdatedDate)
            VALUES (Source.ConvertedSourceDate, Source.DestinationFeatureID, Source.DestinationPID, Source.AdditionalColumn, Source.LastUpdatedDate);

        PRINT 'Sync completed successfully. Rows affected: ' + CAST(@@ROWCOUNT AS VARCHAR);
    END TRY
    BEGIN CATCH
        -- Error handling: customize based on your logging needs
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

Key Adjustments for Your Environment #

• Date Conversion: The TRY_CONVERT(DATE, s.SourceDate, 121) uses format code 121 (ISO8601: yyyy-mm-dd hh:mi:ss.mmm). If your source date string uses a different format (e.g., dd/MM/yyyy), replace 121 with the code that matches your string structure.
• Mapping Joins: I used INNER JOIN to only sync records with valid mappings in both tables. If you need to keep records without a mapping (e.g., set destination IDs to NULL), switch to LEFT JOIN.
• Unique Match Logic: The ON clause in MERGE uses DestinationFeatureID and DestinationPID as the unique key. Update this to match your production table's primary key or unique identifier.
• Incremental Filter: Invalid date strings will return NULL from TRY_CONVERT and be excluded from syncs. Add extra logic (like logging invalid dates) if you need to handle these cases.

Next Steps #

1. Swap all placeholder table/column names with your actual objects.
2. Test the procedure in a non-production environment to verify data transformation and sync behavior.
3. Schedule it to run periodically via SQL Server Agent Job for automated incremental updates.

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