October 18, 2001 at 4:02 pm
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
October 18, 2001 at 6:26 pm
Interesting. I never tried this. Can you post some code from your DTS package? I'll take a look tomorrow.
Steve Jones
October 19, 2001 at 7:24 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply