Clash Royale CLAN TAG#URR8PPP

How to load data in different servers

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?

EXEC master.dbo.sp_serveroption

3 Answers
3

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:

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

ALinkedServer

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

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.

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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.