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

How to get the name of the error column from the errorcolumn output

Hi,

Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?

Regard,

pritesh

Possible, but not trivial. MS seem to consider this a design-time lookup, so you could use something that queries the package structure to get the info. Simon has written a transform that does this as well, note it needs both outputs to be able to get all the metadata. http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||Thanks for the info , i will try to implement it. It sounds useful !|||

Anonymous wrote:

Thanks for the info , i will try to implement it. It sounds useful !

And if you want to continue posting here, please change your display name from Anonymous to something else. Microsoft staff will be cleaning up those accounts with display names of Anonymous.