Package validation error

Question

Package validation error

chinni-652949

Default port

Points: 1425
More actions
August 26, 2008 at 9:23 am

#123061

i need to execute a stored procedure with one input parameter in SSIS data flow.I executed well before,but after i created temp table inside a stored procedure it showing vs_neednewmetadata,validation error
my SP is some thing like this
create proc fields @feildid
as
create table #temp(,,,,,)
insert into select * from originaltable where id = @fieldid
drop table # temp
when i used my proc like
create proc fields @feildid
as
select * from originaltable where id = @fieldid
i could execute properly with no errors in data flow
but when i created a temp tabel i could not,as my procedure need a temp tabel i need to create that.
Thanks for you help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Turtle2008 SSC-Addicted Points: 414 More actions · Answer 1

Hi,

First of all, you have some typos in the code. In the case of code where it's not working, input parameter is @feildid, which should be @ fieldid.

The second place to look at might be the following:

You should add #temp after insert into.

create proc fields @feildid

as

create table #temp(,,,,,)

insert into select * from originaltable where id = @fieldid

drop table # temp

when i used my proc like

BTW- I'm not sure what are you trying to accomplish, since you are inserting to temp table and then drop it. So, you won't get anything after running the Sproc.

Thanks,

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 2

First of All I would rather use ADO.net connection when i am using Proc in the SSIS, especially when i am passing parameters. reasons: Much easier to map the parameters with good naming conventions, second (i don't know why) but i have failed many times when i am using OLEDB connections to exec my proc in SSIS< when same things worked with ADO.NET connections.

I would rather try to avoid ANY DDL in the proc. Instead of creating temp tbl in the proc Create a Stage table and do the insert into stage and at th every start of each ssis cycle truncate it .

Thanks,

chinni-652949 Default port Points: 1425 More actions · Answer 3

thanks for ur response.

Actualy that is not my SP.I have to retrive some columns (8)and the query contain many join which is taking long time to run.So what i did is insted of using the query(8 times) to retrive each column I created a temp table and inserted the required columns and i select the column now from temp table instead of using a query.

Sp working well in SSMS,but validation errors in SSIS

data flow

chinni-652949 Default port Points: 1425 More actions · Answer 4

alter PROCEDURE [dbo].[ResultSet] @ID int

AS

create Table #TempResult

(,,,,,,)

Insert into #TempResult

EXEC [dbo].[Retrivefields] @ID

select * from #TempResult

Drop Table #TempResult

I used in the data flow EXEC [dbo].[RetriveResultSet] 508

but it is not showing columns in columns tabb

Turtle2008 SSC-Addicted Points: 414 More actions · Answer 5

Hi,

Just curious, what exactly the validation error was. Most of the time, the error occurs due to data length mismatch or the use of wrong connection strings. Other than this, I don't see any reason for this not to work on SSIS. I don't think the problem is the use of temp table itself. However, please do try to use the variable of "table" data type instead of temp table. This surely will increase the performance and might save some tempdb space. Please do update me once you succeed.

Thanks