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
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