P2Z Project Handover Report

Generated from the files present in C:\Users\KM Lai\Documents\P2Z on 2026-09-08.

Executive understanding

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:

Inventory observed

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).

Package naming and brand variants

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:

Variant 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.

SSIS package architecture

The packages inspected use three principal OLE DB connection managers:

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.

SQL assets

Source schema/reference scripts

These SQL files are schema/reference scripts, not SSIS packages. They are useful for checking table/column/PK drift before editing a .dtsx file.

Landing database scripts (P2ZLandDB)

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.

Destination schema

SQL Agent logging controls

These scripts update msdb.dbo.sysjobsteps for jobs matching P2Z[_]%; run only with the intended SQL Agent permissions and change-control approval.

Scheduling/reference

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.

Recommended change workflow

  1. Identify the target table and whether its source is BackOfficeDB or PaymentDB.
  2. Check the matching base/F1 package and any available _P1/_J1 variants.
  3. Compare source columns and PKs against the relevant BackOfficeDB_*.sql/PaymentDB_*.sql reference and confirm the destination in PaymentCloudDB.sql.
  4. Confirm that the required <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.
  5. Preserve the package’s project-parameter pattern (ConnSrcDB, ConnDstDB, ConnBatchDB, credentials, and BrandCode); do not hard-code environment credentials.
  6. Validate the lookup’s PK/BrandCode join and the insert/update paths with representative rows for every affected brand.
  7. Test update procedure execution and row counts in the landing and cloud databases, then verify SQL Agent logging/schedule settings separately.

Known uncertainties / takeover checklist

Files reviewed at a glance

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.