I'm using the following transformation script to import data from Excel:
One excel row has to be splitted into 5 records.
The field ID is my primary key.

How can I persuade SQL Server to automaticly set the primary key values ?
Unchecking "enable identity insert" does not help.

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************
Dim nRows
nRows = 5
Int myNextID

Function Main()

myNextID = DTSLookups("GetNextID").Execute(0)
myNextID = cInt(myNextID) + 1
DTSDestination("ID") = myNextID


if nRows > 0 then
Main = DTSTransformStat_SkipFetch
DTSDestination("R_State") = 1
DTSDestination("R_AccountType") = 0
DTSDestination("DateCreated") = now


Select Case nRows
Case 1
DTSDestination("Account_No") = DTSSource("144#963#1/03")
Case 2
DTSDestination("Account_No") = DTSSource("144#963#2/01")
Case 3
DTSDestination("Account_No") = DTSSource("144#963#3/10")
Case 4
DTSDestination("Account_No") = DTSSource("144#963#4/08")
Case 5
DTSDestination("Account_No") = DTSSource("144#963#5/06")
End Select
nRows = nRows - 1
else
nRows = 5
Main = DTSTransformStat_SkipInsert
end if

End Function



thanks for any help
mopetta

In message <10927EB7-93CA-4251-9A92-A7B182B4EE4E (AT) microsoft (DOT) com>, mopetta
<mopetta (AT) discussions (DOT) microsoft.com> writes
I don't think the script is correct. nRows will always be 5 as you code
it as that. You should store the state, nRows in a global variable, not
a script level variable.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org