SSIS validation error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SSIS validation error

Author

Topic

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 11:25:07

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 13:27:23

what are trying to do with temporary table?

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 14:25:46

I need to retrive column names 8 times in the SP and the query to retrive contains many joins so its taking long time if i use the query each time.What I did not i got all the column names to a temp table and using the colums from temp table instead of using the query each time when needed.

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 14:55:30

alter PROCEDURE [dbo].[Gus_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].[Gus_RetriveResultSet] 508

but it is not showing columns in columns tabb

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-08-26 : 14:59:35

Check the below links.

http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

Thanks
Karunakaran

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 15:26:49

THE links are helpful....but insted of creating a procedure they asked create a function but i have a input parameter to procedure.......

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-08-26 : 16:15:09

You can create a table value function which takes input parameters, that should help you out.

Thanks
Karunakaran

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 16:40:27

create function [dbo].[esultSet]
(
@ID int
)

as
Begin

create Table #TempResult
(,,,,,)

Insert into #TempResult
EXEC [dbo].[fields] @ID
select * from #TempResult

Drop Table #TempResult
end

when i create a function it shoeing error like cannot access temp tables winth in function

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 16:50:08

create function [dbo].[ResultSet]
(
@ID int
)
returns @TempResult table (a,d,c)
as
Begin

Insert @TempResult
EXEC [dbo].[fields] @ID

return
end

ERROR:

Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-08-26 : 19:13:38

As far as I know, you cannot call a stored proc inside the function.Instead of calling the sp, whatever you do in the stored proc, do it in the udf.

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-26 : 21:12:38

table values function

select * from [dbo].[ResultSet] ?I could not click OK button I am getting error

Error at Data Flow Task [OLE DB Source 1 [2932]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-08-27 : 11:12:43

Assuming ResultSet is a udf with out any input parameters, it should be
select * from [dbo].[ResultSet]()

Thanks
Karunakaran

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-27 : 11:16:28

for table values function i found like

"select * from [dbo].[ResultSet]@ID =" + (DT_WSTR,20)@MySSISVariable

but to slove my problem i went back to my SP and changes all temp tables to temp variables and i did not find the error this time vs_metadata.

thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources