Store result in Execute Sql task

I have written a stored procedure which will return the columns BEID, Date_of_txn, Txn_Amount as a result after the execution. I am getting the result manually when I run the stored procedure in SQL Server Management Studio. But I want to execute this stored procedure with “Execute SQL” task in SSIS and want to store the result in a variable. How can I do that?

I am not aware of how to store the result in result set as when I run the package, the package got failed every time.

Result set: Single Row
Connection Type: OLE DB
Connection: LocalHost.Fino_Detail
SQLStatement: Exec sp_HighAmount 02, 2019

Stored procedure:

CREATE PROCEDURE [dbo].[sp_HighAmount]
    (@Month INT,
     @Year INT)
AS
BEGIN
    SELECT
        BEID, DATE_OF_TXN, SUM(Txn_amount) AS Txn_Amount 
    FROM 
        Fino_Sales
    WHERE
        SUBSTRING(Date_of_txn, 6, 2) = @Month
        AND SUBSTRING(Date_of_txn, 1, 4) = @Year
    GROUP BY
        BEID, Date_of_txn
    HAVING 
        SUM(Txn_Amount) BETWEEN 35001 AND 50000
        AND SUM(commission_paid) > 350
END

Answer

... 
group by BEID, Date_of_txn
...

There is a high probability that such query returns multiple rows, therefore a resul set should be switched to: “Full result set” and the output to be mapped to a variable with a type: Object, with a further foreach task to traverse of each row.

A good step-by-step walkthrough can read: Execute SQL Task in SSIS Full Row Set

Advertisement

Answer