Error Handling and Logging in SSIS

 

1. Error Handling in SSIS

A. Precedence Constraints (Success, Failure, Completion)

• Control the flow of tasks based on execution status:

  • Green (Success) – Proceed if the previous task succeeds.

  • Red (Failure) – Execute if the previous task fails.

  • Blue (Completion) – Execute regardless of success/failure.

B. Event Handlers

• Execute custom logic when specific events occur:

  • OnError – Runs when a task fails.

  • OnWarning – Runs when a warning occurs.

  • OnTaskFailed – Runs when a task fails.

  • OnPostExecute – Runs after a task completes successfully.

Example:

• Log errors to a database or file when OnError is triggered.

C. Error Output in Data Flow

• Configure error outputs for transformations and destinations:

  • Ignore Failure – Skip the error and continue.

  • Redirect Row – Send failed rows to an error output.

  • Fail Component – Stop execution on error.

Example:

• Redirect bad rows to an error table for later analysis.

D. Transactions & Checkpoints

• Transactions: Use TransactionOption to roll back on failure.

• Checkpoints: Restart packages from the point of failure.

TOP 7 REAL TIME SSIS Scenarios with solutions

 ### **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.