CODEWITHSUNDEEP
sql-serverexcelssis
ZeExplorer
ZeExplorer

Reputation: 553

Add subtoal as new row in data flow task SSIS

SSIS Data flow task

Source: Stored Procedure @ SQL Server Dest: Excel

Stored Procedure Results:

User Score Jim 15 Betsy 10

I need to add a new row to the result set which is total of score before I dump it into the excel file. So the result should look like this:

User Score Jim 15 Betsy 10 Total 25

what is the best way to achieve this via SSIS, there are several ways to calculate a new column based on existing columns, but I'm not sure how to add a new row. Do we have any aggregation functions similar to SSRS?, I tried Aggregation transformation in SSIS as follows:

Score --> Sum User --> Group By

Doesn't give me what I want. Ideas please.

Upvotes: 1

Views: 1807

Answers (1)

ZeExplorer
ZeExplorer

Reputation: 553

This solution worked fine for me. Use AddRow() method via SSIS script task, here is the 3 line code in C#

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    int total = 0;
    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        total = total + Row.Score;

        GrandTotalBuffer.AddRow();              // Add a new row
        GrandTotalBuffer.Score = total;         // Assign calculated value of the variable 'total' to the Column 'Score' for the newly added row
        GrandTotalBuffer.User = "Total";        // Assign value 'Total' to the Column 'User' for the newly added row
    }

    public override void CreateNewOutputRows()
    {

    }

}

And here is how the data flow task looked like:

enter image description here

The Grand Total contains calculated output Output 0 contains regular rows from OLE DB Source thus needing to use the Union All.

Upvotes: 1

Related Questions