question

ChandraManral-1097 avatar image
0 Votes"
ChandraManral-1097 asked ChandraManral-1097 commented

SSIS workload migration to ADF

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







azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered ChandraManral-1097 commented

Hello @ChandraManral-1097,
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 130616-image.png or upvote 130671-image.png 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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Himanshu for your response.

You gave your inputs regarding points 1 & 2 . I also feel cost wise it won't bring any improvement and sizing of SSIS runtime also has to be done using some baseline configuration and gradually it can be changed based on the observation with workload performance.

However I am still looking for any insight to my queries -3,4,5. In point 5 I basically want to automate the process of calling SSIS packages(which will either be stored in Azure SQL DB or Azure Managed Instance) via ADF pipeline. Whatever demo/doc I followed I have seen that usually pipeline has to be created manually for calling a SSIS package. For an environment where 1000+ packages are there it won't be a feasible option. I was not looking for a method to automate ADF or SSIS runtime creation.

Coming to your question
When you say SSIS job , do you mean SQL jobs which calls a SSIS ?- Yes
and I need to know

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. If not, then We may have to create all the schedules manually after migration to ADF may be.




0 Votes 0 ·