Only getting few records inserted from millions

Please assist.

I am retrieving data from SAP BW using SSIS. The data is loaded into:

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "ZTBR_TA_BW_PrimaryKey" PRIMARY KEY
("Master_BRACS_Secondary_Key")
)

TABLESPACE pg_default;

Destination table is:

CREATE TABLE IF NOT EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ZTBR_Query_Destination_pkey" PRIMARY KEY
("Master_BRACS_Secondary_Key")
)

I get the data into the destination by joining a few tables:

ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
OWNER to
-------------------------------

INSERT INTO model."IMETA_ZTBR_BRACS_Model_TA_BW" (
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Cost_Center",
"Internal_Order",
"Trading_Partner",
"Amount_in_company_code_currency",
"Company_code_currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Master_BRACS_Secondary_Key",
"Source_Description_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Entity_Secondary_Key",
"Region_Secondary_Key",
"RowInsertedTimestamp",
"RowUpdatedTimestamp"
)
SELECT
ZTBR."Company_Code",
CAST(ZTBR."Posting_Period" AS character varying(7)) AS "Posting_Period",
ZTBR."Fiscal_Year",
ZTBR."Profit_Center",
ZTBR."Account_Number",
ZTBR."Business_Process",
ZTBR."Cost_Center",
ZTBR."Internal_Order",
ZTBR."Trading_Partner",
ZTBR."Amount_in_company_code_currency",
ZTBR."Company_code_currency",
ZTBR."BRACS_FA",
ZTBR."Expense_Type",
ZTBR."BRACS_ACCT_Key",
ZTBR."CC_Direct",
ZTBR."Segment_PC",
ZTBR."CC_Master_FA",
ZTBR."Master_BRACS_Secondary_Key",
ZTBR."Source_Description_Secondary_Key",
ZTBR."Direct_Indirect_Secondary_Key",
ZTBR."Entity_Secondary_Key",
ZTBR."Region_Secondary_Key",
CURRENT_DATE AS "RowInsertedTimestamp",
CURRENT_TIMESTAMP AS "RowUpdatedTimestamp"
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS ZTBR
JOIN
dim."IMETA_Direct_Indirect_Mapping" AS DIM_DIR_IND ON
ZTBR."Direct_Indirect_Secondary_Key" =
DIM_DIR_IND."Direct_Secondary_Key"
JOIN
dim."IMETA_Entity_Mapping" AS DIM_ENT_MAP ON
ZTBR."Entity_Secondary_Key" = DIM_ENT_MAP."Entity_Secondary_Key"
JOIN
dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" AS DIM_MASTER_BRACS ON
ZTBR."Master_BRACS_Secondary_Key" =
DIM_MASTER_BRACS."ZTBR_TransactionCode"
JOIN
dim."IMETA_Source_Description_Mapping" AS DIM_SRC_DESC ON
ZTBR."Source_Description_Secondary_Key" = DIM_SRC_DESC."BRACS_Key"
JOIN
dim."IMETA_Region_Mapping" AS DIM_REGION_MAP ON
ZTBR."Region_Secondary_Key" = DIM_REGION_MAP."Region_Primary_Key";
--------------------

Dimension tables:

