SSIS Execute SQL task not returning results

William Burke 1 Reputation point
2021-02-03T02:02:45.967+00:00

I have created a SSIS Package with a Execute SQL Task. I return a count from a query to a User:Variable Int32. The query is "SELECT COUNT(*) As RecCnt FROM dbo.Trace_Return_BRE WHERE (BatchId = ?)". The input Variable is a INT32. The input Parameter is defined as a Long in the Execctue task. It has a valid BatchId in it when the execute runs. The RecCnt is mapped to RecordCount which is also a INT32. When I run the Query on the server it returns 42072. But after the task runs Record count is 0. Don't know what I am doing wrong. I tried setting the result set to zero still returned zero.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,470 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2021-02-03T06:37:58.877+00:00

    Hi @William Burke ,

    1.Please check if you set the Execute SQL Task successfully.
    63386-sqlresult.png
    63209-est-general.png
    63210-est-parametermapping.png
    63337-est-resultset.png

    2.Please set Breakpoints to see the value of variable when the package is running.
    We can refer to Breakpoints in SSIS.
    63401-cf-est.png

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.