Archive Tables
Used |
ARCHOWN.PRIME17_CACCOUNTS |
ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_UN_SCORES |
ARCHOWN.FINCL10_LTDH |
ARCHOWN.FINCL10_LDTH |
ARCHOWN.SAMIR_OFFER |
ARCHOWN.FINCL10_RTH |
ARCHOWN.BPM_NCB_CARDS_ROUTE_TABLE |
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING |
ARCHOWN.C360_GOLDENCUSTOMERKYC |
ARCHOWN.FINCL10_ACCOUNTS |
ARCHOWN.FINCL10_LRS |
ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES |
ARCHOWN.PRIME17_APPLOG |
ARCHOWN.C360_CUSTOMERACTIVE |
ARCHOWN.LENDX_CL_CARDS_EXTTABLE |
ARCHOWN.FINCL10_GAC |
ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST |
ARCHOWN.FINCL10_AAS |
ARCHOWN.FINCL10_LAM |
ARCHOWN.FINCL10_CMG |
ARCHOWN.LENDX_LD_CREDITCARD_DECISION |
ARCHOWN.FINCL10_RCT |
ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST |
ARCHOWN.C360_CustomerAML |
ARCHOWN.FINCL10_ATO |
ARCHOWN.C360_GOLDENCUSTOMERACCOUNT |
ARCHOWN.FINCL10_GAM |
ARCHOWN.RIB11_CUSR |
ARCHOWN.FINCL10_LDT |
ARCHOWN.CIB11_CUSR |
ARCHOWN.PRIME17_PEOPLE |
ARCHOWN.PRIME17_CARDX |
ARCHOWN.BPM_NCB_AP_CARDS_DETAILS_DECISION |
ARCHOWN.C360_GOLDENCUSTOMER |
ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE |
--SQL
Note:
--Need to replace Tables, find SSIS packages which load data (There
might be multiple packages). [steps from Only Archive db not for Landing Db]
-- Target completion date is Sunday EOD.
SELECT
e.execution_id,
ex.package_name,
es.executable_id,
ex.executable_name,
es.execution_path,
es.execution_result,
es.start_time,
es.end_time,
DATEDIFF(SECOND, es.start_time,
es.end_time) AS duration_seconds,
CASE
WHEN executable_name LIKE '%IS_LOAN_DELINQUENT%' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC, Archown.Fincl10_AAS'
WHEN executable_name LIKE '%IS_CC_DELINQUENT%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE '%IS_ACTIVE_OFFER%' THEN 'Archown.SAMIR_OFFER'
WHEN executable_name LIKE '%RIB_ENABLED%' THEN 'Archown.RIB11_CUSR,
Archown.Fincl10_Accounts'
WHEN executable_name LIKE '%ACTIVE%' AND executable_name LIKE '%OFFER%' THEN
'Archown.SAMIR_OFFER'
WHEN executable_name like '%Historical cc%' THEN 'Archown.PRIME17_APPLOG,
Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name like '%CIF%' then 'ARCHOWN.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE '%CIB_ENABLED%' THEN 'Archown.CIB11_CUSR,
Archown.Fincl10_Accounts'
WHEN executable_name LIKE '%IS_DO%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE '%SAVINGS%' THEN 'ARCHOWN.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE '%DUPLI%' THEN 'Archown.SAMIR_OFFER'
WHEN executable_name LIKE '%INFLOW%' THEN 'Archown.Fincl10_GAM, Archown.SAMIR_OFFER,
Archown.Fincl10_ATO'
WHEN executable_name LIKE '%ACTIVE%' AND executable_name LIKE '%LOAN%' THEN
'Archown.Fincl10_GAM, Archown'
WHEN executable_name like '%Historical loan%' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'
WHEN executable_name like '%woff%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name like '%BLBD%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name like '%trading%' THEN 'Archown.Fincl10_GAM, Archown.SAMIR_OFFER,
Archown.Fincl10_CMG'
WHEN executable_name LIKE '%EXISTING%' AND executable_name LIKE '%LOAN%' THEN
'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE '%WATCH%' THEN 'Archown.Fincl10_ACCOUNTS,
Archown.Fincl10_RCT'
WHEN executable_name LIKE '%AML%' then 'Archown.C360_CustomerAML'
WHEN executable_name LIKE '%Defaulted%' THEN 'Archown.Fincl10_GAM, Archown.Fincl10_GAC,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE 'INSERTING UPDATING HAS DIGITAL LOAN'
THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE 'INSERTING PAYPLUS LOAN' THEN 'Archown.Fincl10_GAM,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE 'INSERTNG MICRO LOAN FLAG' THEN 'Archown.Fincl10_GAM,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE 'INSERT INTO WRITE OFF' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC'
WHEN executable_name LIKE '%IS_D015%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE '%ACTIVE PAYADVANCE%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name LIKE 'INSERTING STG EIM OBLIGATIONS' THEN 'Archown.Fincl10_LRS'
WHEN executable_name LIKE 'INSERTING ST MONTHLY INT AMT' THEN 'Archown.FINCL10_LDT'
WHEN executable_name LIKE 'INSERTING ST PRDEM' THEN 'Archown.FINCL10_LDT'
WHEN executable_name LIKE '%INSERTING SOLE TRADER FLAG%' then
'Archown.C360_GOLDENCUSTOMER'
WHEN executable_name like '%HAS_HISTORICAL_LOAN%' then 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'
WHEN executable_name like '%HIGH%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_ATO'
WHEN executable_name like '%HISTORICAL CREDIT CARD%' THEN 'Archown.PRIME17_APPLOG,
Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name like '%DECLINE%' THEN 'ARCHOWN.BPM_NCB_AP_CARDS_DETAILS_DECISION,
ARCHOWN.BPM_NCB_CARDS_ROUTE_TABLE, ARCHOWN.LENDX_CL_CARDS_EXTTABLE,
ARCHOWN.LENDX_LD_CREDITCARD_DECISION'
WHEN executable_name like '%CREDIT CARD DEL%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name like '%LOAN DELINQUENT%' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC, Archown.Fincl10_AAS'
WHEN executable_name like '%CLASSIFIED%' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC'
WHEN executable_name like '%existing cc%' THEN 'Archown.PRIME17_CACCOUNTS,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name = 'INSERT INTO ACCT_CLS_FLG' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_CMG'
WHEN executable_name LIKE '%BOB ENABLED%' THEN 'Archown.CIB11_CUSR,
Archown.Fincl10_Accounts'
WHEN executable_name = 'INSERTING UNSECURED RISK SCORE VALUE' THEN 'Archown_Custom_Grmd.GRMD_FINAL_PRE_UN_SCORES'
WHEN executable_name = 'INSERTING CREDIT CARD RISK SCORE VALUE' THEN
'Archown_Custom_Grmd.GRMD_FINAL_PRE_CC_SCORES'
WHEN executable_name = 'INSERT INTO RWR TMP RTO,GAM' THEN 'ARCHOWN.Fincl10_GAM'
WHEN executable_name like '%UNEMPLOYED%' THEN 'Archown.C360_GOLDENCUSTOMER,
ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST,
ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST, ARCHOWN.C360_GOLDENCUSTOMERACCOUNT,
ARCHOWN.C360_GOLDENCUSTOMER'
WHEN executable_name like '%INSERTING IS_MOBILE_APP%' THEN 'Archown.C360_GOLDENCUSTOMER,
Archown.C360_CUSTOMERACTIVE, Archown.C360_GOLDENCUSTOMERKYC,
Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name = 'INSERTING_ IS_MISSED PAYMENT OCMP INSTALOAN' THEN
'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT,
Archown.Fincl10_LDTH'
WHEN executable_name = 'INSERT INTO HAS STAFF ACCOUNT' THEN 'Archown.Fincl10_GAM,
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name like '%LOAN DELIQUENT%' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_GAC, Archown.Fincl10_AAS'
WHEN executable_name LIKE '%OWNER TYPE%' THEN 'ARCHOWN.Fincl10_GAM,
Archown.Fincl10_AAS'
WHEN executable_name = 'INSERTING STG CUSTOMR BALANCES 1' THEN 'ARCHOWN.C360_GOLDENCUSTOMERACCOUNT'
WHEN executable_name = 'INSERTING CUSTOMER DISP AMOUNT,OUTSTANDING BALANCE AND
COUNTS' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING,
ARCHOWN.FINCL10_LAM, Archown.Fincl10_RTH'
WHEN executable_name = 'INSERTING UNSECURED LOAN FLAG' THEN 'ARCHOWN.Fincl10_GAM,
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name = 'INSERTING EXISTING FASTCASH' THEN 'ARCHOWN.Fincl10_GAM,
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name = 'INSERTING UPDATING HAS DIGITAL LOAN 1' THEN
'ARCHOWN.FINCL10_GAM'
WHEN executable_name = 'INSERTING UPDATING HAS DIGITAL LOAN' THEN
'ARCHOWN.FINCL10_GAM'
WHEN executable_name = 'INSERTING ACTIVE CCBIZPREAPPROVED OCMP FLAG' THEN
'ARCHOWN.SAMIR_OFFER'
WHEN executable_name = 'INSERTTING BLOCK LISTED ACCOUNTS' THEN
'ARCHOWN.FINCL10_GAM, ARCHOWN.FINCL10_CMG'
WHEN executable_name = 'INSERTING CC PREQUAL CREDIT CARD EXISTING FLAG OCMP'
THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'
WHEN executable_name = 'INSERTING INTO STG OBLIGATIONS' THEN
'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING,
ARCHOWN.FINCL10_LAM, Archown.Fincl10_GAC'
WHEN executable_name = 'INSERT INTO RWR TEMP OFFERS' THEN 'Archown.SAMIR_OFFER'
WHEN executable_name like '%internet%' then 'Archown.Fincl10_Accounts,
Archown.RIB11_CUSR'
when executable_name = 'INSERTING PAYADVANCE OFFER FLAG' then
'ARCHOWN.SAMIR_OFFER'
WHEN executable_name = 'INSERTING CREDIT CARD EXISTING FLAG' THEN 'ARCHOWN.C360_GOLDENCUSTOMER,
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.PRIME17_PEOPLE,
ARCHOWN.PRIME17_CARDX'
WHEN executable_name = 'INSERTINGIS_INSTALOAN_FLAG' THEN 'Archown.C360_CUSTOMERAML'
WHEN executable_name = 'INSERTING_CREDIT_LIMIT_CC' then 'ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES'
WHEN executable_name = 'INSERT INTO EMPLOYMENT_TENURE' THEN 'ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE,
[ARCHOWN].[C360_GOLDENCUSTOMER], ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST,
[ARCHOWN].C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST'
when executable_name = 'INSERT INTO IS_PRE_APPROVAL_PRE_QUALIFICATION' then
'ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES'
when executable_name = 'INSERTING CREDIT_LIMIT_INSTALOAN' THEN '[ARCHOWN_CUSTOM_GRMD].[GRMD_FINAL_PRE_UN_SCORES]'
WHEN executable_name = 'INSERT INTO CLD CLFD' THEN 'ARCHOWN.Fincl10_GAM,
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.Fincl10_GAC,
ARCHOWN.Fincl10_LTDH'
WHEN executable_name = 'INSERT INTO REPAY COMP' THEN 'ARCHOWN.Fincl10_GAM,
ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.Fincl10_GAC,
ARCHOWN.Fincl10_LTDH'
WHEN executable_name = 'INSERT EMPLOYMENT_CHECK_FASTCASH' THEN 'ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE,
[ARCHOWN].[C360_GOLDENCUSTOMERSYSTEMMAPPING],
ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, [ARCHOWN].C360_GOLDENCUSTOMERACCOUNT,
ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST'
ELSE 'TBD' END Tables_Involved
FROM
catalog.executions AS e WITH (NOLOCK)
JOIN
catalog.executable_statistics AS es WITH
(NOLOCK) ON e.execution_id = es.execution_id
JOIN
catalog.executables AS ex WITH (NOLOCK)
ON es.execution_id = ex.execution_id AND es.executable_id = ex.executable_id
WHERE
e.package_name =
'ETL_UNIFIED_CUSTOMER_BASE.dtsx' and
e.status = 7 and
executable_name LIKE 'INSERT%'
ORDER BY
es.start_time desc
SELECT
f.folder_name,
p.project_name,
pa.package_name,
cm.object_type,
cm.parameter_name AS connection_manager_name,
cm.sensitive,
cm.value_type,
cm.value_set,
cm.[value] AS connection_value
FROM
[SSISDB].[catalog].[projects] p
JOIN
[SSISDB].[catalog].[folders] f ON p.folder_id = f.folder_id
JOIN
[SSISDB].[catalog].[packages] pa ON p.project_id = pa.project_id
JOIN
[SSISDB].[catalog].[object_parameters] cm ON cm.project_id = p.project_id AND cm.object_id = pa.package_id
WHERE
cm.parameter_name LIKE 'CM.%' -- CM. prefix is used for connection managers
AND cm.design_default_value IS NOT NULL
ORDER BY
f.folder_name, p.project_name, pa.package_name, cm.parameter_name;