SSIS Improve upsert method

Question

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

What is "a few exp" ? Exceptions? You need to tell us what the exceptions are together with some source data. — BIDeveloper, Jul 08 '16 at 08:09
Few examples, I mean that for the update, they use staging table but I don't know how they insert from staging to destination DB, in my case I made a stored procedure in ssms for the update, and execute it in OLE DB command — BKChedlia, Jul 08 '16 at 08:12
Maybe you should transform your sp to do a `MERGE` from the staging table to `Bank_Guarantees` instead of only an `UPDATE`. In that case you could get rid of the `Lookup` and the `Bank_Guarantees Destination` — vercelli, Jul 08 '16 at 08:20
But if lookup for Upsert is not a best pratice, why a lot of articles use it ? — BKChedlia, Jul 08 '16 at 08:25
@BKChedlia where does it say is not a best practice? Can't see why — vercelli, Jul 08 '16 at 08:45
I don't know why so many articles use it. SQL commands (i.e. updates) and sometimes lookups in a data flow occur on a row by row basis which _really_ slows down your package. They're fine for a small amount of rows but unusable for a large amount of rows. As far as best practice goes.... if your team is comfortable with a UI and uncomfortable with SQL, and you're not loading millions of rows it might be best practice for your team to do it that way. An 'UPSERT' can be achieved in many ways one way is the way you've done another way is purely through SQL statements. — Nick.Mc, Jul 08 '16 at 09:41
Change your statement from WHEN NOT MATCHED BY bg THEN to WHEN NOT MATCHED BY TARGET THEN and WHEN NOT MATCHED BY stgbg THEN to WHEN NOT MATCHED BY SOURCE THEN, You need to use the keywords TARGET and SOURCE not the aliases, hope this helps. — MSBI-Geek, Jul 08 '16 at 10:10

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

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

Use lookup to update the matching rows with an OLEDB Command(UPDATE Statement)

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.

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.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 08 '16 at 09:12

MSBI-Geek

628
10
25

I tried this Merge (above, I edit my post) but I have this error message : [Execute SQL Task] Error: Executing the query "MERGE INTO [PWCGFA_BG].[dbo].[Bank_Guar..." failed with the following error: "Incorrect syntax near 'bg'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. – BKChedlia Jul 08 '16 at 09:55
1

Change your statement from WHEN NOT MATCHED BY bg THEN to WHEN NOT MATCHED BY TARGET THEN and WHEN NOT MATCHED BY stgbg THEN to WHEN NOT MATCHED BY SOURCE THEN, You need to use the keywords TARGET and SOURCE not the aliases, hope this helps – MSBI-Geek Jul 08 '16 at 10:13
thanks, I did it, when I delete row then add new row and edit row , I have another problem : The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. – BKChedlia Jul 08 '16 at 10:31
1

That is because you might have IATA_CODE multiple times in the target table, probably you need to add additional conditions to match only one target row. – MSBI-Geek Jul 08 '16 at 11:20
You are right, I discovered wrong data no unik id in csv file... thankx MSBI-Geek – BKChedlia Jul 08 '16 at 12:08

SSIS Improve upsert method

1 Answers1