OlE Db Command

Hi,

Iam trying to generate the increment value for a column by taking Maximum value of that field by using OLE DB Command Transformation.

This is the query iam using for that :
select max(ApplicationId)+1 as APPLICATIONID from Source..Applications

Iam finding difficulty in getting the 'APPLICATIONID' as the out put from OLEDB Transformation.OLE DB is not going to work with out any parameters ?can you please throw some light on this .First time iam using this transformation.

Another alternate solution is generating through script task (Thanks Jamie Thomson for his article).But i dont know how i can modify this to get the Max value instead of declaring some constant value for increment

Public Class ScriptMain
Inherits UserComponent
Dim Counter As Int32
Public Sub New()
Counter = 1006
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Counter += 1
Row.APPLICATIONTASKID = Counter

End Sub

End Class
Please me help me in finding one solution from the above two

Thanks
Niru
No replies ..

Am I dint explained properly or nobody are aware of this?

Thanks
Niru
|||

If I understand your question correctly, you're asking to generate a surrogate key id (1,2,3,..) which key id is started from last key id + 1 from your source table. If yes, you can try this:

On control flow, create an Execute SQL task with your select statement:

select isnull(max(ApplicationId),1) as APPLICATIONID from Source..Applications

Set Result Set property = Single Row

Create a variable, on the Result Set assign your variable.

On data flow, create a column, ex. ApplicationKeyID set to null. Follow by script component task; in Input Columns, select the ApplicationKeyID and Usage type = ReadWrite. On Script, type in the variable name for ReadOnlyVariables property. And your script will look something like this:

Public Class ScriptMain

Inherits UserComponent

Dim Counter As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.ApplicationKeyID = Variables.VariableName+ Counter

Counter = Counter + 1

End Sub

End Class

Hope this does not cause any more confusion for you...