SSIS ETLDemo #trending #ssis #sql #sqlserver #tsql

This video provides a demo of an ETL (Extract, Transform, Load) project using sales data. Here's a quick breakdown: Source Data (0:10-0:24): The project utilizes a CSV file containing sales data with columns such as order ID, product ID, quantity, and unit price. Objective (0:52-1:16): The goal is to load sales transaction data from the CSV file into a sales fact table, while also capturing any invalid data (e.g., missing quantity or unit price) into an error log table. Target Tables (1:55-2:31): The data is loaded into two tables: the sales fact table (for valid data) and the error log table (for invalid data). The sales fact table includes a calculated total amount and the date/time of loading. The error log table captures the error message along with the error date and time (2:31-2:49). ETL Process (3:30-5:20): The control flow truncates the sales fact and error log tables. The data flow reads sales data from the source file (3:59-4:04). Data conversion cleans the quantity and unit price (4:07-4:24). A derived column calculates the total amount (4:36-4:43). The data is split into valid and invalid rows (4:47-5:20). Valid data is inserted into the sales fact table, and invalid data into the error log table (5:05-5:19). Output (5:26-6:21): After the ETL process, valid data is loaded into the sales fact table, and invalid data is loaded into the error log table with an error message. Best Practices (7:46-7:57): The demo adheres to best practices, including error handling, logging invalid rows, data validation, and metadata-driven calculations.