How could I use a LookUp Query in a DataTransform Task to reach my goal?

I'm interested in doing the following thing:

I have this stored procedure:

CREATE PROCEDURE usp_sequence
@idseq int OUTPUT
AS
BEGIN TRAN
SET @idseq = (SELECT idSequence FROM tblSEQUENCE)
SET @idseq = @idseq + 1

UPDATE tblSEQUENCE
SET idSequence = @idseq

COMMIT TRAN

GO


which takes values from this table:

CREATE TABLE [dbo].[tblSEQUENCE] (
[idSequence] [int] NOT NULL
) ON [PRIMARY]
GO

I want to use the stored procedure to fill the PK column of this table
:

CREATE TABLE [dbo].[TEST] (
[PK] [int] NOT NULL ,
[Field_1] [varchar] (255) NULL ,
[Field_2] [varchar] (255) NULL ,
[Field_3] [varchar] (255) NULL
) ON [PRIMARY]
GO


I DO KNOW I could use an "Execute SQL Task" (the easiest and quickest
way) with some elaborated SQL code but I've been asked not to do so.
Now, I'm trying to do it with a LookUp Query associated to a
"DataTransform Task" however I'm unable to succeed in it.

Anybody could tell me how could I use a LookUp query in a "Data
Transform Task" to assign an incremental value to the PK column in the
same fashion as would do the procedure?

Thank you from beforehand to anyone who can answer this question.


Greetings,
David Grant

.

Relevant Pages

  • Transactions and cursors.
    ... I want to wrap a stored procedure in a BEGIN TRAN - COMMIT TRAN and abort if ... any update fails. ... The stored procedure uses a cursor and inserts or updates ... a single row in a single table for each iteration of the cursor. ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert SQL does not work in stored procedure.
    ... I thought the "begin tran" and "commit tran" wraps the insert in an explicit ... >> On one of our development servers I've run into a problem with Insert ... The stored procedure works on other servers and used to work on ... >> BEGIN TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Sybase Stored Procedure issue
    ... I am using DBI to connect to Sybase and run a stored procedure. ... PRINT must be of type CHAR or VARCHAR. ... LogErrorAndDie ("Sybase Stored procedure p_clearalldown failed ...
    (comp.lang.perl.modules)
  • Re: Muiltiple Stored Procedure Within Transaction
    ... I want all of my transaction code within my stored procedure. ... >>Commit Tran ... > if (success) ...
    (microsoft.public.dotnet.framework.aspnet)
  • adoutputparam and commit trans
    ... are there any examples of the correct way to use commit tran in ... a stored procedure and then ... set an output parameter with the result. ... in the sql code ...
    (microsoft.public.vb.database.ado)