Azure Data Factories vs SSIS

Question

asked Jul 5, 2019 in Azure by Vaibhav Ameta (17.6k points)

I am thinking of moving our SSIS ETLs to Azure Data Factory. My arguments in favor of such leap are:

Our sources and targets are already in the cloud. ADF is cloud-native so it seems at a good fit.
ADF is a service are therefore we could consume and pay for it on demand. SSIS implies licensing costs and doesn't lend it itself naturally for on-demand consumption (we thought of using DevOps to spin ETL servers on an ad-hoc basis)
Generating ETL code programmatically with SSIS requires very specific skills such as BIML or the DTS API. By moving to ADF I am hoping the combination of JSON and the TSQL and C# in USQL will make the necessary skills more generic.

I am hoping members of the community can share their experiences and thus help me come to a decision.

1 Answer

Ashok · Answer 1 · 2019-07-08T05:43:13+0000

Azure Data Factory is a cloud-based integration service that is Platform as a Service tool, so it does not require any hardware or installation. SSIS is a desktop tool that requires a server with well-defined size which we have to manage. We have to install SQL Server with SSIS.
JSON scripts are used by Azure Data Factory for its orchestration(coding) whereas SSIS uses drag and drop tasks(no coding).
Azure Data Factory is pay-as-you-go service through Azure Subscription whereas SSIS costs only for the license as a part of the SQL server.
Azure Data Factory can make use of HDInsights clusters and run pig & hive scripts. SSIS is also one of the services present in Azure which is accessed through Azure Feature Pack for Integration Services.
Azure Data Factory has a basic editor and no intelligence or debugging tool whereas SSIS provides us powerful GUI, intelligence and debugging tools.
Azure Data Factory is controlled through the Azure portal whereas SSIS is controlled through SSMS.
Azure Data Factory does not have a programming SDK but it has automation through PowerShell without involving third-party components whereas SSIS has a programming SDK, automation through BIML and third-party components.