Ssis-927 -

Rather than a monolithic package, SSIS‑927 is split into 12 logical sub‑packages, each representing a functional domain (e.g., “POS_Ingest”, “WebLog_Transform”, “Dim_Product_SCD”). The master package orchestrates execution using the Execute Package Task (EPT) with SQL Server Agent jobs as the entry point. This modular design yields:

[Source Systems] → [Staging Layer] → [Validation & Business Rules] → 
[Dimensional Modeling] → [Data Warehouse] → [BI / Analytics]

Scenario:
A nightly SSIS package loads daily sales data into DW_Sales. It runs via a SQL Server Agent job under the service account NT SERVICE\SQLSERVERAGENT. After a weekend security hardening, the job starts failing with: SSIS-927

Error 927: The server principal "NT SERVICE\SQLSERVERAGENT" is not able to access the database "DW_Sales" under the current security context.

Resolution Steps Taken

Takeaway: Even built‑in service accounts need explicit permissions when they access user databases. Rather than a monolithic package, SSIS‑927 is split

A Script Component (C#) evaluates business rules expressed in a domain‑specific language (DSL) that compiles to C# expressions at runtime. Example rule: Scenario: A nightly SSIS package loads daily sales

RuleID = 101;
Expression = "Quantity > 0 && (Discount <= 0.5 || CustomerType == 'VIP')";
Severity = 'Critical';

During execution, the script parses the DSL, builds a Lambda Expression, and applies it to each data row. Violations are written to the ErrorQueue with the rule ID, enabling downstream analysts to trace root causes.