Getting the @@identity while in DTS from ActiveX Script or SQL Task?

[ZuluWarrior]

Hi

I am just learning DTS and want to know the correct way to get a value while working with DTS.

THe script below is my start script, it basically connects to the database and calls a Stored Procedure to add a record and gets back the @@identity and places it into a global variable in DTS.

Is this the only way, or best way? Can I use a "Execute SQL task" and place the @@identity value into a global variable?

Zulu Warrior

'***********************************************************
' Visual Basic ActiveX Script
'***********************************************************
Const adCmdStoredProc = 4
Const adInteger = 3
const adParamUnknown = 0
const adParamInput = 1
const adParamOutput = 2
const adParamInputOutput = 3
const adParamReturnValue =4

Function Main()
DTSGlobalVariables("StartTime&quot.Value = now

ConnectionString = "Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=TRL_Docushare_logs;Initial Catalog=LOGS;Data Source=SQL01"

set Conn=CreateObject("ADODB.Connection&quot
Conn.Open ConnectionString

'Create an ADO Command Object ot call the stored procedure
Set Cmd = CreateObject("ADODB.COMMAND&quot
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "sp_StartNewImport"

'This is the return value paramater, it is always defined and hold the Stored Procedure Return Code
cmd.Parameters.Append ( cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0, 0 ))

' Call the Stored Procedure : sp_StartNewImport
cmd.Execute

' Save the value of the new record that is being imported!
DTSGlobalVariables("ImportID&quot.Value = cmd.Parameters(0).value

set cmd = nothing

Conn.close
set Conn = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[rajeevnandanmishra]

Hi there,
I don't know much about Activex scripts. But you can get the latest value of @@identity any time by passing follwing statement to SQL server.
'SELECT @@identity as id'