Table: dim.IMETA_Source_Description_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_Source_Description_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_Source_Description_Mapping"
(
"BRACS_Key" integer NOT NULL DEFAULT
nextval('dim."IMETA_Source_Description_Mapping_BRACS_Key_seq"'::regclass),
"BRACSFA" character varying(255) COLLATE pg_catalog."default" NOT NULL,
"Function" character varying(255) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT "IMETA_Source_Description_Mapping_pkey" PRIMARY KEY ("BRACS_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Source_Description_Mapping"
OWNER to ;
-----------
-- Table: dim.IMETA_Region_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_Region_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
(
"CoCd" character varying(255) COLLATE pg_catalog."default",
"Sub Region" character varying(255) COLLATE pg_catalog."default",
"Region" character varying(255) COLLATE pg_catalog."default",
"BRACS Entity" character varying(255) COLLATE pg_catalog."default",
"Consul" character varying(255) COLLATE pg_catalog."default",
"Report" character varying(255) COLLATE pg_catalog."default",
"Region BRACS" character varying(255) COLLATE pg_catalog."default",
"Group" character varying(255) COLLATE pg_catalog."default",
"Group BRACS" character varying(255) COLLATE pg_catalog."default",
"J" character varying(255) COLLATE pg_catalog."default",
"K" character varying(255) COLLATE pg_catalog."default",
"L" character varying(255) COLLATE pg_catalog."default",
"M" character varying(255) COLLATE pg_catalog."default",
"N" character varying(255) COLLATE pg_catalog."default",
"Region_Primary_Key" integer NOT NULL DEFAULT
nextval('dim."IMETA_Region_Mapping_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY ("Region_Primary_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Region_Mapping"
OWNER to ;--------

-- Table: dim.IMETA_Master_BRACS_to_SAP_Data_TA_BR_

-- DROP TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_";

CREATE TABLE IF NOT EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
(
"ZTBR_TransactionCode" integer NOT NULL DEFAULT
nextval('dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR__ZTBR_TransactionCode_seq"'::regclass),
"Acct Type" character varying(255) COLLATE pg_catalog."default",
"Level 1" character varying(255) COLLATE pg_catalog."default",
"Level 2" character varying(255) COLLATE pg_catalog."default",
"Level 3" character varying(255) COLLATE pg_catalog."default",
"GCoA" double precision,
"Account Desc" character varying(255) COLLATE pg_catalog."default",
"EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
"BRACS" double precision,
"BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date,
CONSTRAINT "ZTBR_TransactionCode_key" UNIQUE ("ZTBR_TransactionCode")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
OWNER to ;
--------
-- Table: dim.IMETA_Entity_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_Entity_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_Entity_Mapping"
(
"Entity_ID" integer NOT NULL DEFAULT
nextval('dim."IMETA_Entity_Mapping_Entity_ID_seq"'::regclass),
"Entity_Secondary_Key" integer,
"Entity" character varying(255) COLLATE pg_catalog."default",
"Entity Name" character varying(255) COLLATE pg_catalog."default",
"Entity Level" integer,
"Level 1" character varying(255) COLLATE pg_catalog."default",
"Level 2" character varying(255) COLLATE pg_catalog."default",
"Level 3" character varying(255) COLLATE pg_catalog."default",
"Level 4" character varying(255) COLLATE pg_catalog."default",
"Level 5" character varying(255) COLLATE pg_catalog."default",
"Level 6" character varying(255) COLLATE pg_catalog."default",
CONSTRAINT "IMETA_Entity_Mapping_pkey" PRIMARY KEY ("Entity_ID"),
CONSTRAINT "IMETA_Entity_Mapping_Entity_Secondary_Key_key" UNIQUE
("Entity_Secondary_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Entity_Mapping"
OWNER to ;

-- Trigger: trigger_increment_secondary_key

-- DROP TRIGGER IF EXISTS trigger_increment_secondary_key ON
dim."IMETA_Entity_Mapping";

CREATE TRIGGER trigger_increment_secondary_key
BEFORE INSERT
ON dim."IMETA_Entity_Mapping"
FOR EACH ROW
EXECUTE FUNCTION public.increment_secondary_key();
----------
-- Table: dim.IMETA_Direct_Indirect_Mapping_New

-- DROP TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New";

CREATE TABLE IF NOT EXISTS dim."IMETA_Direct_Indirect_Mapping_New"
(
"BRACS Account Description" character varying(255) COLLATE
pg_catalog."default",
"CLASSIFICATION" character varying(255) COLLATE pg_catalog."default",
"Direct_Secondary_Key" integer NOT NULL,
"Direct_Primary_Key" integer NOT NULL,
CONSTRAINT "IMETA_Direct_Indirect_Mapping_New_pkey_new" PRIMARY
KEY ("Direct_Primary_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New"
OWNER to ;

The problem is that I am only getting 19 rows inserted into my destination
table. My source has over 5 million rows. What am I doing wrong?

Strangely enough, dim."IMETA_Source_Description_Mapping" only has 19
columns. All of these dimension table contains data to only look up to the
Fact table, meaning data in the dimension tables are Master Data or Data
that don't change. I tried looking up data in every table including the
dimensions and fact, and they all contain data. I also amended the
constraints on the table to see if that is the problem, it did not help.

The Secondary Keys in the Dimension table are surrogate keys. This is to
allow the dimension tables to join to fact table. I can not create these
Keys as Secondary in the Fact table, if I do I get an Secondary key
Violation when running SSIS.

The complete code is also attached.