A few weeks ago I began hearing a rumor that SSIS had a couple years of life remaining. “In two years or so,” according to the rumor, “SSIS will die and be replaced with Azure Data Factory.”
Is Microsoft capable of such a move; deprecating a technology? Yep. Remember SQL Server Notification Services? I can hear some of you thinking, “That was different, Andy.” Was it? Some may recall the deprecation of OLE DB Microsoft is Aligning with ODBC for Native Relational Data Access in August 2011.
I did not doubt someone or something had triggered the rumor. As I am apt to do, I asked questions to seek the answer.
Over Dinner…
In late April 2019 my friend, Sandy Winarko, informed me he was planning an early-May visit from Shanghai (where he lives) to North Carolina. Since Farmville Virginia is a two-hour drive from one of his stops, we planned to meet for an awesome dinner. Sandy (LinkedIn) is Principal Program Manager – Azure Data Governance, Cloud & AI. He is a contributor to many Microsoft Documents regarding Azure-SSIS. Over dinner, I asked Sandy about the rumor, then followed up with more questions via email. What follows are my questions and Sandy’s responses:
AL: Can you tell me what was said to lead people to believe SSIS is nearing the end of its life or internal innovation investment?
SW: People misinterpreted our reducing investments in new connectors as SSIS being phased away, but we want to leverage ADF and our partners to invest in new connectors, while we’re doubling down on our investments in SSIS IR as a lift & shift platform.
AL: At dinner, you shared one path for enterprises to migrate from on-premises SSIS to Azure Data Factory pipelines:
1. Lift and shift existing SSIS to Azure-SSIS (Catalog) or (soon) to Azure Files.
SW: Absolutely.
2. Convert SSIS packages and projects to ADF as needed.
SW: This is limited for few simple standard packages, but nearly impossible for thousands of customized packages.
AL: Will SSIS continue to receive innovation investment?
SW: Absolutely, see above.
Is there a planned date (that you can share) for SSIS innovation to end?
SW: No date, as long as SSIS is bundled w/ SQL Server, there’s always demand to lift & shift, so investment/innovation in SSIS IR continues.
AL: I’ve heard a lot of concern about SSIS being decommissioned expressed in the recent past. Now it fits. Someone made that statement publicly and it’s spreading.
SW: It’s just a misinterpretation of where we’ll focus our investment/innovation.
In an email follow-up, Sandy shared:
We’ll keep adding new functionalities to SSIS, but focusing more on cloud-only/cloud-first features on SSIS in ADF that will be ported back to on-prem SSIS wherever applicable, e.g. we’ve released AKV support, new OLEDB CM that’s configurable for AAD Auth w/ ADF managed identity, ADLS Gen2 support in AFP, Self-Hosted IR as a proxy for SSIS IR (Preview).
Conclusion
In my humble opinion, the reports of SSIS’s demise have been greatly somewhat exaggerated. Has this happened before? Yep. If you didn’t click the link earlier, the deprecation of OLE DB (Microsoft is Aligning with ODBC for Native Relational Data Access) was updated in March 2018 to reflect “OLE DB is now undeprecated” along with the announcement of a new OLE DB driver for SQL Server.
One More Thing…
Lifting and Shifting SSIS from an on-premises SSIS Catalog to a different catalog – whether said target catalog is on-premises (if your enterprise is practicing DevOps or lifecycle management) or part of an Azure Data Factory SSIS Integration Runtime (Azure-SSIS) – is not trivial.
At Enterprise Data & Analytics, we have the tools and expertise to help.
As long as Power Query becomes a first class citizen in SSIS then it will live quite long as many new ETL ppl are becoming familiar with Power BI, Excel and Dataflows, they want the same experience and ease of use in SSIS.