Getting sproc output into a DTS global

  • I want to be able to run a stored procedure once and make its output (a single value) available to a Transform Data Task for use as it processes each row. I've tried various ways to get the sproc output into a global variable, including a select, a return value, and an output parameter. I can get the Execute Query task to recognize that there's an output there, and map it to a global variable, but the global variable doesn't actually get updated when I run the task. If I replace the sproc with a select statement then I can set a global just fine. Why won't my sproc output to a global variable 'take?' Is there another way to accomplish this?

    Robert Merrill

    Berbee...Putting the E in Business

  • Interesting. I never tried this. Can you post some code from your DTS package? I'll take a look tomorrow.

    Steve Jones

  • Thanks for the prompt reply.

    A SQL Server veteran here at work suggested that the sproc was returning multiple result sets and that adding a SET NOCOUNT ON might get it working. He was right. Here's the sproc:


    CREATE PROCEDURE [insert_transform_record]

    @transform_name varchar(50),

    @source_file_name varchar(50)


    -- This next line was what it needed to

    -- make its results available to a DTS

    -- global.



    DECLARE @transform_key numeric(38,0)

    EXEC get_key_from_sequence 'seriesname', @next_key = @transform_key output

    INSERT INTO Transform












    SELECT @transform_key AS 'Transform_key'




    The frustrating part was that the DTS Execute Query in Designer recognized that output row value 'Transform Key' just fine but the binding had no effect when I ran the package. But it's working now. Here's the part of the DTS fragment that invokes the sproc and does the binding saved as Visual Basic):

    '------------- define Task_Sub2 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)

    Public Sub Task_Sub2(ByVal goPackage As Object)

    Dim oTask As DTS.Task

    Dim oLookup As DTS.Lookup

    Dim oCustomTask2 As DTS.ExecuteSQLTask2

    Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")

    Set oCustomTask2 = oTask.CustomTask

    oCustomTask2.Name = "DTSTask_DTSExecuteSQLTask_1"

    oCustomTask2.Description = "Execute SQL Task: undefined"

    oCustomTask2.SQLStatement = "insert_transform_record ?, ?"

    oCustomTask2.ConnectionID = 1

    oCustomTask2.CommandTimeout = 0

    oCustomTask2.InputGlobalVariableNames = """gvSourceFormat"";""gvSourceFileName"""

    oCustomTask2.OutputGlobalVariableNames = """gvTransformKey"""

    oCustomTask2.OutputAsRecordset = False

    goPackage.Tasks.Add oTask

    Set oCustomTask2 = Nothing

    Set oTask = Nothing

    End Sub


    Thanks again.

    Berbee...Putting the E in Business

    Robert Merrill

    Berbee...Putting the E in Business

Viewing 3 posts - 1 through 2 (of 2 total)

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