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

Are you going to perform this task (getting the sequence) for every row
of data from the source file? If so then you may use Lookup query. If
not, then it is better that you use Execute SQL task. But remember even
if you use Lookup query, you will be doing exact same thing. Meaning,
you will be either you will be providing SQL query or the stored
procedure name to the Lookup query. Only advantage of the Lookup query
is that, you can use separate connection aside from source or
destination connection.

Thank you for your quick answer.

Yes, I have to get the sequence for every row of data from the source
table. I know I could do it with an Execute SQL Task calling the
procedure and some cursors (actually, I had implemented this solution
and it worked properly). However, I've been told explicitly not to do
this, so I'm forced to try the LookUp Query inside a DataTransform Task
way.

I've created 2 lookup queries:
A) The first one retrieves the idSequence value from the table
tblSEQUENCE
"SELECT idSequence FROM tblSEQUENCE"

B) The second one updates the table tblSEQUENCE incrementing by 1 the
idSequence value:
"DECLARE @idseq int
EXEC usp_sequence @idseq OUTPUT"
where the procedure is the one defined in the first post.

I'm uncapable to make it work into the Transformation ActiveX Script
given that first lookup query always retrieves the same value for
idSequence. However the second lookup query increments the table, but
the first lookup query doesn't seem to notice it.

This is the ActiveX Script (hope it helps )

"Function Main()

DTSDestination("PK") = DTSLookups("LKP_IdSeq").Execute()
DTSLookups("Increment").Execute()
Main = DTSTransformStat_OK
End Function"


Could you tell me what am I doing wrong?

Greetings,
David Grant

Yunus's Group ha escrito:

As far as I can see what you want to do is find the next sequence number
value from a table. You then want to use that to populate a PK in your
destination

DO NOT use a lookup. It will kill you and there is no need.

1. Read the sequence number into a GV in an ExecuteSQL task
2. IN your DataPump task you have an Active Script transform that
points ONLY to the PK column in the dest. NO source column.
3. In the script yourself you say


DTSGlobalVariables("XXX").Value = Cint(DTSGlobalVariables("XXX").Value)
+ 1
DTSDestination("PK") = DTSGlobalVariables("XXX").Value


That should give you what you need.

You will need to update the sequence table now with the value of the
global variable so that when you retrieve the sequence number again
using MAX you can get the next sequence number.

Allan



"icebold54 (AT) hotmail (DOT) com" <icebold54 (AT) hotmail (DOT) com> wrote

Thank you again Allan. This matches exactly with my objective.

David Grant

Allan Mitchell ha escrito: