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
