Autofill an identitly field using an activex script in DTS

I have a vbscript that copies and transforms data from an access table to and existing MSSQL table. The destination table has an identity column, and I need to autopopulate it as the data is loaded. I'm thinking the line of code would look something like:

DTSDestination("id") = ?

where ? is a command to autoincrement the id by 1. Hopefully this is just a syntax question. Thanks.
dsachtlebenAsked:
Who is Participating?
 
nmcdermaidCommented:
PS a SQl Server IDENTITY column automatically fills in this incremental value
0
 
nmcdermaidCommented:
Use the DTS Package Properties dialog to create a global variable. This global variable can then be accessed in code.


For example:


Function Main()
   DTSDestination("FieldName") = DTSGlobalVariables("NewID").value
   DTSGlobalVariables("NewID").value = DTSGlobalVariables("NewID").value + 1
   Main = DTSTaskExecResult_Success
End Function



0
 
dsachtlebenAuthor Commented:
Thanks for help! I am obviously new to DTS but this was a very clear solution.
0
 
nmcdermaidCommented:
Don't get too familiar! It will probably change next version of SQL Server! than again that is a couple of years away.

There are a lot of handy exmpaples here: SQLDTS.COM
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.