// Locate Data Flow Task (by name) var dfTask = (TaskHost)pkg.Executables .Cast<Executable>() .First(e => ((TaskHost)e).Name == "DF_LoadDynamic");
class FlowBuilder
var pipeline = (MainPipe)dfTask.InnerObject; var source = pipeline.ComponentMetaDataCollection.New(); source.ComponentClassID = "DTSAdapter.FlatFileSource"; SSIS-965
is a defect that surfaces only in runtime , when the metadata (column names, data types, lengths, nullability) that SSIS builds at design‑time does not match the actual schema that the source delivers at execution. The error message looks like:
| Symptom | Business impact | |---------|-----------------| | Package crashes on first row | Batch jobs stop, SLA breach | | Intermittent failures (only when file changes) | Hard to reproduce, support overhead | | Silent data loss (when column is dropped) | Incorrect reporting, audit issues | | Debugging time > 4 h per occurrence | Increased cost, developer fatigue | // Locate Data Flow Task (by name) var
$schema = @() foreach($col in $headers) $schema += [pscustomobject]@ ColumnName = $col.Trim() DataType = 'nvarchar(4000)' # default, can be refined later Nullable = $true
// Configure source connection (assume connection manager already exists) var cm = pkg.Connections["FlatFileConn"]; source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm); source.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID; | Simple; no code changes
| Work‑around | Description | Pros | Cons | |-------------|-------------|------|------| | – set RetainSameConnection = False on the Connection Manager and add a dummy Execute SQL Task that runs SELECT 1 before the Data Flow. | Causes the connection manager to be re‑created at runtime, forcing a new schema read. | Simple; no code changes. | Adds an extra task; may still fail if file is swapped after the dummy task runs. | | B. Use a Staging Table – Load the file into a wide staging table with a varchar(max) column for each field, then perform a set‑based INSERT…SELECT into the final destination after schema validation. | Decouples file schema from the Data Flow; you can validate columns via T‑SQL. | Robust; easy to log errors. | Additional I/O; extra storage; slower for very large files. |