| Topic | What you’ll learn | |-------|-------------------| | Package architecture | How the demo package is structured for modularity and re‑usability. | | Dynamic source handling | Using variables, expressions, and the Foreach Loop to process an arbitrary number of files. | | Advanced transformations | Implementing Script Component look‑ups, Data‑Driven Query destinations, and Slowly Changing Dimension (SCD) Type 2 logic without third‑party tools. | | Error handling & logging | Configuring Event Handlers, SQL Server Profiler‑style logging, and custom failure notifications via e‑mail. | | Performance tuning | Buffer‑size tuning, Parallel Execution, and using Table‑Lock hints for bulk loads. | | Deployment | Packaging the project as an SSIS Catalog project, setting up environment variables, and using Azure‑enabled data sources. |
All log providers (SQL Server, Text File, Windows Event Log) are enabled in the SSIS Catalog. The video emphasizes configuring LoggingMode = Detailed for the Data Flow component to capture row‑level statistics (rows read, rows written, rows rejected). SSIS-951.mp4
The demo package, named Load_FinancialTransactions.dtsx, follows a four‑stage pipeline: All log providers (SQL Server, Text File, Windows
| Stage | Control‑Flow Component | Purpose |
|-------|------------------------|---------|
| 0 | Variables & Parameters | Global parameters (SourceFolder, FileMask, TargetSchema, LoadDate) plus a Project‑level parameter for the Azure Storage SAS token. |
| 1 | Foreach Loop Container | Enumerates all *.csv files under SourceFolder (e.g., C:\ETL\Incoming\*.csv) and sets the current file path to the variable CurrentFile. |
| 2 | Data Flow Task (DFT_Transform_Transactions) | Reads the CSV, applies a Script Component for custom cleansing, splits rows using a Conditional Split, and directs them to three destinations: a Staging Table, an Error Table, and a Dimensional SCD Type 2 table. |
| 3 | Execute SQL Task (SQL_Apply_SCD) | Runs a set‑based MERGE statement that implements the SCD Type 2 logic on the target dimension. |
| 4 | Send Mail Task (Notify_On_Failure) | Fires only on package failure (wired through an OnError event handler). | All log providers (SQL Server
All components are parameterized so the same package can be promoted across environments (DEV → TEST → PROD) without code changes.