1

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.

SW2018
  • 31
  • 1
  • 5
  • 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 Jan 11 '19 at 15:22
  • Are the SP parameters both input parameters? And how are you mapping the results to the object variable? – userfl89 Jan 11 '19 at 15:31
  • 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 Jan 11 '19 at 15:36
  • Does the SP return a result set for the parameters that you used to execute it from SSIS? – userfl89 Jan 11 '19 at 15:45
  • 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 Jan 11 '19 at 15:58

2 Answers2

2

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

SW2018
  • 31
  • 1
  • 5
0

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.

howserss
  • 1,139
  • 1
  • 8
  • 12