| Source | Destination | Technique | |--------|-------------|-----------| | JSON / XML Files | SQL Table, Azure Data Lake | Use JSON Source (SSIS 2022) or XML Source → Data Conversion → OLE DB Destination. | | Parquet | Azure Synapse, Azure Data Lake | Use Azure Feature Pack → Parquet Source. | | REST API (JSON) | Staging Table | Web Service Task → Script Component to parse JSON into columns; optionally use JSON Source from the SSIS Feature Pack. |
| Symbol | Meaning | Example |
|--------|---------|---------|
| → (green) | Success | DataFlowTask → ExecuteSQLTask |
| → (red) | Failure | DataFlowTask → SendMailTask (OnFailure) |
| → (yellow) | Completion (regardless) | DataFlowTask → ArchiveFileTask (OnCompletion) |
| Expression | Custom Boolean | @[User::RowsLoaded] > 0 |
Best practice: Keep the control‑flow DAG shallow; use containers to avoid tangled precedence constraints. SSIS-927
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:
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.
Resolve the SSIS-927 ticket end-to-end: reproduce, diagnose root cause, implement fix, test, and deploy with rollback plan. Best practice: Keep the control‑flow DAG shallow; use
| Transformation | When to Use | Tips |
|----------------|------------|------|
| Lookup (Full Cache) | Small reference tables (≤ 2 M rows). | Set Cache mode = Full for fastest performance. |
| Lookup (Partial/No Cache) | Large tables, memory‑constrained. | Use Partial and set CacheSize appropriately. |
| Merge Join | Joining two sorted streams. | Sort upstream to avoid spool; use Inner Join for performance. |
| Script Component (Transformation) | Complex row‑level logic (e.g., regex, custom hashing). | Write in C#; expose ReadOnly and ReadWrite columns via Inputs and Outputs. |
| Conditional Split | Route rows based on expression. | Combine multiple predicates in one split to reduce downstream components. |
| Data Conversion | Convert data types before loading to destination. | Prefer native source conversions where possible (e.g., set DataType on OLE DB Source). |
| Multicast | Duplicate a data stream to several branches. | Use sparingly; each branch adds a buffer copy. |
| Recordset Destination | Store rows in an ADO.NET Recordset for later use in a Script Task. | Not recommended for large rowsets (use staging tables instead). |
The retail sector generates massive, heterogeneous data streams: point‑of‑sale (POS) logs, e‑commerce clickstreams, inventory updates from distribution centers, and third‑party marketing feeds. The company behind SSIS‑927—referred to here as RetailCo—consolidates these streams nightly into a centralized data warehouse that powers BI dashboards, demand‑forecasting models, and regulatory reporting. Scenario: A nightly SSIS package loads daily sales
RetailCo’s legacy integration stack consisted of ad‑hoc SQL scripts, custom C# console utilities, and a handful of monolithic SSIS packages that were difficult to version, debug, or scale. By 2019 the business demanded a single, auditable pipeline that could:
SSIS‑927 was commissioned to meet these goals. The following sections detail how the project team translated them into a concrete SSIS architecture and the key engineering decisions that made the solution sustainable.