SSIS should not be showing this error ?

Question

Post reply

SSIS should not be showing this error ?

etl.laptop

SSCrazy

Points: 2267
More actions
September 29, 2013 at 12:31 am

#298419

I have an Execute SQL task which gives returns only one row with one column - a number. I set my result set to single row. Despite that, the task fails. Why ? How do I fix it ?
[Execute SQL Task] Error: There is an invalid number of result bindings returned for the
ResultSetType: "ResultSetType_Rowset".

Viewing 4 posts - 1 through 3 (of 3 total)

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

larskandersen Ten Centuries Points: 1232 More actions · Answer 1

Did you map the result to a variable in the Result Set pane for the Execute task?

etl.laptop SSCrazy Points: 2267 More actions · Answer 2

This is what my book says -

(1) Make an exec sql task (with connection) and use the following settings -

Result set = single row

SELECT COUNT(*) AS NR_ROWS FROM SalesLT.Customer

(2) Join a script task to SQL task and use the VB script -

MsgBox (Dts.Variables(0).Value, MsgBoxStyle.Information)

Run the package.

I did all this and nothing worked. Wait, I think that 0 might be the ResultName that you mentioned.

I created a variable called intNum and set ResultName = 0, for this variable.

The SQL task worked and the script failed with the error -

at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)

at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

------------------------------------------------------------------------------------------------------------

Now, I set the readonly variable in script task to intNum and script VB code to

MsgBox(Dts.Variables("intNum").Value)

and it works. I don't know what is the meaning and purpose of this -

MsgBox (Dts.Variables(0).Value, MsgBoxStyle.Information)

larskandersen Ten Centuries Points: 1232 More actions · Answer 3

Dts.Variables(0).Value is the value of the first variable in the collection of variables defined in the Control Flow.

I don’t know what book you are referring to, however to look at values during run time, I think you should get familiar the debugging options within BIDS instead of scripting.

Right click on your Execute task, select “Edit Breakpoints”, select your break points, pre- and post-execute will be good options in this scenario. Click OK and execute package. Now your package will stop at your selected breakpoints and you can watch your variable values be clicking on “Locals” tab, it will most likely be at the bottom of bids.