DTS importing only new records

Hi!
How can I set up a DTS that reads a flat file database and inserts only the new records to an existing table? This DTS will be ran everyday against a flat file that accumulates records.Import data into temp table and compare with ur original table, delete duplicate records from temp table and insert records from temp table to ur original table|||Alternatively, look at the Data Driven Query Task.

1. On the Source tab, write your Query to Pull data from your source (in this case a flat file).
2. On the bindings tab, specify the destination table.
3. On the Transformation tab, a default transform will be created for you in VB Script. Keep it, but it is most likely that you will have to make some changes.
4. On the Queries tab, you will need to specify a query for inserting data and a query for updating data. If there are many columns, this can be a real pain.
5. On your lookups tab, you will need to specify a Query that will return an integer value (Such as SELECT Count(*) FROM MyTable WHERE PK = ?). Leave the ? exactly as it is written above; that is a parameter that you will pass in from the transform statement.
6. Now go back to the Transform tab; add an If...Then...Else statement around the line where it says Main = DTSTransformstat_UpdateQuery

If DTSLookups("MyLookupName").Execute(DTSSource("MyParameter")) > 0 Then
Main = DTSTransformstat_UpdateQuery
Else
Main = DTSTransformstat_InsertQuery
End If

This block basically says If there exists a record in the destination table with the same Primary Key, then run the Update Query, otherwise run the Insert Query.

7. Now go back to the Queries tab and add your INSERT and UPDATE queries. They should look something like:

INSERT:
INSERT INTO MyTable VALUES (?,?,?,?,?)

UPDATE:
UPDATE MyTable SET Value1 = ?, Value2 = ?, Value3 = ?, Value4 = ?
WHERE PK = ?

Finally, on the queries tab, make sure you align the incoming parameters with the place holders (question marks) using the mapping tool at the bottom of the window. It is, no doubt about it, a pain in the a$$.

Another alternative might be to write a single stored procedure that would accept the incoming values, test for the existence of the primary key and perform an INSERT or UPDATE as required.

Regards,

hmscott|||Hi!

Thanks for the detailed suggestion. I'm new to this stuff and I really want to know more about utilizing DTS to maintain and manipulate my tables.

Anyhow, I was able to setup 2 connections (Flat file connection that points to the flat ascii file and an SQL server connection to my destination table) and a Data Driven Query Task. There's no workflow between them, is this correct? When I execute he package, it failed and gave me an error saying: "statement could not be prepared, Line 2: incorrect syntax near ".".

Here's my transformation code:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("MID") = DTSSource("Col001")
DTSDestination("IIN") = DTSSource("Col003")
DTSDestination("ITN2") = DTSSource("Col004")
DTSDestination("ITN") = DTSSource("Col005")
DTSDestination("ITN3") = DTSSource("Col006")
DTSDestination("GENDER") = DTSSource("Col007")
DTSDestination("LANG") = DTSSource("Col008")
DTSDestination("TITLE") = DTSSource("Col009")
DTSDestination("NAME") = DTSSource("Col010")
DTSDestination("ADDRESS") = DTSSource("Col011")
DTSDestination("APT") = DTSSource("Col012")
DTSDestination("CITY") = DTSSource("Col013")
DTSDestination("STREAM") = DTSSource("Col014")
DTSDestination("PROV") = DTSSource("Col015")
DTSDestination("POSTCODE") = DTSSource("Col016")
If DTSLookups("Count_ITN2").Execute (DTSSource("Col004")) > 0 Then
Else
Main = DTSTransformstat_InsertQuery
End If
End Function