SQL Server Performance

Problem with update-Insert query

Discussion in 'SQL Server DTS-Related Questions' started by Abhi79, Feb 22, 2006.

  1. Abhi79 New Member

    Hi,

    Currently I am trying to perform some DTS Transformation from Oracle DB(ODBC Driver) to Sql DB (OLE DB) as the data transfer is very huge like (9,00000 records) , I am simply using DataPump task to perform this transformation.
    I am using Update and Insert query while datapump in Lookup method while running pcakage I am getting following error.
    NO Value Given for One or More Required Paramter

    I am using following Active X script.

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

    Function Main()
    ' Copy each source column to the destination column
    dim Qupdate,Qinsert

    DTSDestination("CUST_ID") = DTSSource("PARTY_CODE")

    if(( DTSSource("Party_Code").value) =(DTSDestination("CUST_ID").value)) Then
    Qupdate=DTSLookups("QueryUpdate").Execute (DTSTransformStat_QueryUpdate)

    DTSDestination("C_PHONE_NO") = DTSSource("PHONE_NUM")
    DTSDestination("C_ZIP") = DTSSource("PIN_CODE")
    DTSDestination("C_CNTRY") = DTSSource("CNTRY_CODE")
    DTSDestination("C_CITY") = DTSSource("CITY_CODE")
    DTSDestination("C_ADDR2") = DTSSource("ADDR_2")
    DTSDestination("C_ADDR1") = DTSSource("ADDR_1")
    DTSDestination("LOC_DESC") = DTSSource("PARTY_NAME")
    DTSDestination("BRANCH_ID") = DTSSource("BRANCH_ID")
    DTSDestination("BANK_ID") = DTSSource("BANK_CODE")
    else


    Qinsert=DTSLookups("QueryInsert").Execute (DTSTransformStat_QueryInsert)

    DTSDestination("C_PHONE_NO") = DTSSource("PHONE_NUM")
    DTSDestination("C_ZIP") = DTSSource("PIN_CODE")
    DTSDestination("C_CNTRY") = DTSSource("CNTRY_CODE")
    DTSDestination("C_CITY") = DTSSource("CITY_CODE")
    DTSDestination("C_ADDR2") = DTSSource("ADDR_2")
    DTSDestination("C_ADDR1") = DTSSource("ADDR_1")
    DTSDestination("LOC_DESC") = DTSSource("PARTY_NAME")
    DTSDestination("BRANCH_ID") = DTSSource("BRANCH_ID")
    DTSDestination("BANK_ID") = DTSSource("BANK_CODE")
    End IF
    Main = DTSTransformStat_OK
    End Function
    ********************************************************************
    My update query in Lookups is :-(QueryUpdate)
    UPDATE Abc
    SET BANK_ID = '?', BRANCH_ID = '?', LOC_DESC = ' ?', C_ADDR1 = ' ?', C_ADDR2 = ' ?', C_CITY = '?', C_CNTRY = '?', C_ZIP = '?', C_PHONE_NO = '?'
    WHERE (CUST_ID = ' ?')

    Insert is also same except give set I am suppling corresponding values in through wildcard.

    Please help me regarding this.



Share This Page