×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data Driven Query, UPDATE only processing one source row

Data Driven Query, UPDATE only processing one source row

Data Driven Query, UPDATE only processing one source row

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close