Store Procedure as OLE DB source
-
My colleague showed my this earlier and I was wandering if anyone has any ideas.
If you use a stored procedure which has multiple statements as the source for a data flow task you cannot select the columns returned.
Here is a very simple SP which will sho the problem.
Create Proc spTest
As
Select 999 as mycolumn Into #mytable
Select * from #mytable
Go
If you create this SP, then create a new package with a dataflow task using the SP as the SQL command for a OLE DB source you cannot view the the columns output from the query. Therefore the Source is useless.
Oddly enough you can preview the result with any problems.
SSIS appears to pickup the result of the first statement executed within the SP. You can see this by changing the SP to
Alter Proc spTest
As
Select 0 as anothercolumn where 1=0
Select 999 as mycolumn Into #mytable
Select * from #mytable
Go
Does anyone have any thoughts on this?
Thanks
Daniel
-
Hi,
This is actually caused by your settings if you add the following to your procedure it should work.
SET
FMTONLY OFF; SET NO_BROWSETABLE OFF
-
Hey,
Thanks for you response. I have added these settings to my SP and now I can select the columns and preview the data. However now I get the following error:
[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
As before the query works in query analyser and I can preview the data through the SSIS data source control. I doesn't work when I execute the package.
Thanks
Daniel
-
Hi Daniel,
This is a different issue, but I think setting NOCOUNT to ON should fix this problem.
For some reason it thinks that when it receives the rowcount , thats its completed and returns without selecting the actual row.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply