SSIS workload migration to ADF

Question

Hi,

For one of our customer who is looking for migration of SSIS to PaaS , we are proposing migration to ADF approach to run them. However I have few queries around it-

1. Costwise -will there be any saving if they run their SSIS workload with ADF If not that what benefits they get by moving to ADF because SSIS is more mature and developer friendly for ETL as compared to ADF and they might still continue their development in SSIS and use ADF just for deployment
2. What is the best way to do sizing for SSIS Integration run time machine that can support existing workload?
3. Currently they multiple instances of SSIS running (may be supporting different groups). What should be our strategy to decide if there should be multiple ADF instances or single ADF with multiple SSIS integration run time
4. As destination package store Azure Sql DB or Sql server Managed Instance , based on which factor We should pick one
5. Once We migrate the package , We need to create a ADF pipeline corresponding to it. Is there a way We can automate this process so that such pipelines can be created in one go for all the migrated packages
6. SSIS jobs which are currently running with sql server agent , can they be migrated as well if my destination package store is SSIDB catalog or is it only supported if file store is the destination package store.

Appreciate your insights on above points .

Thanks,
Chandra

Answer 1

Hello @Chandra Manral ,
Thanks for the question and using MS Q&A platform.
Running the SSIS package from ADF is not going to bring cost advantage as this will only acts like a scheduling agent . I think one big adantage of using the ADF that you do not bother about the compute power . With my experiance I have seen that SSIS are deployed in a SQL in-prermise instance and when we have a huge data pull going on and the server is in pressure , it is impacting all the services which has a depenedency on the server .

The other point is how do you see the feature of the current work . Do you have plans to move the data files ( csv, paraquet , avro etc ) to cloud storage from in premise , if yes ADF does have more connector supporting these . ADF is a very new product and is still implamenating new features every month .

On the ask as to how to size the SSIS -IR, i think you are planning for your known managed instance or Azure SQL . You will have to try out few option and establish that . if I where you , I could have Azure-SSIS IR to setup the baseline( if possible ) . It gives you the option to scale up and down , which should help .

On the ask as to how to decide SSIS to ADF mapping mapping , The location of the data and the package is the key . I think the below lines for here should help you .

The location of your Azure-SSIS IR does not need to be the same as the location of your Data Factory, but it should be the same as the location of your own Azure SQL Database or SQL Managed Instance where SSISDB is located. This way, your Azure-SSIS Integration Runtime can easily access the SSISDB without incurring excessive traffic between different locations.

Fort automating the creation of ADF , you can refer this .

When you say SSIS job , do you mean SQL jobs which calls a SSIS ?

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators