I've researched this question on the InterWeb and already found the INNUMERABLE responses that all claim the solution is to "set nocount on". however, that has done absolutely nothing for me.
Plus, my SQL code isn't a stored procedure; it's just SQL code., but it clearly and unambiguously selects columns from a CTE as the last statement.
it runs fine in SSMS, but the data flow task (oledb source), errs out with "ERROR - Error: A rowset based on the SQL command was not returned by the OLE DB provider.".
Are you able to run the code in SQL Server Management Studio and get the expected output?
How/where did you insert the SET NOCOUNT ON? Can you post your code?
Yes, as I mentioned it runs fine in SSMS. The expected output is about 10 rows. I put the SET NOCOUNT ON near the beginning of my SQL code in the data flow task, right near the top where I set transaction isolation level , etc.
Changing the code to sanitize and desensitize it is possible, however, I would like to ask whether the information I have given is sufficient first of all and if not, I will work on sanitizing it to post?
Thank you in advance for helping and look at this.
OK, now (after waiting long time because i was waiting on DBA to give perm to create sproc on that server), I turned my code INTO a stored procedure, and it worked fine.
Funny though - all of the web articles seem like the stored procedure was the problem - but no, having raw code was the problem - when I turned my data flow sql into "exec dbo.procedure", it worked fine. (even without using SET NOCOUNT ON). (?? weird)
Sorry, I had missed where you said you were able to run it successfully in SSMS. All other thoughts I have are things that you may already have tried. For example:
Run the profiler and then run your SSIS job to see what SQL Server is receiving.
Change your query to a placeholder query that returns only the representative data
My experience has been that even if you don't have SET NOCOUNT ON it works correctly if you have only statement. But if your stored procedure has multiple statements, for example, setting a variable, followed by selecting the data to be returned, then you need SET NOCOUNT ON.