[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


DTS - Import file update/insert record

Posted on 2006-06-06
Medium Priority
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
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 16841393
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16841399
>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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

868 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