### **Real-Time Scenarios in SSIS with Solutions**
SSIS (SQL Server Integration Services) is widely used for **ETL (Extract, Transform, Load)** processes. Below are some **real-world SSIS challenges** and their solutions.
---
## **1. Scenario: Slow Data Load from Source to Destination**
### **Problem:**
- A large dataset (millions of rows) is taking too long to load.
- The package fails due to timeouts.
### **Solutions:**
✔ **Use Batch Processing** – Split data into smaller chunks (e.g., 10,000 rows per batch).
✔ **Optimize Destination Settings** –
- Use **Table Lock** for bulk inserts.
- Set **Batch Size = 10,000** in the OLE DB Destination.
✔ **Increase Buffer Size** – Adjust **DefaultBufferMaxRows** and **DefaultBufferSize** in Data Flow properties.
✔ **Use Fast Load Option** – Enable **"Fast Load"** in OLE DB Destination.
---
## **2. Scenario: Handling Flat File Import Errors**
### **Problem:**
- A CSV file has missing columns, wrong data types, or corrupt rows.
- The package fails and stops processing.
### **Solutions:**
✔ **Use Error Outputs** – Redirect bad rows to an error log table.
✔ **Data Conversion Task** – Explicitly convert columns before loading.
✔ **Flat File Source Error Handling** –
- Set **"Ignore truncation errors"** if needed.
- Use a **Script Component** to validate data before loading.
---
## **3. Scenario: Dynamic File Import (Changing File Names)**
### **Problem:**
- Need to import files with names like `Sales_20240623.csv`, `Sales_20240624.csv`, etc.
- Hardcoding filenames is not scalable.
### **Solutions:**
✔ **Use Variables & Expressions** –
- Set a variable like `User::FileName = "Sales_" + (DT_STR, 8, 1252)GETDATE() + ".csv"`
- Use **Expressions** in the **Flat File Connection Manager** to dynamically set the file path.
✔ **Foreach Loop Container** – Loop through all files in a folder.
