Manualy Create IDENTITY Column inside ControlFlow

Dear Friends... I'm having a problem...

I want to manually create the identity column for a table...

I have some dataflws, and in each dataflow I insert values in this table...

I need to start the controlflow in a SQL task to get the last ID and save it in a global variable with name D_INST_IDENTITY.

And in each dataflow I have a script component transform, to get the ID... using a local variable COUNTER! and for each row I increment this value...

Until this step there is no problem... the problem starts here...:

I need to refresh the global variable in the final of each dataflow in order that in the next sequence dataflow I have D_INST_IDENTITY refreshed......

D_INST_IDENTITY = D_INST_IDENTITY + COUNTER

How can I do it? I have a RowCount transform next the script component, but generates errors...

What do you think I can do it?
Thanks!!

Can you not just use an Execute SQL Task to get the current max value in the target table and store is in a variable?

-Jamie

|||

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

|||

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

|||

PedroCGD wrote:

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

You don't do it IN the data-flow, you do it BEFORE the data-flow

PedroCGD wrote:

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

So you put an Execute SQL Task before each data-flow. Is there a problem with doing that?

(There are actually some cleverer ways of doing it but for now - let's keep it simple.)

-Jamie

|||

PedroCGD wrote:

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

It really helps if, when you get an error, you tell us the error emssage.

I'm pretty sure I can guess what it is though. Try this:

SELECT ISNULL(MAX(INST_ID), 0) +1 FROM Instrumento

-Jamie

|||

Dear Jamie,

In order I have teh best performance, I avoid to use multiple SQL tasks. Imagin that I have 20 dataflows? Why I need to execute a query in database for each dataflow, if I have a counter to automatically give me the IDENTITY column? Do you think is more consistent using SQL Task?

I have found the solution to read a global variavel, and change it inside the script component transform...

I initialize in the Script Component properties the variable INST_IDENTITY as ReadWritevariables, and in order to avoid errors inside the PreExecute method when I read the value, I changed the code as you can see:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim counter As Integer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

counter = counter + 1

Row.TesteKey = counter

End Sub

Public Overrides Sub PreExecute()

Dim vars As IDTSVariables90 = Nothing

Me.VariableDispenser.LockForRead("User::INST_IDENTITY")

Me.VariableDispenser.GetVariables(vars)

counter = CType(vars(0).Value, Integer)

vars.Unlock()

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("INST_IDENTITY").Value = counter

MyBase.PostExecute()

End Sub

Protected Overrides Sub Finalize()

MyBase.Finalize()

End Sub

End Class

I was having errors if I read the value of a ReadWriteVariables inside the PreExcute Method, so, using VariableDispenser I can do it!!

Thanks!!

|||

PedroCGD wrote:

I have found the solution to read a global variavel, and change it inside the script component transform...

Cool! That is the "other way" that I alluded to earlier.

-Jamie