How do I change the server name destination dynamically on a SSIS?

Question

Hi, I have a SSIS that should be execute since the server and invoke since a client. The SSIS copies the data from the server to the client, but the client's name can change, so how can I change the server name destination with the name of the client that invokes the SSIS?

Thanks!!!

Answer 1

You can configure your SSIS package to read variables from various sources such as environment variables, XML files, the system registry or a SQL database table. Check out package configurations here. If you setup your SSIS package to read the client name from a configuration variable instead of hardcoding it in the package you should be able to change it at runtime.

This link has some information on how to read SSIS configuration from an XML configuration file.

Answer 2

Store the server name in a variable and have your connection strings built from an expression rather than stored statically.

The first response to the question on this forum post details the steps you'll need to take.

Answer 3

I use a script task and then dynamically change the connection string by getting it from a variable

For example

Dts.Connections("FlatFileCSV").ConnectionString = SomeVariable

In your case the name of the connectionstring won't be FlatFileCSV but something else