CODEWITHSUNDEEP
ssis
BKChedlia
BKChedlia

Reputation: 337

SSIS Improve upsert method

I have database table of 100,000 rows, imported from CSV each week using an SSIS package. Usually updates, but sometimes it can add rows.

I see a few exceptions with staging table during the update rows - I don't know why? and how to update from staging to destination table?

This is the merge code :

MERGE INTO [PWCGFA_BG].[dbo].[Bank_Guarantees]  WITH (HOLDLOCK) AS bg 
USING [PWCGFA_BG].[dbo].[stagingBG] AS stgbg
    ON bg.IATA_CODE = stgbg.IATA_CODE
WHEN MATCHED THEN 
    UPDATE  set  
        bg.LEGAL_NAME=stgbg.LEGAL_NAME,
    bg.TRADING_NAME=stgbg.TRADING_NAME,
    bg.COUNTRY=stgbg.COUNTRY,
    bg.CURRENCY=stgbg.CURRENCY,
    bg.LANGUAGE=stgbg.LANGUAGE,
    bg.STATUS=stgbg.STATUS,
    bg.BANK_NAME=stgbg.BANK_NAME,
    bg.BANK_GUARANTEE_AMOUNT=stgbg.BANK_GUARANTEE_AMOUNT,
    bg.BANK_GUARANTEE_CURRENCY=stgbg.BANK_GUARANTEE_CURRENCY,
    bg.BANK_GUARANTEE_EXPIRY_DATE=stgbg.BANK_GUARANTEE_EXPIRY_DATE,
    bg.ACCREDITATION_DATE=stgbg.ACCREDITATION_DATE,
    bg.CLASS_PAX_OR_CGO=stgbg.CLASS_PAX_OR_CGO,
    bg.LOCATION_TYPE=stgbg.LOCATION_TYPE,
    bg.XREF=stgbg.XREF,
    bg.IRRS=stgbg.IRRS,
    bg.TAX_CODE=stgbg.TAX_CODE,
    bg.COUNTRY_CODE=stgbg.COUNTRY_CODE,
    bg.CITY=stgbg.CITY,
    bg.DEF=stgbg.DEF,
    bg.OWN_SHARE_CHANGE=stgbg.OWN_SHARE_CHANGE
WHEN NOT MATCHED BY bg THEN
    INSERT (IATA_CODE,LEGAL_NAME,TRADING_NAME,COUNTRY,CURRENCY,LANGUAGE,STATUS,BANK_NAME,BANK_GUARANTEE_AMOUNT,BANK_GUARANTEE_CURRENCY,BANK_GUARANTEE_EXPIRY_DATE,ACCREDITATION_DATE,CLASS_PAX_OR_CGO,LOCATION_TYPE,XREF,IRRS,TAX_CODE,CITY,DEF,OWN_SHARE_CHANGE)
    VALUES (stgbg.IATA_CODE,stgbg.LEGAL_NAME,stgbg.TRADING_NAME,stgbg.COUNTRY,stgbg.CURRENCY,stgbg.LANGUAGE,stgbg.STATUS,stgbg.BANK_NAME,stgbg.BANK_GUARANTEE_AMOUNT,stgbg.BANK_GUARANTEE_CURRENCY,stgbg.BANK_GUARANTEE_EXPIRY_DATE,stgbg.ACCREDITATION_DATE,stgbg.CLASS_PAX_OR_CGO,stgbg.LOCATION_TYPE,stgbg.XREF,stgbg.IRRS,stgbg.TAX_CODE,stgbg.CITY,stgbg.DEF,stgbg.OWN_SHARE_CHANGE)
WHEN NOT MATCHED BY stgbg THEN
    DELETE

enter image description here

Upvotes: 2

Views: 1509

Answers (1)

MSBI-Geek
MSBI-Geek

Reputation: 648

If your source(staging) and destination tables on the same Server you can use MERGE statement with Execute SQL task, which is faster and very effective than a lookup which uses a row by row operation.

But if the destination is on a different Server, you have the following options

  1. Use lookup to update the matching rows with an OLEDB Command(UPDATE Statement)
  2. Use a Merge Join (with LEFT OUTER JOIN) to identify the new/matching records and then use a conditional split to INSERT or UPDATE records. This works same as the Lookup but faster.
  3. Create a temporary table in the destination db, dump data from staging to that table and then use the MERGE statement, this is faster than using a lookup.

Upvotes: 1

Related Questions