Don't capture the Result of Execute SQL Task in a Variable

Question

Post reply

Don't capture the Result of Execute SQL Task in a Variable

Lisset

SSCommitted

Points: 1858
More actions
October 21, 2010 at 6:03 pm

#143196

Hi everybody, I have the follow description.
Variable
- Name=LoopLimit
- Scope=Package3
- Data Type=Int32
Execute SQL Task
-ResultSet=SingleRow
SQLStatement=select count(*) as Limit from BITACORA_CARGA_ARCHIVO
ResultSet
- Result name=Limit
- Variable Name=User::LoopLimit
When start to debugg, this run without error but the variable LoopLimit don't capture the value of SQLStatement. Maybe I need configure some item aditional for can capture the result?.
Thanks a lot by your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How are you looking at the variable?

After you have run the package, or while the package is running using a breakpoint?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Satyananda.Reddy SSC Enthusiast Points: 147 More actions · Answer 2

Satyananda.Reddy

SSC Enthusiast

Points: 147

October 25, 2010 at 6:22 am

#1241529

make variable datatype as Object and try

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Satyananda.Reddy (10/25/2010)
make variable datatype as Object and try

Why? The query has obviously the purpose the return a scalar result, hence the Single Row resultset.

A variable of type Object is typically needed for full result sets, so that you can use ADO.NET to work with them. Using Object in this case just overcomplicates things, as a script task/component has to be written to access the data.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Martin_A SSC-Addicted Points: 432 More actions · Answer 4

Lisset (10/21/2010)
Hi everybody, I have the follow description.
Variable
- Name=LoopLimit
- Scope=Package3
- Data Type=Int32
Execute SQL Task
-ResultSet=SingleRow
SQLStatement=select count(*) as Limit from BITACORA_CARGA_ARCHIVO
ResultSet
- Result name=Limit
- Variable Name=User::LoopLimit
When start to debugg, this run without error but the variable LoopLimit don't capture the value of SQLStatement. Maybe I need configure some item aditional for can capture the result?.
Thanks a lot by your help.

I guess it should not be necessary really but you could try naming the resultset to 0 instead of Limit and see if it makes any difference.

Lisset SSCommitted Points: 1858 More actions · Answer 5

I see the variablewhile is running using a breakpoint.

I has tried declaring the variable like Object, and don't capture the value.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Lisset (10/26/2010)
I see the variablewhile is running using a breakpoint.
I has tried declaring the variable like Object, and don't capture the value.

There is no need to declare the variale as type Object.

As Martin_A mentioned, try using 0 as the result name.

Finally, where is your breakpoint? At the Execute SQL Task itself, or at a task after the Execute SQL Task? Maybe you can try to write a small script task after the Execute SQL Task, displaying the value of the variable in a message box, if you want to be 100% sure.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lisset SSCommitted Points: 1858 More actions · Answer 7

My breakpoint is OnPostExecute from a task after the Execute SQL Task, and don't capture the value.

My optional solution was change the type from the column to nchar...but I follow with the doubt.