Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data Driven Query, UPDATE only processing one source row

Status
Not open for further replies.

FiMacAU

Programmer
Feb 25, 2009
2
US
Hello,

I've got a DTS package that includes a single Data Driven Query whose purpose is to update address records in a SQL Server table (destination,Connection 2) with address information that is pulled from a csv file (source,Connection 1). The ActiveX script that I've
written to perform the details of the transformation is shown below.
The problem I'm having is for whatever reason the package only updates the first record that it selects from the source (Connection 1). The source contains 150 rows and when the package runs it claims that it has processed 150 rows, but only one is actually updated.
Any help!?!? Thanks in advance.


'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

Function Main()

on error resume next

Dim strAddrLine1
Dim strDateCheck
Dim strIDCheck
Dim errText1
Dim objFSO
Dim objStream
Dim strID
Dim strDate
Dim count

count = 0

const OUTPUT_FILE = "c:\FionDTS.txt"
const fsoForWriting = 2

set objFSO = CreateObject "Scripting.FileSystemObject")

set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)

strDate = Date()
objStream.WriteLine(strDate)
objStream.WriteBlankLines(1)

errText1 = "Starting error log"
objStream.WriteLine(errText1)
objStream.WriteBlankLines(1)

strID = DTSSource("Id")
objStream.WriteLine("Current Student ID is " &strID)
objStream.WriteBlankLines(1)

strAddrLine1 = DTSSource("AddrLine1") & ""
objStream.WriteLine("Current Addr1 is " &strAddrLine1)
objStream.WriteBlankLines(1)

strIDCheck = DTSLookups("AddrIDLookup").Execute(DTSSource("ID"))
objStream.WriteLine("ID CHECK is " &strIDCheck)
objStream.WriteBlankLines(1)

strDateCheck = DTSLookups("DateCheck").Execute(DTSSource
("ChangeDate"), strIDCheck)
objStream.WriteLine("DateCheck lookup1 returned " &strDateCheck)
objStream.WriteBlankLines(1)

If (strIDCheck <> "") and (strAddrLine1 <> "") Then

strDateCheck = DTSLookups("DateCheck").Execute(DTSSource
("ChangeDate"), strIDCheck)
objStream.WriteLine("DateCheck lookup returned " &strDateCheck)
objStream.WriteBlankLines(1)

If (strDateCheck = "Y") Then

DTSDestination("ADDR1") = DTSSource("AddrLine1")
DTSDestination("ADDR2") = DTSSource("AddrLine2")
DTSDestination("CITY") = DTSSource("AddrCity")
DTSDestination("STATE") = DTSSource("AddrState")
DTSDestination("ZIP") = DTSSource("AddrZip")
DTSDestination("COUNTRY") = DTSSource("AddrNatn")
DTSDestination("DAYPHONE") = DTSSource("PhoneNumber")
DTSDestination("ADDRESS_ID") = DTSLookups("AddrIDLookup").Execute
(DTSSource("ID"))
Main = DTSTransformstat_UpdateQuery
Else
Main = DTSTransformStat_SkipRow
objStream.WriteLine("Date in CLM newer than date in Banner")
objStream.WriteBlankLines(1)
End If
Else
objStream.WriteLine("Null Input")
objStream.WriteBlankLines(1)
Main = DTSTransformStat_SkipRow
End If

strIDCheck = ""
strAddrLine1 = ""
strID = ""
strDateCheck = ""

End If
End Function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top