Logical method of using SSIS to transform data and upload it to Azure Data Warehouse

2
votes

My current model looks like this:

  1. Gather disparate data sources and import into SQL Server.
  2. Process and transform data using SSIS packages.
  3. final step in the SSIS package uploads data to the data warehouse.
  4. BI tools pull data from the data warehouse for end users.

Is this a logical work flow? I initially was going to use data factory and the Azure SSIS integration runtime to process data. However I didn't understand why these steps were needed, as it would seem simpler in my situation just to build my SSIS packages on premises and upload the processed data to my data warehouse. What benefits would I gain from using data factory and the integration runtime? My main concern is that my current model will make automation difficult but I'm not entirely sure. Any help is appreciated.

1
sql-serverazuressisazure-data-factory

1 Answers

1
votes

Your possible paths here would be SSIS on prem, SSIS on VM in Cloud, SSIS in ADF or natively build the pipelines in ADF.

ADF is an Azure Cloud PaaS managed service for data movement and data integration orchestration. To reach back into on-prem data sources, you need to use an Integration Runtime gateway on the source side. So, if you are looking to move to a Cloud-first architecture or migrating into Azure, ADF is a good solution (use V2).

If you are remaining all on-prem SSIS on-prem is the best scenario.

If this is hybrid, where you will continue to have some data on prem and load Azure Data Warehouse in the Cloud, then you can still use SSIS on prem with connectors into ADW as the target. Or if you have to eliminate the local server concept, you can run that SSIS in a VM in Azure.

If you want to eliminate both the datacenter server and the need to patch, maintain, etc. the SSIS server, then use SSIS in ADF, which provides SSIS as a Service. In that case, you can still move data in a hybrid manner.

It really is going to depend on factors such as are you comfortable more in Visual Studio to develop SSIS jobs or do you want to build the pipelines in JSON in ADF? Do you have a plan or a need to move to Cloud? Do you want to move to a Cloud-Managed service (i.e. ADF V2)?

I hope that helps!!