Hello,
Newbie here using DTS. I have written a script that should read
records from 1 table and insert or update the records in another table.
These tables exist in the same database. When I run the script I get
a message that it completed succesfully. However, only half of the
records that should be inserted actually are. This is consistent
regardless of the number of records that are to be inserted. I am
probably missing something very simple, just don't know any better.
Any help on this would be greatly appreciated. Server is SQL 2000.
Here is the code:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function DoubleUpSingleQuotesCust(strInput)
DoubleUpSingleQuotesCust = Replace(strInput, "'", "''")
End Function

Function DoubleUpSingleQuotesDescr(strInput)
DoubleUpSingleQuotesDescr = Replace(strInput, "'", "''")
End Function

Function Main()
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H8
Const adCmdText = &H1
Const adCmdTable = &H2
Const adCmdStoredProc = &H4
Dim countr
Set SourceConn = CreateObject("ADODB.Connection")
Set SourceRecordset = CreateObject("ADODB.Recordset")
Set srchrecordset = CreateObject("ADODB.Recordset")

SourceConn.Open "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data
Source=(LOCAL); Initial Catalog=OCRData; User ID = *****; password =
*****"

' -----Delete all records with NO order entry date-----
sqldelstr = "DELETE FROM OnlineCell WHERE (((OnlineCell.ORder_Ent) Is
Null));"
SourceConn.Execute sqldelstr

' ------Delete all records with NO order number-----
sqldelnullord = "DELETE FROM OnlineCell WHERE (((OnlineCell.Order_Num)
Is Null));"
SourceConn.Execute sqldelnullord
SQLCmdText = "Select * from Onlinecell"

SourceRecordset.Open SQLCmdText, SourceConn, adOpenStatic
countr = 1


If SourceRecordset.recordcount < 1 Then
MsgBox " There are no records found. Return a Failure code"
Main = DTSTaskExecResult_Failure
Else
Dim ordnum, line, cellnum, orddue, shipvia, qty, ptno, rev,
cust, sales, desc, ordent, chr, stat, DestSQL
For countr = 1 To SourceRecordset.recordcount
ordnum = SourceRecordset.Fields("Order_Num").Value
line = SourceRecordset.Fields("Line").Value
cellnum = SourceRecordset.Fields("Cell_Num").Value
orddue =
CDate(SourceRecordset.Fields("Order_Due").Value)
shipvia = SourceRecordset.Fields("Ship_Via").Value
qty = SourceRecordset.Fields("Qty").Value
qtyneed = SourceRecordset.Fields("Qty").Value
ptno = SourceRecordset.Fields("Pt_No").Value
rev = SourceRecordset.Fields("Rev").Value
cust = SourceRecordset.Fields("Cust").Value
custD = DoubleUpSingleQuotesCust(cust)
sales = SourceRecordset.Fields("Net_Sales").Value
desc = SourceRecordset.Fields("Descr").Value
descD = DoubleUpSingleQuotesDescr(desc)
ordent =
CDate(SourceRecordset.Fields("Order_Ent").Value)
chr = SourceRecordset.Fields("CHR").Value
srchordnum = " ' " & ordnum & " ' "
srchline = " ' " & line & " ' "
Status = "Open"
strsql = "SELECT * FROM cellexp WHERE (Order_Num = " &
srchordnum & " AND Line = " & srchline & ")"
srchrecordset.Open strsql, SourceConn, adOpenStatic

If srchrecordset.recordcount < 1 Then
DestSQL = "INSERT INTO cellexp (Cell_Num,
Order_Due, Ship_Via, Qty, QtyNeeded, Pt_No, Rev, Order_Num, Line, Cust,
Net_Sales, Descr, Order_Ent, Status, CHR) VALUES ('" & cellnum & "','"
& orddue & "','" & shipvia & "'," & qty & "," & qtyneed & ",'" & ptno &
"','" & rev & "','" & ordnum & "'," & line & ",'" & custD & "'," &
sales & ",'" & descD & "','" & ordent & "','" & Status & "','" & chr &
"')"
SourceConn.Execute DestSQL
ElseIf srchrecordset.recordcount = 1 Then
stat = srchrecordset.Fields("Status").Value
If stat = "Open" Then
qtyneed =
SourceRecordset.Fields("Qty").Value -
(srchrecordset.Fields("Qty").Value -
srchrecordset.Fields("QtyNeeded").Value)
DestSQL = "UPDATE cellexp SET
Cell_Num ='" & cellnum & "', Order_Due ='" & orddue & "', Ship_Via ='"
& shipvia & "', Qty =" & qty & ", QtyNeeded =" & qtyneed & ", Pt_No ='"
& ptno & "', Rev ='" & rev & "', Order_Num ='" & ordnum & "', Line =" &
line & ", Cust ='" & cust & "', Net_Sales =" & sales & ", Descr ='" &
desc & "', Order_Ent ='" & ordent & "' WHERE (Order_Num = " &
srchordnum & " AND Line = " & srchline & ")"
SourceConn.Execute DestSQL
ElseIf stat = "Closed" Then
qtyneed =
SourceRecordset.Fields("Qty").Value -
(srchrecordset.Fields("Qty").Value -
srchrecordset.Fields("QtyNeeded").Value)
If qtyneed = 0 Then
DestSQL = "UPDATE
cellexp SET Cell_Num ='" & cellnum & "', Order_Due ='" & orddue & "',
Ship_Via ='" & shipvia & "', Qty =" & qty & ", QtyNeeded =" & qtyneeded
& ", Pt_No ='" & ptno & "', Rev ='" & rev & "', Order_Num ='" & ordnum
& "', Line =" & line & ", Cust ='" & cust & "', Net_Sales =" & sales &
", Desc ='" & desc & "', Order_Ent ='" & ordent & "' WHERE (Order_Num =
" & srchordnum & " AND Line = " & srchline & ")"
SourceConn.Execute
DestSQL
Else
DestSQL = "UPDATE
cellexp SET Cell_Num ='" & cellnum & "', Order_Due ='" & orddue & "',
Ship_Via ='" & shipvia & "', Qty =" & qty & ", QtyNeeded =" & qtyneeded
& ", Pt_No ='" & ptno & "', Rev ='" & rev & "', Order_Num ='" & ordnum
& "', Line =" & line & ", Cust ='" & cust & "', Net_Sales =" & sales &
", Desc ='" & desc & "', Order_Ent ='" & ordent & "', Status = Open
WHERE (Order_Num = " & srchordnum & " AND Line = " & srchline & ")"
SourceConn.Execute
DestSQL
End If
End If
End If
SourceRecordset.MoveNext
srchrecordset.Close
countr = countr + 1
Next
End If
Main = DTSTaskExecResult_Success
End Function

Hello jberger (AT) veeder (DOT) com,

Why are you using VBScript inside DTS? Why not use DTS to do what you want?

This could just as easily be a .vbs file.

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com