November 23, 2023 at 8:25 am
Please assist. I am connecting to SAP with XtractIS to Postgres. Because it is not a usual OLE DB connection, some security features in SSIS, preventing duplicates. are not available. I also have to bring in primary keys to my destination table as the data from SAP does not have primary keys. How can i prevent duplicates using XtractIS/SSIS?
My code that i use for upsert is:
WITH source_data AS (
SELECT
fact."ZTBR_TransactionCode",
fact."Company_Code",
fact."Posting_Period",
fact."Fiscal_Year",
fact."Profit_Center",
fact."Account_Number",
fact."Business_Process",
fact."Internal_Order",
fact."Amount_in_Company_Code_Currency",
fact."Company_Code_Currency",
fact."BRACS_FA",
fact."Expense_Type",
fact."BRACS_ACCT_Key",
fact."CC_Direct",
fact."Segment_PC",
fact."CC_Master_FA",
fact."Region_Secondary_Key",
fact."Direct_Indirect_Secondary_Key",
fact."Source_Description_Secondary_Key",
fact."Entity_Secondary_Key",
fact."Master_BRACS_Secondary_Key",
CURRENT_TIMESTAMP AS "Loaddate"
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
)
INSERT INTO fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" (
"ZTBR_TransactionCode",
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Internal_Order",
"Amount_in_Company_Code_Currency",
"Company_Code_Currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Region_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Source_Description_Secondary_Key",
"Entity_Secondary_Key",
"Master_BRACS_Secondary_Key",
"Loaddate"
)
SELECT * FROM source_data
ON CONFLICT ("ZTBR_TransactionCode")
DO UPDATE SET
"Company_Code" = EXCLUDED."Company_Code",
"Posting_Period" = EXCLUDED."Posting_Period",
"Fiscal_Year" = EXCLUDED."Fiscal_Year",
"Profit_Center" = EXCLUDED."Profit_Center",
"Account_Number" = EXCLUDED."Account_Number",
"Business_Process" = EXCLUDED."Business_Process",
"Internal_Order" = EXCLUDED."Internal_Order",
"Amount_in_Company_Code_Currency" = EXCLUDED."Amount_in_Company_Code_Currency",
"Company_Code_Currency" = EXCLUDED."Company_Code_Currency",
"BRACS_FA" = EXCLUDED."BRACS_FA",
"Expense_Type" = EXCLUDED."Expense_Type",
"BRACS_ACCT_Key" = EXCLUDED."BRACS_ACCT_Key",
"CC_Direct" = EXCLUDED."CC_Direct",
"Segment_PC" = EXCLUDED."Segment_PC",
"CC_Master_FA" = EXCLUDED."CC_Master_FA",
"Region_Secondary_Key" = EXCLUDED."Region_Secondary_Key",
"Direct_Indirect_Secondary_Key" = EXCLUDED."Direct_Indirect_Secondary_Key",
"Source_Description_Secondary_Key" = EXCLUDED."Source_Description_Secondary_Key",
"Entity_Secondary_Key" = EXCLUDED."Entity_Secondary_Key",
"Master_BRACS_Secondary_Key" = EXCLUDED."Master_BRACS_Secondary_Key",
"Loaddate" = EXCLUDED."Loaddate";
I do make use of a staging table, but still duplicates slips through.
The problem is XtractIS does not have a function that you can load data by date or primary key.
An example, with the 1 load(into Staging table) 6 records were loaded with primary keys 1-6. Second load 2 new records were load + the previous 6 into Staging table. Total of 8 records are now loaded into Staging table, because XtractIS see's the second load of records as a total new load. Meaning 6 previous records + 8 "new" records loaded, gives a total of 14 records in Staging table. It should be the 6 previous records(with primary id's 1- 6) + the 2 new records. Should have id's 1-8, but now it is 1-14 id's/records. This is the huge issue.
... and i still gets duplicates.
November 23, 2023 at 8:38 am
first of format your code - it is showing as a single line.
second - use a staging table and do the dedup from it within your code.
if you don't know how, and taking in consideration your prior history of posting Postgres code here, I strongly advise you hire a consultant that does know what to do.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply