hi friends,

First of all I'm sorry as I'm recalling this old post.

I came accros the same problem 'Using Stored Procedure with temp table in SSIS' , and accidently I saw this old post.

From different articles I red that 3 methods to solve this issue.

1) At the start of the SP give two SET statements

SET FMTONLY OFF

SET NOCOUNT ON

Bad about this approach is when we execute the package once, the sp will get executed 5 times (by some method we can limit it to 2 only not to 1), so if insert statement is there in SP it will execute 5 times.

2) Use table variable

This case we can't create any nonclustured index on the table variable also SQL doesn't maintain any statistics for table variable.

3) Use a never true condition to trick the OLEDB , ie at the top of the SP give the condition as

if(1=2)

begin

here give the select statement with all columns in the final result with proper datatype,ie

SELECT CAST(NULL AS INT) AS C1,CAST(NULL AS VARCHAR(30)) AS C2..

end

Now I will tell you my problem, my sp has an insert to a table, so if I go for the first option it will insert more than once for each execution of the package, in my sp I need some nonclustered index on the table so I cant go with second option also.

So I preffered third option ,but in this case it will work only if we directly pass the input of the SP , ie

exec sp_name 1,'abcd'

It is not working if I pass the value through paramter,like

exec sp_name ?,? 🙁

If any one has a solution for this please help me.

Once again sorry for recalling the old post.

Thanks & Regards,

MC