• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

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
0
Dawn_Bl
Asked:
Dawn_Bl
  • 2
1 Solution
 
BillAn1Commented:
you can't use a stored procedure as as source in a Transform Data Task. A stored procedure does not have a defined output structure that you can map a transformation into. A stored procedure can result in one data set, or multiple datasets, or none....depending on the values when it is run. There is no way for the DTS designer to determine what the output of a stored proc is going to look like at design time.

If you want you can have your stored procdure run and load the data into a table as a Query Task , and then have a Transform Data Task load the data from the table.
Alternatively you could rewrite your stored procedure as a table function. This does return a defined record set which a DTS package can map to.
0
 
Dawn_BlAuthor Commented:
Thanks... the first part confirms my suspicions. Unfortunately the second part is what I am trying to avoid... direct access to tables for security/permission reasons.
0
 
BillAn1Commented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now