This should be really straight forward and easy, but SSIS variables are not easy for whatever reason.
I am trying to pass a simple count to a variable in my result set and use that count in a precedent constraint. If the value of the count is greater than 0, than continue with the package. The issue is however, that my variable is never increasing past 0.
Here are the details of my package:
Variable = CheckData
Scope = Package
Data Type = Int32
Value = 0
In an Execute SQL task I have the following configuration:
Result Set = Single Row
SQLStatement = SELECT COUNT(ID) AS CheckData FROM Table_A
In the Result Set window I have:
Result Name = CheckData
Variable Name = User::CheckData
I used a script task to identify the value of the variable and I am able to see that the value never increases past 0. Also, if I statically set the variable value to anything greater than 0 the package continues to execute. So the problem is obviously with the execute sql task not passing the value of my count to the variable.
I know my source table has data and the count is greater than 0. Why is my variable not getting the value from the count?
SQL Server Reporting Analyst