SSIS and the Future – December 2020 Edition

My friend, Sandy Winarko, recently published a fantastic summary of the efforts of the Microsoft SSIS Team to mitigate friction for enterprises seeking to lift and shift SSIS workloads to Azure. In my humble opinion, their work is outstanding.

Looking In From the Outside

As we say in Farmville Virginia, “Data integration and I go back.” What do we mean when we say such a thing? We mean there’s a history, and my history with data integration spans over two decades – back to the days when I was writing semi-dynamic, HTML-generating, Visual Basic 3.0 applications with a Timer control to overwrite htm files on a Windows machine running Personal Web Services (remember PWS?), which in turn served these htm files to an enterprise manufacturing network. Back then, we called it SCADA – an acronym for Supervisory (or System, depending on who you asked) Control And Data Acquisition.

The name has changed (more than once), but the functionality remains intact.

I have no special insight or knowledge regarding Microsoft’s plans.
I have 2+ decades of experience delivering data integration solutions with Microsoft technology.

I base my outside-looking-in perspective on experience, and – to me, at least – it appears Azure is designed to be an “all of the above” solution. Based on my experience-informed perception, I believe SSIS will remain supported for some time to come. How much time? I cannot be sure. My logic for making such a statement is as follows:

  • Microsoft earns money when enterprises use Azure.
  • SSIS is available in Azure.
  • Microsoft earns money from SSIS.

I can hear some of you thinking…

“What if Microsoft Stops Innovating SSIS, Andy?”

That’s an excellent question. I’m glad you asked!

A number of companies have invested in third-party tools and solutions to support and extend SSIS functionality. Consider CozyRoc, SentryOne, CData, KingswaySoft, and ZappySys; just to name a few vendors supplying and supporting SSIS tasks and components that extend the functionality of SSIS. These companies, and others, continue to extend and expand SSIS capabilities.

Plus, books about building SSIS custom tasks continue to be written (the second edition of Building Custom Tasks for SQL Server Integration Services is available for pre-order, and is projected for release 24 Feb 2021). </ShamelessPlug>

Plus, Amazon Web Services (AWS) supports the SSIS Catalog on SQL Server 2016, 2017, and 2019. I blogged about that here.

My answer to your question is: Microsoft is not the only company innovating SSIS.

More Than Money

My outside-in perspective includes support for legacy code. As Buck Woody once tweeted: Another name for legacy code is “code that works.” A number of enterprises (a vast majority at the time of this writing) orchestrate and integrate their data using SSIS executed either on-premises or in data centers. Will many enterprises lift and shift at least some of their SSIS to the cloud? Yes, doubtless yes. Will all? No, doubtless no. My guesstimate is: Most enterprises will lift and shift at least some of their SSIS workload from on-premises to the cloud over the next decade.

What the Microsoft SSIS Team has done over the past couple / three years is reduce the pain of this impending shift. Sandy’s post covers several ways they’ve accomplished pain relief. I can hear many of you thinking…

“Is Pain Relief Their Goal, Andy?”

I think so. At least, pain relief is their short-term goal.

Longer-term, reducing the friction between executing SSIS on-premises and in the cloud expands enterprise data integration options, and more options is always a good thing in engineering.

One advantage of an “all of the above” approach is enterprises may begin by lifting and shifting some of their SSIS to the cloud, and then refactor some of their SSIS packages to other technologies – such as Azure Data Factory (ADF), Azure Synapse Analytics, Azure Databricks, Azure Logic Apps, Azure Functions, and more.

Why refactor?
SSIS is sometimes not the best solution for certain operations or workloads.

Please remember: Software development is a combination of a business problem that needs to be solved, a developer (or developer team), and the technology with which the developer (or team) is familiar. Developers aren’t stupid or evil for creating the best solution they can in the shortest time frame. Instead, they are doing their jobsAnd, if you – as a developer – do not look back at some of the code you produced in the past and cringe, you are not growing as a developer.

As I stated earlier, options are a good thing in engineering.

Conclusion

While change is inevitable, having a plan and understanding a path from here to there eases the pain of change.

Need Help Lifting and Shifting Your Enterprise Data Warehouse and ETL?

Enterprise Data & Analytics

At Enterprise Data & Analytics, we believe you should ask four questions when hiring an enterprise data consultancy.

Enterprise Data & Analytics offers comprehensive Data Warehouse, ETL, SSIS, and Azure Data Factory knowledge, experience, and success to your enterprise. We engage in three phases:

  1. Training – Enterprise Data & Analytics offers enterprise-scale, experience-based training; sharing knowledge from experience with enterprise teams of all sizes.
  2. Partnering – Enterprise Data & Analytics team members join your enterprise team members at the developer, project manager, and developer manager levels to shepherd (freshly-trained) team members as we – together – apply data integration patterns to your enterprise data warehouse solution.
  3. Sustaining – After the code is implemented, tested, documented, and signed-off; Enterprise Data & Analytics team members stand ready to support your enterprise data warehouse team in the event of a disaster, re-join your team to jumpstart the next version, or merely to answer a question.

Contact us today!

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, SSIS and Biml guy. I was cloud before cloud was cool. :{>

2 thoughts on “SSIS and the Future – December 2020 Edition

  1. I think you may have hit the nail on the head regarding future ADF improvements and changes to make some things more painless. But, since I feel like Microsoft’s approach is to always have more than one path to get things done, I suspect on-premise SSIS functions are going to remain for quite some time.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.