The returned integer value from SSIS performs an SQL task

Question

I am using SQL Server 2005 Business Intelligence Studio and struggling with integer return from a very simple SQL execution task. For a very simple test, I wrote an SQL statement like:

Select 35 As 'TotalRecords' 

Then I set the ResultSet as

 ResultName = TotalRecords and VariableName = User::TotalRecords 

When I execute this, the statement is executed, but the variable has no updated value. However, it has a default value that I set when defining the variable.

Returning a date variable works, but an integer variable does not work. The type of the specified User::TotalRecords is Int32 in the package area.

Thanks for any tips.

Answer 1

The value for the result name is invalid. Try using the ordinal (positional) approach.

Basic setup for OLE or ADO.NET SQL task execution

Here I have indicated serial number 0 for a column with a null value in my result set.

Here you can see the source variables and their values ​​(-1, -2), as well as their run-time values ​​of 35 for both.

Answer 2

Once your package completes the yuor variables, return to the default state. If you use a breakpoint or msgbox, you will see that at runtime your variable matters.