SSIS Script Component - C# Code Implementation Guide

Table of Contents

1. Script Component Architecture
2. Data Type Strategy and Type Safety
3. Helper Methods and Strictness Levels
4. Core Implementation Patterns
5. Output Buffer Management
6. Error Handling and Logging
7. Performance Optimization
8. Best Practices Summary

1. Script Component Architecture

Basic Class Structure

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.OleDb;  // Using OLE DB for performance
using System.IO;
using System.Collections.Generic;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Connection management
    IDTSConnectionManager100 connMgr;
    OleDbConnection conn;
    OleDbDataReader reader;
    
    // Lifecycle methods
    public override void AcquireConnections(object Transaction) { }
    public override void PreExecute() { }
    public override void CreateNewOutputRows() { }
    public override void PostExecute() { }
    public override void ReleaseConnections() { }
}

Method Execution Order

1. AcquireConnections(): Establish database connection
2. PreExecute(): Initialize query and prepare data reader
3. CreateNewOutputRows(): Main data processing loop
4. PostExecute(): Cleanup data reader
5. ReleaseConnections(): Release database connection

Key Design Principles

• Type Safety: All data conversions handled through helper methods
• Null Handling: Automatic management of SSIS _IsNull properties
• Error Resilience: Configurable strictness levels for data quality
• Performance: Optimized for large datasets with progress tracking

2. Data Type Strategy and Type Safety

Our Data Type Mapping Strategy

Money Fields as Strings - Strategic Decision

Business Requirement: Our destination schema expects money fields as nvarchar(50) instead of decimal(28,2).

// WRONG: Would cause schema mismatch
// AssignValue(GetNullableDecimal(reader["l_Loan_Amount"]), ...);

// CORRECT: Money as string for schema compatibility
AssignString(GetString(reader["l_Loan_Amount"]), 
    val => Output0Buffer.lLoanAmount = val, 
    isNull => Output0Buffer.lLoanAmount_IsNull = isNull);

Why This Works:

• SQL Server's money type converts cleanly to string representation
• Preserves exact precision without floating-point errors
• Maintains compatibility with existing table schemas
• Easily reversible in downstream processes if needed

Conservative Field Sizing Strategy

// Default string length: 512 characters (conservative approach)
private string GetString(object value, int maxLength = 512, bool truncate = false, 
                        string fieldName = "String", int strictnessLevel = 0)

Benefits:

• Prevents data truncation in edge cases
• Simplifies configuration (one size for most fields)
• Trade-off: Storage overhead vs. data safety

3. Helper Methods and Strictness Levels

Strictness Level System

Our implementation uses a 3-level strictness system for data quality control:

• Level 0 (Silent): Accept nulls/issues quietly, log nothing
• Level 1 (Warning): Log warnings for nulls/invalid data but continue processing
• Level 2 (Error): Throw exceptions for nulls/invalid data, fail the component

String ID Field Handler with Validation

private string GetStringID(object value, string fieldName = "ID", int maxLength = 50, int strictnessLevel = 2)
{
    if (value == null || value == DBNull.Value)
    {
        if (strictnessLevel == 2)
        {
            ComponentMetaData.FireError(0, "NULL_ID_ERROR", 
                $"CRITICAL ERROR: NULL value found in required string ID field '{fieldName}'", "", 0, out bool cancel);
            throw new Exception($"NULL value not allowed in string ID field '{fieldName}'");
        }
        else if (strictnessLevel == 1)
        {
            ComponentMetaData.FireWarning(0, "NULL_ID", 
                $"NULL value found in string ID field '{fieldName}' - keeping as null", "", 0);
        }
        return null;
    }
    
    string str = value.ToString().Trim();
    
    if (string.IsNullOrEmpty(str))
    {
        if (strictnessLevel == 2)
        {
            ComponentMetaData.FireError(0, "EMPTY_ID_ERROR", 
                $"CRITICAL ERROR: Empty value found in required string ID field '{fieldName}'", "", 0, out bool cancel);
            throw new Exception($"Empty value not allowed in string ID field '{fieldName}'");
        }
        else if (strictnessLevel == 1)
        {
            ComponentMetaData.FireWarning(0, "EMPTY_ID", 
                $"Empty value found in string ID field '{fieldName}' - keeping as null", "", 0);
        }
        return null;
    }
    
    if (str.Length > maxLength)
    {
        ComponentMetaData.FireWarning(0, "TRUNCATE_ID", 
            $"Truncating ID field '{fieldName}' from {str.Length} to {maxLength} characters", "", 0);
        return str.Substring(0, maxLength);
    }
    
    return str;
}

