SSIS Execute SQL Task Stored Procedure returning empty result sets

Question

I have a simple stored procedure that returns 1 row with 3 columns. I am trying to create an SSIS package that reuses the values in these columns later. If I run a simple select query everything is happy but when I run the stored procedure I get the following error:

[Execute SQL Task] Error: Executing the query "rs_UpdateMemberExtract" failed with the following error: "Unable to populate result columns for single row result type. The query returned an empty result set.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've set up an ado.net connection and the proc runs correctly in SSMS, I have the task set up like follows:

ConnectionType: ADO.NET
Connection: ServerName.connectionName
SQLSourceType: Direct Input
SQLstatment: Name of Proc (rs_UpdateMemberExtract)
IsQueryStoredProcedure: True

I have each parameter mapped to a variable and the direction set to Output

I have the name of each column that should be returned in the result set and mapped to the associated variable.

The task works if I set the ResultSet to 'None',

Any ideas What I've missed?

Thanks

What do you literally have for SQLStatement? I hope not "Name of Proc (rs_UpdateMemberExtract)". It should just be "dbo.rs_UpdateMemberExtract". — Tab Alleman
Ok, now why do you have the parameters direction set to Output? Your stored procedure returns output parameters, or a resultset, or both? What are you setting ResultSet to? Sounds like it should be set to "Single Row". — Tab Alleman
Initially it was returning output parameters but it doesn't know. I have changed this. It returns a 1 row with 3 columns of data. I have the ResultSet set to single row. Yet still get the same error as above — Readamus

MiguelH MiguelH · Accepted Answer · 2015-09-07T14:15:14

If an SQL Task Editor expects results returned in a row … but there are no records to return then the following SQL fills a null return with a space. Note : can also be used to return zero if a numeric “return row” is expected

SELECT '' + ISNULL ((SELECT x FROM y WHERE (z = ?)), '') AS  myfield

SSIS Execute SQL Task Stored Procedure returning empty result sets

1 Answers