Generated from the files present in C:\Users\KM Lai\Documents\P2Z on 2026-09-08.
P2Z is an SSIS migration/replication project that moves selected tables from on-premises SQL Server databases into a multi-brand cloud SQL Server database. The current design is:
BackOfficeDB / PaymentDB (on-prem)
|
| SSIS data flow; BrandCode is carried with each row
v
P2ZLandDB (cloud landing/control database)
| |
| PK snapshot + lookup | update rows staged in *_Upd
v v
*_PK tables p_<Table>_Upd stored procedure
|
v
PaymentCloudDB (final cloud database)
The operational rules supplied by the project owner are:
PaymentCloudDB.P2ZLandDB.dbo.<Table>_Upd table; the matching dbo.p_<Table>_Upd procedure then updates PaymentCloudDB.P2ZLandDB.dbo.<Table>_PK. The lookup uses the PK snapshot and BrandCode to route the row.F1, P1, and J1; BrandCode is part of the multi-tenant row identity/filtering.| Type | Count | Purpose |
|---|---|---|
.dtsx |
172 | SSIS packages, stored as individual flat package files |
.sql |
13 | Source extracts/schema references, landing PK/update tables and procedures, cloud schema, SQL Agent logging controls |
.xlsx |
1 | P2Z_Frequency.xlsx, presumed scheduling/frequency reference |
.zip |
2 | BackOfficeDB_T1.zip and PaymentDB_T1.zip, archived/source reference material |
There is no SSIS project/solution file visible in this folder; the packages appear to be individual .dtsx artifacts. All inspected packages declare SSIS package format version 8 and LastModifiedProductVersion="12.0.5000.0" (SQL Server/SSIS 2014-era package metadata).
The base package name is the F1/default implementation. Brand-specific variants are suffixed _P1 and _J1; there are no _F1 files in the current folder. The inventory contains 21 _P1.dtsx packages and 23 _J1.dtsx packages, with the remaining packages using the unsuffixed/base name. This means the base package must not automatically be treated as an unrelated package: it is the F1 variant according to the handover rules.
Examples of variant families include:
BankAccount.dtsx, BankAccount_P1.dtsx, BankAccount_J1.dtsxDeposit.dtsx, Deposit_P1.dtsx, Deposit_J1.dtsxDepositV2.dtsx, DepositV2_P1.dtsx, DepositV2_J1.dtsxPaymentGateway.dtsx, PaymentGateway_P1.dtsx, PaymentGateway_J1.dtsxWithdrawal.dtsx, Withdrawal_P1.dtsx, Withdrawal_J1.dtsxWithdrawalV2.dtsx, WithdrawalV2_P1.dtsx, WithdrawalV2_J1.dtsxVariant coverage is not uniform. Some tables have only the base package, some have a P1 or J1 variant, and some have both. This is consistent with source-column drift between brands. When modifying or adding a package, compare the base/F1 package with the available P1/J1 variants and confirm the source schema before cloning.
The packages inspected use three principal OLE DB connection managers:
SrcDB, parameterized by project variable ConnSrcDB (the source is normally PaymentDB; packages related to back-office entities use BackOfficeDB).DstLandDB, parameterized by ConnDstDB (the P2ZLandDB landing database).BatchDB, parameterized by ConnBatchDB (the observed connection is P2ZBatchDB, used for batch/control data).Credentials are parameterized through ConnStrUsrName and ConnStrUsrPwd. BrandCode is a project parameter used in source queries, PK/update staging, lookups, and destination filtering. Packages also contain operational variables such as BatchID, insert/update counters, PK/date-column metadata, and start/end time values.
The package XML contains encrypted sensitive password values. Do not copy those values into documentation, source control, chat, or new scripts. Connection strings and credentials should be supplied through the deployment environment/project parameters.
Typical control-flow/data-flow elements found in the package XML include Execute SQL tasks, Data Flow tasks, OLE DB sources/destinations, lookup components, and SQL commands for clearing/loading PK and update staging tables. The exact sequence varies by package, so the package being changed must be inspected rather than assuming every package has identical tasks.
BackOfficeDB_F1.sql — approximately 1,313 CREATE TABLE statements and 1,038 PK declarations; the base/F1 back-office schema reference.BackOfficeDB_P1.sql — approximately 905 tables and 727 PK declarations.BackOfficeDB_J1.sql — approximately 1,105 tables and 892 PK declarations.BackOfficeDB_J1 (1).sql — a second J1 extract with the same approximate table/PK counts; preserve it as a separate dated/reference copy until its provenance is confirmed.PaymentDB_J1.sql — approximately 375 tables and 319 PK declarations; the J1 payment source reference. No F1/P1 payment extract with the same naming convention is present.BackOfficeDB_T1.zip and PaymentDB_T1.zip — archived T1 source material; inspect only when tracing historical/source differences.These SQL files are schema/reference scripts, not SSIS packages. They are useful for checking table/column/PK drift before editing a .dtsx file.
P2ZLandDB)P2ZLandDB_PK.sql creates the <Table>_PK staging tables. The observed script covers approximately 127 PK tables. These tables include a generated LandID, BrandCode, and table-specific source PK columns; many also have a brand index/unique index.P2ZLandDB_PKDrop.sql removes FK constraints and drops the PK staging tables.P2ZLandDB_Upd.sql creates approximately 92 <Table>_Upd tables. These tables include LandID, BrandCode, source columns, and supporting indexes/constraints.P2ZLandDB_UpdDrop.sql removes FK constraints and drops the update staging tables.P2ZLandDB_pUpd.sql creates approximately 92 stored procedures named dbo.p_<Table>_Upd, each accepting @BrandCode varchar(10) and applying staged update rows to the corresponding table in PaymentCloudDB.The PK and update scripts have explicit additions for newer migrations such as DefaultWithdrawalThreshold and Trans_ID; do not assume the scripts are static or that every package has a matching table/procedure.
PaymentCloudDB.sql is the final cloud database schema reference. It contains approximately 157 destination table definitions and includes BrandCode in the shared multi-brand tables.P2Z_SQLAgent_SetLoggingBasic 1.sql changes P2Z SSIS SQL Agent job steps from logging level 0 to 1 (basic logging).P2Z_SQLAgent_SetLoggingNone 1.sql changes P2Z SSIS SQL Agent job steps from logging level 1 to 0 (no logging).These scripts update msdb.dbo.sysjobsteps for jobs matching P2Z[_]%; run only with the intended SQL Agent permissions and change-control approval.
P2Z_Frequency.xlsx is the only workbook in the folder and is likely the execution-frequency/scheduling reference. Its contents should be verified in Excel before changing job schedules; the presence of the workbook alone is not enough to infer the production schedule.
BackOfficeDB or PaymentDB._P1/_J1 variants.BackOfficeDB_*.sql/PaymentDB_*.sql reference and confirm the destination in PaymentCloudDB.sql.<Table>_PK, <Table>_Upd, and p_<Table>_Upd objects exist in the P2ZLandDB scripts. If not, treat it as a schema migration, not only an SSIS edit.ConnSrcDB, ConnDstDB, ConnBatchDB, credentials, and BrandCode); do not hard-code environment credentials.BrandCode join and the insert/update paths with representative rows for every affected brand.SrcDB connection manager/query; do not infer it only from the filename.P2ZLandDB and P2ZBatchDB. The intended interpretation is final destination PaymentCloudDB, landing/update/PK control in P2ZLandDB, and batch/control support in P2ZBatchDB._P1/_J1 files as “not present in this folder,” not proof that the package is not required for that brand.Representative files used to establish this understanding include AlipayWithdrawalAccount.dtsx, BankAccount.dtsx, BankAccount_P1.dtsx, BankAccount_J1.dtsx, Withdrawal.dtsx, Withdrawal_P1.dtsx, P2ZLandDB_PK.sql, P2ZLandDB_Upd.sql, P2ZLandDB_pUpd.sql, PaymentCloudDB.sql, and both SQL Agent logging scripts. The complete inventory is the set of files currently present beside this report.
This report intentionally excludes passwords, encrypted sensitive values, and full SQL/package contents.