SSIS task: C # Script: how to change the SQL connection string based on the server environment dtsx is running on?

Question

I have a script task where I change the connection string for db, however it does not contain one important piece of information, which is the server on which the package itself is running.

Is there a way to get the server name from a C # script?

I know that in sql I can select @@ servername, but I need a package to check the name of the server it is running on (dtsx) on which the sql server is not located.

I know this is a strange request, but there are many options, here are the ones that I am currently studying:

+Through A Batch CMD whose results i could store in a pkg level variable +Through ac# script +SQL if it is possible 

any other ways anyone knows about would be greatly appreciated.

EDIT / UPDATE: I found several ways to do this:

 C#: public string host = System.Environment.MachineName.ToString(); cmd: hostname ...-->then store in pkg variable 

Answer 1

Although you can use .NET to get the server name, the traditional way of SSIS is to use the previously existing package variable "System :: MachineName" (as a note, note that you may need to click "Show System" Variables "to see it in package Variables window.) Assuming that SSIS 2008 and C # (2005 / VB provide the same variable):

1) Add the variable name "System :: MachineName" (without quotation marks) to the task editor script property ReadOnlyVariables

2) Inside the script, you access the variable as follows:

 Dts.Variables["System::MachineName"].Value.ToString() 

Hope this helps. Kristian

Answer 2

Christian has the right decision, uses his own variables and skips the tangled roads that you tried to go down. I didn’t want to make such radical changes as this to their message, but the thought of screenshots will help strengthen the answer.

If you have not created any variables in your package, this is what the default will look like.

Click the gray X and display the available system variables and their current values. Some values ​​are set at creation time - CreatorName, CreatorComputerName; others change as a result of the event (keep VersionBuild automatic increments); while others change as a result of the execution of the package (StartTime, ContainerStartTime, MachineName, etc.)

Finally, note also that some system variables are context sensitive. As you can see from the scope, OnError system variables expose things like ErrorCode, ErrorDescription, etc.

Answer 3

Create a new Script task before SQL Query tasks. Use the Script task to update the ConnectionString like this (if your connection name is "SQLConnection":

 Dts.Connections("SQLConnection").ConnectionString = "Data Source =" + System.Environment.MachineName.ToString() + ";Initial Catalog =myDataBase; Integrated Security =SSPI;"; 

I remember that the user interface was particularly restrictive when and when this variable was set, but I have done this in the past. Let me know if you encounter any problems.

Answer 4

I used to use the environment variable (windows), which my package reads at startup, and get the connection string from it! To achieve this, you can use the package configuration.