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:

// Locate Data Flow Task (by name) var dfTask = (TaskHost)pkg.Executables .Cast<Executable>() .First(e => ((TaskHost)e).Name == "DF_LoadDynamic");

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

SSIS‑965 – “Data Flow task fails with The data type of the column is unknown ” TL;DR – SSIS‑965 is a long‑standing “metadata‑loss” bug that appears when a Flat File Source (or OLE DB Source ) is used together with dynamic column discovery in a Data Flow that is later reused by a Script Component or Derived Column . The root cause is the way the SSIS runtime caches the metadata of the source at design‑time but discards it at run‑time when the Connection Manager is refreshed with a new schema. The fix is to force a metadata refresh (re‑initialise the component) or, better, to decouple schema discovery from the data flow by using a staging table or Data Flow parameters . Below is a step‑by‑step forensic analysis, a reproducible test case, the official Microsoft KB work‑around, a clean‑room implementation that eliminates the issue, performance considerations, and a checklist for preventing the bug in future projects. 1. Background & Why It Matters SQL Server Integration Services (SSIS) is the ETL engine for the Microsoft data‑platform. A huge proportion of SSIS packages are data‑flow‑centric – they read from a source, perform transformations, and write to a destination.

$firstLine = Get-Content -Path $FilePath -TotalCount 1 $headers = $firstLine -split $Delimiter