Nullable DateTime with Comprehensive Error Handling

private DateTime? GetNullableDateTime(object value, string fieldName = "DateTime", int strictnessLevel = 0)
{
    if (value == null || value == DBNull.Value)
    {
        if (strictnessLevel == 2)
        {
            ComponentMetaData.FireError(0, "NULL_DATETIME_ERROR", 
                $"CRITICAL ERROR: NULL value found in DateTime field '{fieldName}'", "", 0, out bool cancel);
            throw new Exception($"NULL value not allowed in DateTime field '{fieldName}'");
        }
        else if (strictnessLevel == 1)
        {
            ComponentMetaData.FireWarning(0, "NULL_DATETIME", 
                $"NULL value found in DateTime field '{fieldName}' - keeping as null", "", 0);
        }
        return null;
    }
    
    try
    {
        return Convert.ToDateTime(value);
    }
    catch (Exception ex)
    {
        if (strictnessLevel == 2)
        {
            ComponentMetaData.FireError(0, "INVALID_DATETIME_ERROR", 
                $"CRITICAL ERROR: Invalid DateTime value in field '{fieldName}': {value} - Error: {ex.Message}", "", 0, out bool cancel);
            throw new Exception($"Invalid value in DateTime field '{fieldName}': {value}");
        }
        else if (strictnessLevel == 1)
        {
            ComponentMetaData.FireWarning(0, "INVALID_DATETIME", 
                $"Invalid DateTime value in field '{fieldName}': {value} - keeping as null. Error: {ex.Message}", "", 0);
        }
        return null;
    }
}

Decimal with Precision Control

private decimal? GetNullableDecimal(object value, int? digits = null, string fieldName = "Decimal", int strictnessLevel = 0)
{
    if (value == null || value == DBNull.Value)
    {
        if (strictnessLevel == 2)
        {
            ComponentMetaData.FireError(0, "NULL_DECIMAL_ERROR", 
                $"CRITICAL ERROR: NULL value found in Decimal field '{fieldName}'", "", 0, out bool cancel);
            throw new Exception($"NULL value not allowed in Decimal field '{fieldName}'");
        }
        else if (strictnessLevel == 1)
        {
            ComponentMetaData.FireWarning(0, "NULL_DECIMAL", 
                $"NULL value found in Decimal field '{fieldName}' - keeping as null", "", 0);
        }
        return null;
    }
    
    try
    {
        decimal result = Convert.ToDecimal(value);
        if (digits.HasValue)
            return Math.Round(result, digits.Value);
        return result;
    }
    catch (Exception ex)
    {
        if (strictnessLevel == 2)
        {
            ComponentMetaData.FireError(0, "INVALID_DECIMAL_ERROR", 
                $"CRITICAL ERROR: Invalid Decimal value in field '{fieldName}': {value} - Error: {ex.Message}", "", 0, out bool cancel);
            throw new Exception($"Invalid value in Decimal field '{fieldName}': {value}");
        }
        else if (strictnessLevel == 1)
        {
            ComponentMetaData.FireWarning(0, "INVALID_DECIMAL", 
                $"Invalid Decimal value in field '{fieldName}': {value} - keeping as null. Error: {ex.Message}", "", 0);
        }
        return null;
    }
}

Three-State Boolean Logic

private bool? GetNullableBoolean(object value)
{
    if (value == null || value == DBNull.Value)
        return null;
    
    if (value is bool boolValue)
        return boolValue;
    
    if (value is string stringValue)
    {
        if (bool.TryParse(stringValue, out bool parsedBool))
            return parsedBool;
        
        string normalized = stringValue.Trim().ToLowerInvariant();
        switch (normalized)
        {
            case "1":
            case "yes":
            case "y":
                return true;
            case "0":
            case "no":
            case "n":
                return false;
            default:
                throw new ArgumentException($"Invalid boolean value: '{stringValue}'");
        }
    }
    
    if (value is int intValue)
    {
        if (intValue == 0) return false;
        if (intValue == 1) return true;
        throw new ArgumentException($"Invalid boolean value: {intValue}. Only 0 or 1 allowed.");
    }
    
    throw new ArgumentException($"Invalid type for boolean conversion: {value.GetType().Name}");
}

4. Core Implementation Patterns

Connection Management

