如何在SSIS中对重复SESSION ID行的时间相关字段求和合并？

Absolutely! You can definitely achieve this row aggregation in SSIS—here are two practical approaches to get your desired output:

Approach 1: Aggregate Directly in the Source SQL Query (Recommended) #

This is the most efficient method since databases are optimized for grouping and summing data. Instead of pulling all raw rows into SSIS, let SQL Server do the heavy lifting first.

Use a grouped SELECT query as your SSIS data source, like this:

SELECT 
    [SESSION ID],
    -- Convert total seconds to HH:MM.SS format
    CONCAT(
        RIGHT('0' + CAST(FLOOR(SUM(TSEC) / 3600) AS VARCHAR), 2),
        ':',
        RIGHT('0' + CAST(FLOOR((SUM(TSEC) % 3600) / 60) AS VARCHAR), 2),
        '.',
        RIGHT('0' + CAST(SUM(TSEC) % 60 AS VARCHAR), 2)
    ) AS [TALK TIME],
    SUM(TSEC) AS TSEC,
    ROUND(SUM(TSEC) / 60.0, 2) AS TMIN
FROM YourSourceTableName
GROUP BY [SESSION ID]

How this works: #

• We group rows by SESSION ID to combine duplicates
• SUM(TSEC) gives us the total seconds for each session
• The CONCAT string manipulation converts total seconds into your desired HH:MM.SS format
• ROUND(SUM(TSEC)/60.0, 2) calculates the total minutes rounded to two decimals

Approach 2: Aggregate Within the SSIS Data Flow #

If you can’t modify the source query (e.g., using a fixed view), you can handle the aggregation directly in SSIS:

1. Add an OLE DB Source: Connect to your SQL Server table and pull the raw data.
2. Add a Sort Component: Configure it to sort by SESSION ID (the Aggregate component requires sorted input).
3. Add an Aggregate Component:
   • In the "Group By" tab, select SESSION ID as the grouping column.
   • In the "Aggregations" tab, select TSEC, set the operation to Sum, and rename the output column to TotalTSEC.
4. Add a Derived Column Component: Create two new columns to calculate your final values:
   • For TALK TIME, use this expression to convert total seconds to the required format:

     RIGHT("0" + (DT_WSTR,2)(FLOOR([TotalTSEC] / 3600)), 2) + ":" + RIGHT("0" + (DT_WSTR,2)(FLOOR(([TotalTSEC] % 3600) / 60)), 2) + "." + RIGHT("0" + (DT_WSTR,2)([TotalTSEC] % 60), 2)
     

   • For TMIN, use:

     ROUND([TotalTSEC] / 60.0, 2)
     

5. Add an OLE DB Destination: Connect it to your target table and map the derived columns appropriately.

Final Note #

Both methods will produce your desired output, but the SQL query approach is almost always better—it’s faster, uses fewer SSIS resources, and is easier to debug and maintain.

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