How to fix error - "No disconnected record set is available for the specified SQL statement."

1
votes

I am executing a stored procedure to collect the data set into a variable of object type. The stored procedure has 2 parameters and works fine. When I use the same stored procedure in 'execute Sql task' in ssis I get the error message as "[Execute SQL Task] Error: Executing the query "EXEC [dbo].proc_procname] @CD1 = ?, @C..." failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

2
ssisexecute-sql-task
The stored procedure is used to retrieve a data set from multiple files, a temptable and CTE, for a report to be exported to excel. – SW2018
Are the SP parameters both input parameters? And how are you mapping the results to the object variable? – userfl89
Yes both are input parameters for the stored procedure and result set is mapped to a object type variable via result set option in the editor window. – SW2018
Does the SP return a result set for the parameters that you used to execute it from SSIS? – userfl89
Yes, it does. Thanks for your thoughts. The error is resolved. The input parameter was defined as string, I was passing it with quotes. removal of quotes, resolved the error. – SW2018

2 Answers

2
votes

The input parameter was defined as string, removal of quotes from value of that parameter, resolved the error.

0
votes

I was also getting this error. In my case it was a large amount of inline T-SQL. At the top it had a use and a few set statements with go in between. I commented out all the Go statements and that solved my issue.