Seeking Suggestion to design a SSIS Package

SSIS and Import/Export
1

i am using ado.net connection and SP

to pull data from table to excel destination.

Problem is i have used temp table in my SP, and its giving error.

even i have make delay validation and retain same connection as true, but still error occured.

can any one suggest to rectify the issue.

2

I believe your problem is an artifact of the way SSIS works. When it process as source component at design time, it actually runs your stored procedure but with SET FMTONLY ON in effect. It does this to get the metadata so it can build the correct column definitions. If your proc returns data from a temp table, that table does not exist since with SET FMTONLY ON in effect, the code in your proc that creates the temp table is scanned, but not run. One solution I have used is to fake the result set:

create proc ...
as
begin
if 1 = 0 begin -- this will actually execute if SET FMTONLY ON is in effect
    -- return same result set as real query, below
    select 1 as col1, 
                2 as col2,
                ...
end

create table #temp ...
...

select col1, col2, ... from #temp