Using stored procedures within a DTS data transformation task

Posted on 2005-04-20
Last Modified: 2010-05-18
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.

Question by:Dawn_Bl
    LVL 17

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 17

    Expert Comment

    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
    select a,b,c from MyTable

    you do

    create function dummyFunc() returns @t table (a integer, b integer, c integer)
    insert into @t
    select a,b,c from MyTable

    Then dummyFunc 'looks like' a table for DTS, but acts like a stored proc - you can pass it parameters etc.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SSIS 2010 Convert to SSIS 2013 On Different Box 3 31
    Sql query to last records from table 3 43
    SQL Split output 2 16
    space 16 17
    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now