Post reply

Store Procedure as OLE DB source

  • December 15, 2006 at 10:08 am

    #368571

    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

  • December 17, 2006 at 11:19 pm

    #678571

    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

  • December 18, 2006 at 2:46 am

    #678583

    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

  • December 18, 2006 at 7:32 pm

    #678847

    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