Post reply

Using a Stored Procedure (containing temp tables) in an OLE DB Source task

  • June 23, 2010 at 7:03 am

    #141676

    I have a stored procedure that queries several tables from Server A. The result set from this proc will then be inserted into a Sql Server table on Server B.

    I'd like to use the stored procedure in an OLE DB Source task but when I do that it throws an error because it tries to validate my temp tables in the stored proc.

    I have tired setting DelayValidation=TRUE on all tasks and RetainSameConnection=TRUE on the connection. I still get an error when trying to click on Columns.

    Any suggestions?

  • June 23, 2010 at 9:30 am

    #1184032

    Your problem stems from the fact that sprocs don't provide a "contract" of what they are going to output, unlike views and functions. What you really need is the sproc to return the structure that it will eventually output.

    There are a couple methods to "trick" SSIS into using a sproc.

    One is to add something like:

    SELECT Field1 = CONVERT( int, 0 ),

    Field2 = CONVERT( varchar(30), '' ),

    Field3 = CONVERT( tinyint, 0 )

    WHERE 1=0

    What this does is always return a table with no rows with the correct structure, BUT must EXACTLY match what will eventually be output by the sproc. I find this method a little disconcerting because it returns two outputs but that is me..

    CEWII

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply