How to migrate SQL Server Integration Services

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. For those companies who have solutions built with SSIS should think to evolve to Azure and expand 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.

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 to 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, 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.

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;

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 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, but, 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 on 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 different, in fact, by using different components from DataBricks or Azure Data Factory and being some of them more efficient, your logics would change to smaller and faster pipelines which would result in less processing time, resources and costs.

Using Azure 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.