How to handle the incremental values

[SqlHunter]

I have an ActiveX script to migrate the data and the source data is join of different tables.


Function Main()

DTSDestination("Spike_id")---> This is an incremental value and source doesn't have any value. so I have to go on increment it.Normally it is suppose to increment automatically when u insert but its not working and gives me an error.So i have to get the max of the spike_id and then add one to it.So please help me how to increment this value

DTSDestination("emp_id") = DTSSource("emp_id")
DTSDestination("Dept") = DTSSource("Dept_System_default")
DTSDestination("loan_amount") = DTSSource("amount")
DTSDestination("Entry_dateoriginal_upb")= DTSSource("original_date")
DTSDestination("interest_rate") = DTSSource("interest_rate")
DTSDestination("created_by") ="Admin"
DTSDestination("create_date") =Date

Main = DTSTransformStat_OK

End Function