DTS & SCOPE_IDENTITY()

Question

DTS & SCOPE_IDENTITY()

Sean Gough

SSC Journeyman

Points: 79
More actions
February 15, 2005 at 2:50 pm

#163350

As part of a DTS package I need to insert data into a table and then grab the identity of the just inserted record for use later on in the DTS package. To accomplish this, I wrote a stored procedure of the variety...
INSERT INTO tblName (valuue1, value2) VALUES (@parameter1, @parameter2)
SELECT SCOPE_IDENTITY() AS new_id
GO
Now, when I run this in Query Analyzer or in VB it works great. It returns a single record with the correct seed value. However, when I run this via an Execute SQL Task and try to save the output to a global variable it doesn't work. The step executes correctly and inserts the record, but the global variable never gets updated.
Can someone let me know what I am doing wrong?
Thanks in advance!

Viewing 11 posts - 1 through 10 (of 10 total)

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

Put the code you've got into a stored procedure. Pass the parameters for the insert in as input parameters, capture the return from SCOPE_IDENTITY() in the output parameters.

--------------------
Colt 45 - the original point and click interface

Sean Gough SSC Journeyman Points: 79 More actions · Answer 2

Sorry if I was unclear, but this already is in a stored procedure. I tried what you mention, but unless I return a recordset (rather than just an output parameter) then the Execute SQL Task output paramters tab does not allow me to map output to global variables via the Row Value - Parameter Mapping feature.

Basically, I need the stored procdures output saved into a global variable. According to BOL the method I use *should* work with the Execute SQL Task in DTS, however, it doesn't?

I use this method in other situations to put an entire rowset into a global variable, and that works fine...

Sean Gough SSC Journeyman Points: 79 More actions · Answer 3

Nevermind, got this to work finally! I changed my stored procedure to this...

CREATE PROCEDURE sp_test(@paramater1 varchar(10), @parameter2 varchar(100), @new_id int OUTPUT)

AS

SET NOCOUNT ON

INSERT INTO tblName (value1, value2) VALUES (@paramater1 , @parameter2 )

SELECT @new_id = SCOPE_IDENTITY()

RETURN

SET NOCOUNT OFF

GO

I then changed the SQL in the Execute SQL Task to the following...

DECLARE @new_id int

EXEC sp_test ?, ?, @new_id OUTPUT

SELECT @new_id AS new_id

At first when I tried the parameter mapping I got the following error...

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Syntax error or access violation

But fortunately this is a design-time error only and I was able to get around it by using a substitute statement, mapping my paramaters and then putting my original SQL back in via a disconnected edit.

Cheers!

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

Personally, I've found returning the output as a recordset makes it much easier to return the value to DTS. Output parameters have always needed stupid work-arounds like the one you did above (good job on figuring it out, though!).

Signature is NULL

philcart SSC-Forever Points: 47795 More actions · Answer 5

This is all you need in the stored procedure.

CREATE PROCEDURE sp_test(
    @paramater1 varchar(10)
    , @parameter2 varchar(100)
)
AS
SET NOCOUNT ON 
INSERT INTO tblName (value1, value2) VALUES (@paramater1 , @parameter2 )
SELECT @new_id = SCOPE_IDENTITY()
GO

This should allow you to assign both the input and output parameters without oo much problems.

--------------------
Colt 45 - the original point and click interface

Sean Gough SSC Journeyman Points: 79 More actions · Answer 6

Is there an advantage to that over what I have done?

philcart SSC-Forever Points: 47795 More actions · Answer 7

You don't have

"...get around it by using a substitute statement, mapping my paramaters and then putting my original SQL back in via a disconnected edit..."

Makes maintenance a whole lot easier

--------------------
Colt 45 - the original point and click interface

Sean Gough SSC Journeyman Points: 79 More actions · Answer 8

Hmmm...but that is exactly what I did the first time and for some reason, while the task would execute fine and I had the output parameter mapped to global variable, it simply wouldn't update that global variable upon execution.

Weirdness. I think I'll let it be now that it works...although I did add a comment to the package to walk someone through the workaround in the event maintenance is needed.

Thanks for your help!

-Sean

philcart SSC-Forever Points: 47795 More actions · Answer 9

If you added the comment as a Text Annotation, don't rely on it staying there. DTS is notrious for losing Text Annotations and the layout of the tasks.

--------------------
Colt 45 - the original point and click interface

Calvin Lawson SSChampion Points: 11030 More actions · Answer 10

By returning as a recordset I meant:

CREATE PROCEDURE sp_test

@paramater1 varchar(10) , @parameter2 varchar(100))

AS

SET NOCOUNT ON

INSERT INTO tblName (value1, value2) VALUES (@paramater1 , @parameter2 )

SET NOCOUNT OFF

SELECT SCOPE_IDENTITY() Identity

GO

Then you capture this as a "Row Value" under the "Output Parameters" screen. Like I said before, I've had problems with output parameters when I'm also using input parameters. I found a BOL article that talked about this problem, but I can't find it now. Using "Row Values" always works for me, though.

Signature is NULL