Dawn_Bl
asked on
Using stored procedures within a DTS data transformation task
When creating a DTS package on MS SQL Server 2000, how can I use a stored procedure as the data source on a Transform Data Task. When I specify SQL Query for the data source tab and enter a query such as "exec sp_some_proc", I can preview the result set from the stored proc, but the transformation is invalid since the package can't seem to tell what columns to expect as a result of the stored proc call.
Thanks,
Dawn
Thanks,
Dawn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can still use your stored proc to access your tables, and have the stored proc dump results into a table that only this table is accessible to the user.
ALternatively, as I say you can create a function which is basiclaly similar to a procedure in erms of limiting access etc, but it returns a table, which can then be used as your source.
i.e. instead of
create procedure dummyProc
as
begin
select a,b,c from MyTable
end
you do
create function dummyFunc() returns @t table (a integer, b integer, c integer)
as
begin
insert into @t
select a,b,c from MyTable
return
end
Then dummyFunc 'looks like' a table for DTS, but acts like a stored proc - you can pass it parameters etc.
ALternatively, as I say you can create a function which is basiclaly similar to a procedure in erms of limiting access etc, but it returns a table, which can then be used as your source.
i.e. instead of
create procedure dummyProc
as
begin
select a,b,c from MyTable
end
you do
create function dummyFunc() returns @t table (a integer, b integer, c integer)
as
begin
insert into @t
select a,b,c from MyTable
return
end
Then dummyFunc 'looks like' a table for DTS, but acts like a stored proc - you can pass it parameters etc.
ASKER