August 4, 2004 at 3:39 pm
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
August 5, 2004 at 2:30 am
I've had this error only when I am inserting a data in a field that is smaller the the data size.
Azz.
August 5, 2004 at 10:39 am
Import data to empty table, same like target. And then run SQL query to update record already exists or insert new record.
August 5, 2004 at 10:48 am
Yes, enlarging some db fields got rid of the error message. thank you
August 5, 2004 at 10:48 am
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