Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Unanswered: DTS importing only new records


    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.

  2. #2
    Join Date
    Jun 2003

    Thumbs up Re

    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
    I love cursor,though I never use it.Cos' I dont want to misuse it.


  3. #3
    Join Date
    Dec 2002
    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
      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 INTO MyTable VALUES (?,?,?,?,?)

    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.


    Last edited by hmscott; 11-16-05 at 10:17.
    Have you hugged your backup today?

  4. #4
    Join Date
    Oct 2003

    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
    Main = DTSTransformstat_InsertQuery
    End If
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts