How to get the Out put from the Stored Procedure

Hi Every body,

i am trying to execute a stored procedure and want the out put to be passed to another database table.

I tried to create a OLEDB Source and gave the Exec Procedure Statement. I tried to see the preview and able to see the out put results. But when i am clicking on the columns to map to the destination i am not able to see the metadata.

Can you guys pls let me know how to do this.

thanx in advance..

Regards,

Dev

If your stored procedure is complex, you may need to insert a SELECT that returns the expected columns as the first statement in your stored proc. You can add a WHERE clause like WHERE 0 = 1 to ensure that no rows are returned.

When you are using a multiple operation stored procedures, a lot of tools (SSIS included) use the first resultset to determine the metadata for the stored proc. You can add a "dummy" resultset to ensure that it gets the right metadata.

|||

Hi,

I really appreciate your early reply. i will give a try and let you know the out come.

Thanx & Regards,

Dev

|||

hi,

can you pls let me know how to write a dummy SQL Statement for the out put, as i am not able to get the table dosent existsin the Database

Dev

|||

Hi,

I tried a sample like this

1. Created a SP

ALTERprocedure [dbo].[usp_test]

as

begin

declare @.error_number int,

@.row_count int

CREATETABLE #temp (

test1 varchar(50),

test2 varchar(50)

)

SELECT*from #temp where 0=1;

end

2. Added OLEDB SOURCE -- > added as a SQL Command EXEC usp_test.

3. now when i click on the metadata it's not showing up the coloumns

Please suggest what i am doing wrong

Regds,

Dev

|||

SELECT '' AS mystringcolumn, 1 as myintcolumn WHERE 1 = 0

The columns here need to match your expected resultset, both in name and type.

|||You need to put the select before the create table. See my post above.|||

Add this t-sql at the end of the storedproc

declare @.sql varchar (50 )

selelct @.sql='select * from #temp where 0=1'

Exec (@.sql)

give it a try