An SSIS package (.dtsx) is converted by parsing every Data Flow Task into a graph of sources, transforms, and destinations — OLE DB/Flat File/Excel sources and destinations, Derived Column, Conditional Split, Data Conversion, Lookup, Merge Join, Aggregate, Sort, Union All, and Multicast all have direct SQL or PySpark equivalents. Control-flow tasks like Execute SQL Task are kept as reference text rather than translated, since they represent orchestration, not row-level logic.
- A .dtsx package can contain multiple independent Data Flow Tasks — each converts as its own graph.
- Data flow components (sources, joins, derived columns, aggregates) map to SQL or PySpark directly; control-flow tasks (Execute SQL Task, Script Task, containers) don't, and shouldn't be forced into the same translation.
- Conditional Split's default (catch-all) branch and Lookup's no-match branch both need manual review, the same way Alteryx's unmatched Join outputs do.
SSIS packages mix orchestration and transformation
A .dtsx file has two layers: control flow, which sequences tasks (Execute SQL Task, Script Task, containers, loops), and data flow, which moves and transforms rows inside a Data Flow Task. Only the second layer is a 1:1 match for SQL or PySpark logic — the first layer is closer to a job scheduler's configuration.
Treating both layers the same way is where naive conversions break. The data flow should convert into real transformation code; the control flow should be preserved as context, not forced into a query.
Data flow components that map directly
The core SSIS data flow components used in most production packages all have a direct SQL or PySpark equivalent.
- OLE DB / Flat File / Excel Source and Destination → source tables and output targets
- Derived Column → calculated columns
- Data Conversion → explicit type casts
- Merge Join, Aggregate, Sort, Union All → JOIN, GROUP BY, ORDER BY, UNION ALL
- Multicast → the same input reused across multiple downstream branches, with no row-level change of its own
What needs manual review
Conditional Split routes rows into named case outputs, each with its own condition — those map cleanly to filtered branches. The default (catch-all) output is different: it represents 'none of the above cases matched,' and synthesizing that as the negation of every other case is not done automatically, so it should be reviewed before being trusted.
Lookup behaves the same way as a join for its match output, but its no-match output — rows that failed to find a match — needs the same manual attention as an anti-join. Execute SQL Task and other control-flow tasks are kept as readable reference text (their SQL, if any, is preserved) rather than merged into the data flow logic.
Where Deflows fits
Deflows parses every Data Flow Task in a .dtsx package into the same internal flow structure used for Tableau Prep, Alteryx, and Power Query. Control-flow tasks are kept as flagged reference nodes — Execute SQL Task's query text is preserved so Document mode can describe what it does — rather than being silently dropped or misrepresented as row-level transforms.
Questions teams ask
Can a package with multiple Data Flow Tasks be converted in one pass?
Yes. Each Data Flow Task in the package is parsed as its own independent graph, so a single .dtsx file with several Data Flow Tasks produces separate, clearly attributed conversion results rather than one merged graph.
What happens to Execute SQL Task and Script Task during conversion?
They are control-flow, not data-flow, so they are not translated into the generated SQL or PySpark. Execute SQL Task's query text is preserved as reference so it isn't lost, and Script Task logic is flagged for manual review since script bodies aren't parsed in the current version.