Search code examples
c#sqlssis

Passing query results to variable in SSIS

I have a SSIS package that runs a Execute SQL Task to get a row count, stores that in a variable, and then using a Script Task reads that variable and checks to see if it's greater than 0.

The issue I am having is that when I debug the package, it's reading the variable as -1 (which I assume is just SQL saying the query executed successfully), instead of the 38,000-some rows that are in the DB. As far as I can tell, everything is set up correctly and coded the right way, so I'm not sure what could cause this.

Execute SQL Task settings: enter image description here enter image description here

Code from Script Task: enter image description here

private byte[] emptyBytes = new byte[0];

    public void Main()
    {
        int rowCount = Convert.ToInt32(Dts.Variables["User::SMART_rowcount"].Value.ToString());

        if (rowCount > 0)
        {
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        else
        {
            Dts.Log("The SMART Row Count Check has failed due to result set having no rows. Check table SMART.[SMART_SEC_MGMT_QUOTE_DATA].", (int)Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure, emptyBytes);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

EDIT:

Here is the flow of these and the variables:

enter image description here enter image description here

Solution

  • So I had everything set up correctly and there were no issues with my process. To resolve this error, I deleted the Execute SQL Task, remade it with all of the exact same settings and everything, and it is now returning the correct result.

    "Have you tried turning it off and back on again?"