Your SSIS Packages Are the Spec

Ask anyone who has scoped an SSIS migration where the budget actually went. Not into the new platform. It went into reading: opening packages one at a time, squinting at data flows, and transcribing fifteen years of undocumented business logic into a spreadsheet so someone could rebuild it somewhere else.

‍

That work exists because most SSIS estates outlived their authors. The developer who built the 2008 packages left years ago. The wiki stopped being true around the same time. What remains is the one artifact that never lies about what the ETL does: the packages themselves.

‍

So the packages are the spec. And the first question to ask of any modernization tool is not what it can generate. Ask what it can read.

‍

Reading is the real work

‍

A .dtsx file is XML. Every connection string, every SQL statement, every derived column expression, every precedence constraint sits in a machine-readable document. Migration projects still put humans on transcription duty anyway, because most tooling around SSIS was built to execute packages, not to translate them.

‍

BimlFlex comes at modernization from the other end. Before anything gets generated for a new platform, the legacy estate gets imported: reverse-engineered from package XML into Biml, the readable markup language that has described SSIS assets since 2008. In Biml form the logic can be inspected, diffed, refactored, and promoted into the kind of metadata model we described in what metadata-driven automation is.

‍

The importer ships in BimlStudio, and the same engine is exposed through the Biml compiler's command line (--importSsis, driven by a JSON options file), so an import can run as a scripted, repeatable step instead of a click-through.

‍

Four ways in

‍

Real estates are messy, so the importer accepts legacy SSIS from four different surfaces. They appear in the product as four import sources:

‍

• DTSX From File System. Loose package files, and more than one at a time. This is the path for the folder of packages you inherited with no project around them.
• DTProj From File System. A full Visual Studio SSIS project, imported with its project structure and project-level connections intact.
• ISPAC From File System. The built deployment artifact. Useful when the compiled .ispac is the thing you trust, or the only thing you still have.
• Project From Package Catalog. Point the importer at a live SSIS Catalog: name the server, the folder, and the project, and it pulls the deployed project straight out of SSISDB.

‍

That last one matters more than it looks. On long-lived estates, source control and production drift apart. Packages get patched on the server during an incident and the fix never lands back in the repo. When the two disagree, production is the truth, and an importer that can only read files off a developer's machine will faithfully modernize the wrong version. Reading the running catalog closes that gap.

‍

One component, many identities

‍

Here is the part that separates a real importer from a demo. SSIS is over twenty years old, and the same logical component has carried different identities across its releases. Depending on which version of SSIS saved the package, an OLE DB Source is identified in the XML by one of three class GUIDs, a ProgID like DTSAdapter.OLEDBSource.3, or the friendly name newer formats use:

‍

<component refId="Package\DFT Load Customer\OLE DB Source" 

          componentClassID="Microsoft.OLEDBSource" ...> 

‍

The importer recognizes all six identities of that one component and maps every one of them to the same Biml element. Multiply that across the catalog: over 200 registered component identities in total, with dedicated translation for 48 control-flow task types, 32 data-flow transforms, 26 connection manager types, 21 destinations, and 16 sources, plus the containers and event handlers that hold them together. Coverage reaches past stock Microsoft components too: the Attunity Oracle and ODBC connectors, the Theobald SAP source, and the Azure Feature Pack components for Blob Storage, Data Lake, Flexible File, and HDFS endpoints all import.

‍

Version detection is automatic as well. Every package declares its format in the XML:

‍

<DTS:Property DTS:Name="PackageFormatVersion">6</DTS:Property> 

‍

The importer reads that property and adjusts: 2 means SSIS 2005, 3 means 2008, 6 means 2012, 8 and up mean 2014 or later. You can pin the version manually, but you rarely need to. A folder that mixes 2008-era packages with 2019 ones imports in one pass.

‍

What comes out the other side

‍

The output of an import is working Biml: packages, project-level connections, file formats, and the script projects behind your Script Tasks, all as readable markup you own. Import options control fidelity. You can keep the original SSIS names and IDs, carry SSIS annotations across, and stamp every imported item with the package it came from, so six months later you can still trace a line of Biml back to its source package.

‍

Two details do disproportionate work in practice. First, the importer can build an offline schema during import: it reconstructs table and column definitions, down to length, precision, scale, and code page, from the external metadata each package already carries. That means you can import an estate without the original source systems being reachable, which is exactly the situation you are in when the estate belongs to a system being retired.

‍

Second, the import tells you what it could not do. Components it cannot map produce warnings in the import log rather than silently vanishing, and destination columns left unmapped can be flagged with annotations. On a thousand-package estate, an honest list of the two dozen things needing human attention is worth more than a claim of full coverage.

‍

The on-ramp, not the destination

‍

An importer this thorough still only gets you to the starting line, and it is worth being plain about that. Import gives you a faithful, readable model of what your ETL does today, including the parts that only exist because of a workaround from 2011. Modernization is deciding what that logic should become. Mechanically converting every package one-for-one onto a new engine just relocates your technical debt, a trap we covered in cloud data warehouse migrations.

‍

The BimlFlex path runs through the model instead. Imported logic gets rationalized into metadata: sources, targets, mappings, and load patterns. From that metadata, BimlFlex generates the new estate natively, whether that is Azure Data Factory pipelines, Databricks, Snowflake, or, when the answer is to stay put and clean house, current-version SSIS. The generated code lands in your repos and runs in your tenant, which is the difference between building an asset and renting a liability.

‍

The packages were never the asset. The logic inside them is. An importer that can read every surface your estate lives on, and every identity a component has worn since 2005, is how you keep the logic and leave the platform.