Post reply

DTS - How to check in destination table & filter

  • February 28, 2003 at 2:49 am

    #106151

    Hi,

    Iam copying data from Access to SQL using DTS - script saved as VB file. But I have to copy only records that are not existing in destination table.

    So I tried ammending the generated code...

    oCustomeTask.SourseSQLStatement = "Select * from Sales where SalesNo not in (Select SalesNo from [DestinationServer].[dbo].[Sales])"

    Above statement results in systax error. I desperately need a solution... Please help

    Thanks in advance.

    Kodali

  • March 3, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • March 3, 2003 at 3:29 pm

    #450170

    Where/How are you running the VB code?

    Thanks

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • March 4, 2003 at 8:46 am

    #450232

    I may be misunderstanding you so please bear with me. Seems like you are using a SELECT statement to read information from Access. In that case, you cannot use the NOT IN statement unless your destination also resides in that database. You may want to put all the data into a staging table on the SQL machine, then use this query to move the new data from that staging table into the permanent one. Hope this makes sense.

    Guarddata-

  • March 8, 2003 at 7:26 am

    #450619

    Kodali,

    You could take a different approach and use the Transform Data Task.

    For the transformation inside the task, use an ActiveX Script. And in there use the Lookups in the Browser Tab to take each of the Access rows and look it up in the destination to see if it is there or not. And based on the result, add it or not to the destination.

    This may at least get your data moving while you figure out your approach.

    Jennifer

  • March 8, 2003 at 7:36 am

    #450620

    Hi Kodali,

    Yet another approach for you. You could make your access DB a linked server (look under security folder in your Ent Mgr), and then you can write your query as before (i.e.

    Select * from <linkedservername>...tblBob where tblBob.Key Not IN (Select sqlservertblBob.Key from sqlseerverTblBob)

    ).

    If you don't want to have this linked permanently, you could always use the passthru type query, which is basically linking the Access DB for the life of the query only.

    Hope you get your data thru.

    Steve

    Steve.

    Steve.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply