Update records using DTS

  • Thanks in advance for taking the time to read my question.

    I'm relatively new to using DTS and VBScript. I've found other threads concerning my question but have not found an answer with enough detail to create a solution.

    I have successfully imported data from an Access Database using DTS. I am familiar with how to use Lookup's to populate fields using another table.  I am trying  now to implement a Data Driven Query Task using a lookup on MemberID (DTSSource("Field1")) to see if a record already exists for a member, and if so, just update the record rather than insert it.

    Here is my transformation VBScript Main Function

    '**********************************************************************

    '  Visual Basic Transformation Script

    '************************************************************************

    Function Main()

    If DTSLookups("lkp_memberID").Execute(DTSSource("Field1")) = 0 Then

     DTSDestination("MemberID") = DTSSource("Field1")

     DTSDestination("FirstName") = DTSSource("Field2")

     DTSDestination("MiddleName") = DTSSource("Field3")

     DTSDestination("LastName") = DTSSource("Field4")

     DTSDestination("DOB") = DTSSource("Field5")

     DTSDestination("Age") = DTSSource("Field6")

     DTSDestination("Gender") = DTSSource("Field7")

     DTSDestination("SSN") = DTSSource("Field8")

     DTSDestination("DependentStatus") = DTSSource("Field9")

     DTSDestination("Address1") = DTSSource("Field10")

     DTSDestination("Address2") = DTSSource("Field11")

     DTSDestination("City") = DTSSource("Field12")

     DTSDestination("State") = DTSSource("Field13")

     DTSDestination("ZipCode") = DTSSource("Field14")

     DTSDestination("Country") = DTSSource("Field15")

     DTSDestination("HomePhone") = DTSSource("Field16")

     DTSDestination("Email") = DTSSource("Field17")

     DTSDestination("PCPID") = DTSSource("Field18")

     DTSDestination("EligibilityBeginDate") = DTSSource("Field19")

     DTSDestination("EligibilityEndDate") = DTSSource("Field20")

     DTSDestination("EmployerGroupID") = DTSSource("Field21")

     DTSDestination("EmployerGroupName") = DTSSource("Field22")

     DTSDestination("SubscriberIdentifier") = DTSSource("Field23")

     DTSDestination("PolicyID") = DTSSource("Field24")

     DTSDestination("PlanID") = DTSSource("Field25")

     DTSDestination("LineOfBusinessID") = DTSSource("Field26")

     DTSDestination("MedicaidBeginDate") = DTSSource("Field27")

     DTSDestination("MedicaidEndDate") = DTSSource("Field28")

     DTSDestination("EligibilityCategory") = DTSSource("Field29")

     DTSDestination("OriginalMedicareReason") = DTSSource("Field30")

     DTSDestination("PharmacyBenefitFlag") = DTSSource("Field31")

     DTSDestination("PopulationIdentifier") = DTSSource("Field32")

     Main = DTSTransformstat_InsertQuery

    else

    DTSDestination("MemberID") = DTSSource("Field1")

     DTSDestination("FirstName") = DTSSource("Field2")

     DTSDestination("MiddleName") = DTSSource("Field3")

     DTSDestination("LastName") = DTSSource("Field4")

     DTSDestination("DOB") = DTSSource("Field5")

     DTSDestination("Age") = DTSSource("Field6")

     DTSDestination("Gender") = DTSSource("Field7")

     DTSDestination("SSN") = DTSSource("Field8")

     DTSDestination("DependentStatus") = DTSSource("Field9")

     DTSDestination("Address1") = DTSSource("Field10")

     DTSDestination("Address2") = DTSSource("Field11")

     DTSDestination("City") = DTSSource("Field12")

     DTSDestination("State") = DTSSource("Field13")

     DTSDestination("ZipCode") = DTSSource("Field14")

     DTSDestination("Country") = DTSSource("Field15")

     DTSDestination("HomePhone") = DTSSource("Field16")

     DTSDestination("Email") = DTSSource("Field17")

     DTSDestination("PCPID") = DTSSource("Field18")

     DTSDestination("EligibilityBeginDate") = DTSSource("Field19")

     DTSDestination("EligibilityEndDate") = DTSSource("Field20")

     DTSDestination("EmployerGroupID") = DTSSource("Field21")

     DTSDestination("EmployerGroupName") = DTSSource("Field22")

     DTSDestination("SubscriberIdentifier") = DTSSource("Field23")

     DTSDestination("PolicyID") = DTSSource("Field24")

     DTSDestination("PlanID") = DTSSource("Field25")

     DTSDestination("LineOfBusinessID") = DTSSource("Field26")

     DTSDestination("MedicaidBeginDate") = DTSSource("Field27")

     DTSDestination("MedicaidEndDate") = DTSSource("Field28")

     DTSDestination("EligibilityCategory") = DTSSource("Field29")

     DTSDestination("OriginalMedicareReason") = DTSSource("Field30")

     DTSDestination("PharmacyBenefitFlag") = DTSSource("Field31")

     DTSDestination("PopulationIdentifier") = DTSSource("Field32")

     Main = DTSTransformstat_UpdateQuery

    end if

    End Function

    ****************************************

    Here is my lookup function named lkp_memberID

    SELECT     COUNT(*) AS Expr1

    FROM         tblMedMemberData

    WHERE     (MemberID = ?) 

    I receive an error when running this task

    Error Source: OLE DB PRovider for SQL Server

    Error Description: This statement has been terminated. String or binary data would be truncated.

    Can you help?

    Thanks very much!

    Laurie

  • I've had this error only when I am inserting a data in a field that is smaller the the data size.

    Azz.

     

  • Import data to empty table, same like target. And then run SQL query to update record already exists or insert new record.

  • Yes, enlarging some db fields got rid of the error message. thank you

  • Interesting thought! thank you. I'll try that.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply