How to load data in different servers

Question

I am designing an ETL project on SSIS and I want it to be dynamic. I will use this project for many customers therefore I will query these extractions against different servers.

For example, I have this query in a step with "execute SQL task" component :

        INSERT DataWarehouse.schema.fact1

        SELECT  *
        FROM    Database.schema.table1

My datawarehouse is always in localhost But "Database.schema.table1" could be in different servers therefore I will have Different linkservers in our customer's servers to retrieve its data.

This means for example I will need the query change like this for customer1 :

        INSERT DataWarehouse.schema.fact1

        SELECT  *
        FROM    [192.168.1.100].Database.schema.table1

And for customer2 I will need the query to be like this :

        INSERT DataWarehouse.schema.fact1

        SELECT  *
        FROM    [10.2.5.100].Database.schema.table1

I've tried extract and loading with SSIS components but because of my complex queries, It became so messy.

Any ideas how to make my query dynamic?

Answer 1

As per this link Changing Properties of a Linked Server in SQL Server

One way to solve your problem is to make sure that the linked server logical name is always the same, regardless of what the actual physical host is.

So the process here would be:

1. Create the linked server with the linked server wizard
2. Use this to rename the server to a consistent name that can be used in your code

i.e.

EXEC master.dbo.sp_serveroption 
@server=N'192.168.1.100', 
@optname=N'name', 
@optvalue=N'ALinkedServer'

Now you can refer to ALinkedServer in your code

A better way is to script the linked server creation properly - don't use the SSMS wizard

Here's the template - you need to do more research to fund out the correct values here

USE master;
GO
EXEC sp_addlinkedserver
   @server = 'ConsistentServerName',
   @srvproduct = 'product name',
   @provider = 'provider name',
   @datasrc = 'ActualPhysicalServerName',
   @location = 'location',
   @provstr = 'provider string',
   @catalog = 'catalog';
GO

But the last word is: Don't use linked servers. Use SSIS