public override void AcquireConnections(object Transaction)
{
    connMgr = this.Connections.Connection;
    conn = (OleDbConnection)connMgr.AcquireConnection(null);
    
    bool fireAgain = false;
    ComponentMetaData.FireInformation(0, "CONNECTION", 
        $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Connection acquired successfully", "", 0, ref fireAgain);
}

public override void ReleaseConnections()
{
    connMgr.ReleaseConnection(conn);
    
    bool fireAgain = false;
    ComponentMetaData.FireInformation(0, "RELEASE", 
        $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Connection released", "", 0, ref fireAgain);
}

File-Based SQL Query Loading

public override void PreExecute()
{
    base.PreExecute();
    
    // Show column metadata first (optional debugging feature)
    ShowInputColumnTypes();
    
    // Load SQL from external file for maintainability
    string path = @"c:\PCLender\PCL_Update\pcl_update.sql";
    string sql = File.ReadAllText(path);
    
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    cmd.CommandTimeout = 300; // 5 minutes for large queries
    reader = cmd.ExecuteReader();
    
    bool fireAgain = false;
    ComponentMetaData.FireInformation(0, "PREEXECUTE", 
        $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Query loaded and reader initialized", "", 0, ref fireAgain);
}

Advantages of File-Based SQL:

• Version control: SQL changes tracked separately from C# code
• Team collaboration: Database developers can modify queries independently
• No recompilation: SQL changes don't require rebuilding SSIS package
• Complex queries: Support for very large, sophisticated SQL statements

Main Data Processing Loop

public override void CreateNewOutputRows()
{
    int rowCount = 0;
    int maxRows = 300; // Configurable limit for testing/development
    bool fireAgain = false;
    
    ComponentMetaData.FireInformation(0, "PROGRESS", 
        $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Starting data processing - Target: {maxRows} rows", "", 0, ref fireAgain);
    
    while (reader.Read() && rowCount < maxRows)
    {
        rowCount++;
        Output0Buffer.AddRow();
        
        // Type-safe assignments using helper methods
        ProcessRowData();
        
        // Progress logging every 50 rows
        if (rowCount % 50 == 0)
        {
            ComponentMetaData.FireInformation(0, "PROGRESS", 
                $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Processed {rowCount} rows", "", 0, ref fireAgain);
        }
    }
    
    ComponentMetaData.FireInformation(0, "COMPLETE", 
        $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Total rows processed: {rowCount}", "", 0, ref fireAgain);
}

5. Output Buffer Management

Bulk Assignment Helpers

Our implementation uses sophisticated assignment helpers that automatically handle SSIS _IsNull properties:

// For nullable value types (DateTime, int, decimal, etc.)
private void AssignValue<T>(T? value, Action<T> setValue, Action<bool> setIsNull) where T : struct
{
    if (value.HasValue)
    {
        setValue(value.Value);
    }
    else
    {
        setIsNull(true);
    }
}

// For reference types (strings)
private void AssignString(string value, Action<string> setValue, Action<bool> setIsNull)
{
    if (value != null)
    {
        setValue(value);
    }
    else
    {
        setIsNull(true);
    }
}

Type-Safe Row Processing Pattern

private void ProcessRowData()
{
    // STRING ID FIELDS - Critical business keys (Level 2: Error on null)
    AssignString(
        GetStringID(reader["l_Loan_Number_Id"], "lLoanNumberId", strictnessLevel: 2), 
        val => Output0Buffer.lLoanNumberId = val, 
        isNull => Output0Buffer.lLoanNumberId_IsNull = isNull
    );
    
    AssignString(
        GetStringID(reader["program_program_ID"], "programprogramID", strictnessLevel: 1), 
        val => Output0Buffer.programprogramID = val, 
        isNull => Output0Buffer.programprogramID_IsNull = isNull
    );
    
    // DATETIME FIELDS - Business dates (Level 0: Silent nulls OK)
    AssignValue(
        GetNullableDateTime(reader["DatesPivot_ApplicationDate"]), 
        val => Output0Buffer.DatesPivotApplicationDate = val, 
        isNull => Output0Buffer.DatesPivotApplicationDate_IsNull = isNull
    );
    
    // CRITICAL DATETIME FIELDS - Required (Level 2: Error on null)
    AssignValue(
        GetNullableDateTime(reader["DatesPivot_LoSCreatedDateTime"], strictnessLevel: 2), 
        val => Output0Buffer.DatesPivotLoSCreatedDateTime = val, 
        isNull => Output0Buffer.DatesPivotLoSCreatedDateTime_IsNull = isNull
    );
    
    // MONEY FIELDS - As strings for schema compatibility
    AssignString(
        GetString(reader["l_Loan_Amount"]), 
        val => Output0Buffer.lLoanAmount = val, 
        isNull => Output0Buffer.lLoanAmount_IsNull = isNull
    );
    
    // NUMERIC FIELDS - With controlled precision
    AssignValue(
        GetNullableDecimal(reader["l_Rate"], 3), // 3 decimal places
        val => Output0Buffer.lRate = val, 
        isNull => Output0Buffer.lRate_IsNull = isNull
    );
    
    // INTEGER FIELDS - Business logic conversion
    AssignValue(
        GetNullableInt32(reader["l_Customer_Credit_Score"]), 
        val => Output0Buffer.lCustomerCreditScore = val, 
        isNull => Output0Buffer.lCustomerCreditScore_IsNull = isNull
    );
    
    // BOOLEAN FIELDS - Three-state logic
    AssignValue(
        GetNullableBoolean(reader["loan2_LORequestCompensationInPricing"]), 
        val => Output0Buffer.loan2LORequestCompensationInPricing = val, 
        isNull => Output0Buffer.loan2LORequestCompensationInPricing_IsNull = isNull
    );
}

Column Name Mapping Pattern

Key Pattern: SQL column (with underscores) → Output buffer (without underscores)

// SQL Column: "l_Loan_Number_Id" → Output Property: "lLoanNumberId"
AssignString(
    GetStringID(reader["l_Loan_Number_Id"], "lLoanNumberId", strictnessLevel: 2), 
    val => Output0Buffer.lLoanNumberId = val,
    isNull => Output0Buffer.lLoanNumberId_IsNull = isNull
);

// SQL Column: "DatesPivot_ApplicationDate" → Output Property: "DatesPivotApplicationDate" 
AssignValue(
    GetNullableDateTime(reader["DatesPivot_ApplicationDate"]), 
    val => Output0Buffer.DatesPivotApplicationDate = val,
    isNull => Output0Buffer.DatesPivotApplicationDate_IsNull = isNull
);

6. Error Handling and Logging

Comprehensive Logging Strategy

Our implementation includes detailed logging at multiple levels with timestamps:

// Connection status
ComponentMetaData.FireInformation(0, "CONNECTION", 
    $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Connection acquired successfully", "", 0, ref fireAgain);

// Progress during processing  
if (rowCount % 50 == 0)
{
    ComponentMetaData.FireInformation(0, "PROGRESS", 
        $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}] Processed {rowCount} rows with type-safe conversions", "", 0, ref fireAgain);
}

// Warning conditions with context
ComponentMetaData.FireWarning(0, "TRUNCATE", 
    $"Truncating value from {str.Length} to {maxLength} characters in field '{fieldName}'", "", 0);

// Error conditions with full context
ComponentMetaData.FireError(0, "NULL_ID_ERROR", 
    $"CRITICAL ERROR: NULL value found in required field '{fieldName}' - processing cannot continue", "", 0, out bool cancel);

Error Recovery Patterns

public override void CreateNewOutputRows()
{
    try
    {
        while (reader.Read() && rowCount < maxRows)
        {
            try
            {
                rowCount++;
                Output0Buffer.AddRow();
                ProcessRowData();
            }
            catch (Exception rowEx)
            {
                // Log row-level error but continue processing
                bool fireAgain = false;
                ComponentMetaData.FireWarning(0, "ROW_ERROR", 
                    $"Row {rowCount} processing error: {rowEx.Message} - skipping row", "", 0);
                
                // Could implement row-level error table logging here
            }
        }
    }
    catch (Exception ex)
    {
        bool fireAgain = false;
        ComponentMetaData.FireError(0, "PROCESSING_ERROR", 
            $"Critical processing failure: {ex.Message}", "", 0, out fireAgain);
        throw; // Re-throw to fail the component
    }
}

Input Column Metadata Analysis (Debugging Feature)

private void ShowInputColumnTypes()
{
    try
    {
        bool fireAgain = false;
        string path = @"c:\PCLender\PCL_Update\pcl_update.sql";
        string sql = File.ReadAllText(path);
        
        using (OleDbCommand debugCmd = new OleDbCommand(sql, conn))
        using (OleDbDataReader debugReader = debugCmd.ExecuteReader())
        {
            ComponentMetaData.FireInformation(0, "INPUT TYPES", 
                "=== INPUT COLUMN DATA TYPES ===", "", 0, ref fireAgain);
            
            int columnCount = debugReader.FieldCount;
            
            // Statistical analysis arrays
            int[] nullCounts = new int[columnCount];
            HashSet<object>[] uniqueValues = new HashSet<object>[columnCount];
            
            for (int i = 0; i < columnCount; i++)
            {
                uniqueValues[i] = new HashSet<object>();
            }
            
            // Analyze data patterns
            int totalRows = 0;
            while (debugReader.Read())
            {
                totalRows++;
                for (int i = 0; i < columnCount; i++)
                {
                    object value = debugReader.GetValue(i);
                    
                    if (value == null || value == DBNull.Value)
                    {
                        nullCounts[i]++;
                    }
                    else
                    {
                        uniqueValues[i].Add(value);
                    }
                }
            }
            
            // Report column analysis
            for (int i = 0; i < columnCount; i++)
            {
                string columnName = debugReader.GetName(i);
                string dataType = debugReader.GetFieldType(i).Name;
                int uniqueCount = uniqueValues[i].Count;
                
                ComponentMetaData.FireInformation(0, "COLUMN TYPE",
                    $"Column {i:D2}: '{columnName}' ({dataType}) | Nulls: {nullCounts[i]} | Uniques: {uniqueCount}", "", 0, ref fireAgain);
            }
            
            ComponentMetaData.FireInformation(0, "INPUT TYPES", 
                $"Analysis complete: {columnCount} columns, {totalRows} rows", "", 0, ref fireAgain);
        }
    }
    catch (Exception ex)
    {
        ComponentMetaData.FireError(0, "TYPE_ANALYSIS_ERROR", 
            $"Error analyzing input types: {ex.Message}", "", 0, out bool cancel);
    }
}

7. Performance Optimization

Row Limiting for Development

public override void CreateNewOutputRows()
{
    int maxRows = 300; // Configurable - increase for production
    int rowCount = 0;
    
    while (reader.Read() && rowCount < maxRows)
    {
        // Process rows...
        rowCount++;
        
        // Progress logging for long-running processes
        if (rowCount % 100 == 0)
        {
            ComponentMetaData.FireInformation(0, "PROGRESS", 
                $"Processed {rowCount} of {maxRows} rows", "", 0, ref fireAgain);
        }
    }
}

Memory Management for Large Result Sets

// Configure command timeout for large queries
public override void PreExecute()
{
    base.PreExecute();
    
    string sql = File.ReadAllText(sqlFilePath);
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    cmd.CommandTimeout = 300; // 5 minutes for complex queries
    
    reader = cmd.ExecuteReader();
}

Connection Pooling Optimization

public override void AcquireConnections(object Transaction)
{
    connMgr = this.Connections.Connection;
    conn = (OleDbConnection)connMgr.AcquireConnection(null);
    
    // Ensure connection is open and ready
    if (conn.State != ConnectionState.Open)
    {
        conn.Open();
    }
    
    ComponentMetaData.FireInformation(0, "CONNECTION", 
        $"Connection state: {conn.State}, Server version: {conn.ServerVersion}", "", 0, ref fireAgain);
}

8. Best Practices Summary

Design Phase

1. Survey your SQL schema for underscore usage before starting development
2. Choose consistent data type strategy early (especially for money fields)
3. Plan strictness levels per field based on business requirements
4. Design external SQL file structure for team collaboration

Implementation Phase

5. Use type-safe helper methods with configurable strictness levels
6. Implement assignment helpers to automate _IsNull property management
7. Add comprehensive logging with timestamps for production monitoring
8. Handle errors gracefully with row-level vs. component-level strategies

Testing Phase

9. Test with representative data volumes early in development
10. Validate null handling across all strictness levels
11. Monitor performance with different row limits
12. Test error scenarios to ensure graceful degradation

Production Phase

13. Monitor error logs for data quality patterns
14. Adjust row limits based on actual performance requirements
15. Version control SQL files separately from SSIS packages
16. Document strictness level decisions for future maintenance

Code Organization

17. Keep helper methods in logical groups by data type
18. Use consistent naming for fields and parameters
19. Comment strictness level decisions in the code
20. Create reusable libraries for common patterns across projects

When to Use This Approach

Ideal for:

• Complex type conversion requirements beyond standard SSIS components
• Robust error handling and data quality validation needs
• Custom business logic during data extraction
• Schema alignment between different pipeline approaches
• Detailed logging and monitoring requirements

Consider Alternatives When:

• Simple passthrough scenarios with minimal transformation
• Source schema follows C# naming conventions (no underscores)
• Performance is more critical than maintainability
• Team lacks C# development expertise

This implementation provides a robust foundation for enterprise SSIS Script Component development that balances type safety, maintainability, and performance while handling complex real-world data integration challenges.