Using lookups with where clause?

• dbwhz

  Old Hand

  Points: 328

  More actions

  September 9, 2005 at 12:16 am

  #62005

  I am trying to update some rows in a oracle database from my sql server database

  SQL Server Table: payment

  - logon_id

  - card_no

  - card_type

  - last_mod

  Oracle Table: userdetails

  - userid (same as logon_id)

  - card_number

  - card

  I only want to update the oracle table with last_mod equals today's date and where card_no is not null

  I created two connections in my DTS package. one to my local SQL database and one to my oracle db

  I made a data_pump between them and made a lookup called "lkp_payment"

  In the lookups i got the SQL query:

  SELECT card, cardnumber

  FROM userdetails

  WHERE (userid = ?)

  I created a transformation with a activex set logon_id, card_no, card_type as source and userid, card_number, card as destination.

  I got the code in my activex script:

  Function Main()

  dim arrPayment

  DTSDestination("userid") = DTSSource("logon_id")

  arrPayment = DTSLookups("lkp_payment").Execute(DTSSource("logon_id")

  DTSDestination("card") = arrPayment(0)

  DTSDestination("cardnumber") = arrPayment(1)

  End Function

  I get this running but it transfers all rows which is not what i want...

  Where am i supposed to put in the where clause so it only filters out the vaild values?

• philcart

  SSC-Forever

  Points: 47794

  More actions

  September 9, 2005 at 2:05 am

  #588637

  Maybe it's just because it's Friday evening here ... but I'm confused

  Doesn't your source data drive the datapump, so therefore the WHERE clause would go on your source data not the lookup ??

   

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