DTS - Import file update/insert record

Posted on 2006-06-06
Last Modified: 2013-11-30
I am attemting to write a dts package that imports a file and if the record exists update the record and if it doesn't insert it.

I am using a data driven query..

Here is what i have:

Function Main()

Select case  DTSSOurce("col001")

case (DTSSource("col001") = DTSDestination("numID") )

      DTSDestination("numID") = DTSSource("Col001")
      DTSDestination("txtName").value = DTSSource("Col002").value
      Main = DTSTransformstat_UpdateQuery

case else
      DTSDestination("numID") = DTSSOURCE("col001")
      DTSDestination("txtName").value = DTSSOurce("Col002").value
      Main = DTSTransformstat_InsertQuery

end select

End Function

I am getting cannot insert duplicate records..

Do you see anything i am doing wrong?

Question by:DJMoonLight
    LVL 142

    Accepted Solution

    you cannot expect the DTSDestination collection to have the values of the potentially duplicate row, the values will actually all be empty/null.

    what I do to update/insert into a table, is
    1) I load the file as is into a staging table, no checks
    2) with "simple" update and insert statements (using joins), I update existing rows resp. insert new rows

    you cannot do that with the DTS data flow in 1 step
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >you cannot do that with the DTS data flow in 1 step
    well, even if one can/could, it's not recommendable, as it would check line by line. takes too much time

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now