Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in Azure by (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

0 votes
by (47.2k points)
  • 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.

Browse Categories

...