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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxxxx" <icebold54@xxxxxxxxxxx> wrote in message news:icebold54@xxxxxxxxxxx:

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