We all know not everything is forever. SSIS (SQL Server Integration Services) was and is a tool used by many people and companies, but like every technology, it must evolve. That evolution happened gradually with the appearance of cloud technologies and especially Azure and nowadays you can have all SSIS capabilities in Azure Data Factory allied with many more tools. Companies that have solutions built with SSIS should consider evolving to Azure and expanding their data governance and analysis. This can be achieved with some work because you can migrate the solutions you have made with SSIS to Azure.
1. Preparation for migrating SSIS to Azure Data Factory
Ensure that you have everything ready to do this migration. Let us make a list:
a. Guarantee that you have all Azure infrastructure created before starting to do the migration and an instance of SSIS. This is because Azure has a lot of permissions that should be defined to ensure security and data access only to the people allowed to;
b. Install SSIS pack features to Azure to prevent errors or bugs while connecting SSIS projects to Azure;
c. Create a new ADF (Azure Data Factory) instance and configure SSIS so you can create the pipelines to run your projects;
d. Change all your SSIS project connections from local bases to Azure database and change all the steps from SSIS to the steps of Azure;
e. Configure an Azure-SSIS Integration Runtime so you can run all cloud-converted SSIS projects;
f. Before migrating, analyze all your SSIS projects and see their needs and dependencies. Run the projects and see if they are really reading and writing. Sometimes, they give success, but they are doing nothing;
g. Deploy all your projects so they can be stored in the SSIS Catalog and validate if everything is working properly.
2. Advantages: scalability and integration
As you know, using Azure gives great scalability to your projects and to the way you govern data and as such, it has advantages:
a. Scalability: Azure gives you the ability to define computation resources to your needs;
b. Integration with many tools: You can always use other tools and resources depending on what you need, like Azure Blob Storage, Azure Data Lake, etc;
c. Integration with PaaS services: Azure can be integrated with other platforms like Azure Machine Learning or Azure DataBricks, where you can create more advanced data pipelines;
3. Disadvantages: initial complexity
Azure has a lot of advantages but there are some questions too like:
a. Initial complexity: The migration of SSIS projects can be complex and take some time, depending on the number of projects you have and their dependencies;
b. Cloud costs: Since you are migrating your projects from local to the cloud, you still have costs, which, if not managed, can be high;
c. Not taking full advantage of cloud modules: Direct migrations cannot capture all the benefits. For that, you should consider a process reengineering that leverages ADF or even Azure Synapse. In this case, you might even be able to optimize further costs.
This is a fast and awesome solution for you to migrate from an on-prem architecture to a cloud solution Still, even so, we know that if you wanted to migrate your data pipelines to Azure using another tool, it would be a solution too, with a little more work and investment but with a great increase of innovation and agility since you would be evolving your technology stack and cost reduction in the end. What we are saying is that you can reengineer your processes using another tool like Microsoft Fabric, Synapse, DataBricks or Azure Data Factory while enjoying all Azure capabilities. This means that the logic would be similar, but the components would be different. In fact, by using different components from DataBricks or Azure Data Factory and being some of them more efficient, your logic would change to smaller and faster pipelines, which would result in less processing time, resources and costs.
Final thoughts
Using Azure Data Factory requires some effort, but it brings more value, too, as you can have all your SSIS projects in the cloud working properly, well organized and stored, and all your data protected with the right layers of security and permissions. This means you can stop managing your on-prem implementation, saving time and money. In the end, it is something that will increase the agility of your data projects and make you evolve to cloud solutions where you can expand your resources and the tools you need to improve the quality of your projects and data. This can even be the starting point for a full process reengineering using technologies such as Microsoft Fabric that will allow leveraging all benefits from cloud analytics. Our team of experts can help you go through this process, defining what is the best strategy for your specific context and then implementing the cloud migration.