Happy New Year folks.
Gail, Hope you are well. It was nice meeting you in UK, 7 months back at InsideSQL.
At the moment this is a POC but hoping to work it out in favour of project, trying to evangelize Azure and Modern way of building data projects/estates.
Below is the approach I'm engaging now to help a client.Solution 1:
Using Data Migration Utility to migrate Schema and Data into the Azure SQL Data Warehouse. Then onward continue using the existing SSIS packages to start loading the data.
In this approach there will be performance hit into the loading data warehouse but also there will be lot of time spent in clearing the incompatible issues of the Schema and Data Migration. There will work involved in updating the target configuration settings in the SSIS packages.
Creating the new data routines using AZCopy to load data into Azure blob and then load data into the Data Warehouse using the Polybase.
Performance is good in this method and also its a modern approach to building data warehouse. This approach will have to involve creation of new data routines and the option to resuse existing ssis packages might be hindered.
Redgate is an option I could use but there is cost involved here I assume and I'm sure my client might not be interested on this but I will have a quite word and see.