I'm not sure if you covered a star-schema or dimensional model design in your studies or not. It might be helpful to read up a bit on dimension and fact tables...

Thanks everyone for the feedback and constructive comments.

Hi Julian, I was trying to focus more on the basic principals of how sql statements are created dynamically.
But you make a very good point, I should probably have nudged...

From a dimensional modelling and data warehouse design aspect, I'd read up as much as possible about the Kimball methodology:
https://www.kimballgroup.com/
http://www.sqlservercentral.com/blogs/microsoft-business-intelligence-and-data-warehousing/2012/04/08/the-kimball-approach/

Tutorial for SSAS:

I think one of the closest end to end examples you're going to find is probably the AdventureWorks databases and cubes that can be downloaded from Microsoft.
A lot of...

I'm guessing this is what you are looking for:

WITH
Duplicates
AS
(
    SELECT    [Policy]
    FROM    Insurance
    GROUP BY [Policy]
    HAVING COUNT(ID) > 1
)

SELECT    I.ID, I.[Policy]

It could have something to do with the default schema assigned to the user you are executing the code with.

Have a look at this article:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/163434/

You’ll have to modify the package a bit to execute the SQL files instead of importing the data but the principle of running the...

What do you mean exactly by "execution of all SQL"?
Do you have SQL script files (.sql) sitting in the folder?

SSRS continues to make use of web services:
https://docs.microsoft.com/en-us/sql/reporting-services/report-server-web-service/report-server-web-service

You'll just have to check if any of the method names have changed or deprecated in...

SSRS has many web service methods which can be used.
Have a look at these examples:
https://weblogs.asp.net/srkirkland/exporting-a-sql-server-reporting-services-2005-report-directly-to-pdf-or-excel
https://forums.asp.net/t/1549431.aspx?Code+to+render+the+report+in+PDF+format+using+C+in+SSRS+2008
There are loads more on the internet.

What is the purpose of writing the data from your new central database back to the individual Access databases?

If you are not interested in recording any history (changes...

Hi Huw
I think the first thing to take into consideration is, do you want to be able to store any of the changes to those customers?
ie. If I...

Are you referring to your dimTablename or fctTablename (dimensional model) designed tables as staging?
I have always considered the "Extract" tables as part of the staging layer. Yes transformations can...

Check out this link: https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html