New to Azure Data Factory. Best ETL tool - SSIS or Stored Procedures

Question

Hi Experts,

I am new Azure Data Factory.

For the past 10 years I have been using SSIS to perform the ETL operations.
However, I have received recommendation to use stored procedures going forward rather the SSIS as SSIS is fading away.

Wondering if anybody could please provide advise on best methods of doing ETL operations on Azure Data Factory.

Would it be Stored Procedures or SSIS ? Why and Why not? Anything else that I am not aware of?

Appreciate your help here.

Thanks in advance.

Regards,
Shyam

Answer 1

Hello @Shyam Sreeramula

Azure Data Factory is much more than just executing a stored procedure, you can perform multiple operations using it

You can also run the SSIS packages on cloud using ADF

If you want to get away from SSIS packages and want to fully move to PaaS, then you can identify the efforts needed for the current design to redevelop using ADF components, if you are comfortable with SSIS and want to leverage the cloud integration then you can run your SSIS packages on cloud

SSIS works well when several of the following statements are true:

1) Your workload is mostly or completely on-premises
2) You already own SQL Server 2016 or newer
3) Your ETL processes are running consistently throughout the day, not concentrated just on certain times of the day
4) Your organization already has an investment in SSIS assets

ADF is a great choice when several of the following describe your setup:

1) Your organization is growing, and you want to pay for what you are currently using, not what you plan to use in the future
2) Most or all of your workload is in the cloud
3) You have spikes of activity when your ETL processes are running (nightly, 2x daily, etc.)
4) Your leadership is comfortable with a variable, consumption-based pricing model

You can read more on this here