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?

The simple answer is to give your linked server the same logical name no matter what the remote server is. However you should avoid linked servers. SSIS is better for this. Keep in mind that you can rarely load a source system table directly into a fact. Some transformation is always required, including mapping surrogate keys — Nick.Mc, Aug 05 '18 at 06:45
@Nick.McDermaid Thanks for your comment, But I didn't found any way to change the linkserver's logical name. can you tell me how to do it? And I used these queries for example and in real, I'm not inserting everything from operational table to a fact. Thanks again — Pouya Kamyar, Aug 05 '18 at 06:52
It took me about fifteen seconds to find this https://stackoverflow.com/questions/11866079/changing-properties-of-a-linked-server-in-sql-server which says to use `EXEC master.dbo.sp_serveroption`. Did you google this? — Nick.Mc, Aug 05 '18 at 08:17

score 2 · Accepted Answer · answered Aug 06 '18 at 06:15

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:

Create the linked server with the linked server wizard
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

score 0 · Answer 2 · answered Aug 05 '18 at 07:16

I would suggest you to do the below steps to execute same statement across multiple servers. As suggested by @Nick.McDermaid, I would strongly recommend against linked server. It is better to go for exact server name in SSIS.

Put the INSERT statement into a separate variable
Create a foreach container in SSIS.
Inside foreach containter, have a script task and get the current server name from the list of servernames. You can have comma separated list of servernames and get current one.
Again, inside foreach container, create Execute Process Task & call Sqlcmd.exe with connection information specific to each server, based on the server name got in Step No. 3, using SSIS expressions. Refer to this Stackoverflow post on using expressions for Execute ProcessTask for more information on calling Execute process task in SSIS.

score 0 · Answer 3 · answered Aug 06 '18 at 14:30

How about making a SSIS package that works for one of your systems.
Parameterize your working package to accept a connection string
create another package that loops thru your connection strings and calls your working package and passes the conn string

How to load data in different servers

3 Answers3