June 23, 2010 at 7:03 am
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
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