Getting sproc output into a DTS global

• merrillrt

  Grasshopper

  Points: 14

  More actions

  October 18, 2001 at 4:02 pm

  #104685

  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
  merrill@berbee.com
  
  Berbee...Putting the E in Business

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737868

  More actions

  October 18, 2001 at 6:26 pm

  #419128

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

  Steve Jones

  steve@dkranch.net

• merrillrt

  Grasshopper

  Points: 14

  More actions

  October 19, 2001 at 7:24 am

  #419132

  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)

  AS

  -- This next line was what it needed to

  -- make its results available to a DTS

  -- global.

  SET NOCOUNT ON

  BEGIN TRANSACTION

  DECLARE @transform_key numeric(38,0)

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

  INSERT INTO Transform

  (

  Transform_key,

  Transform_transformName,

  Transform_sourceFileName

  )

  VALUES

  (

  @transform_key,

  @transform_name,

  @source_file_name

  )

  SELECT @transform_key AS 'Transform_key'

  COMMIT TRANSACTION

  GO

  --------------------------------------------

  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
  merrill@berbee.com
  
  Berbee...Putting the E in Business