CODEWITHSUNDEEP
sql-serverssis
riksof-zeeshan
riksof-zeeshan

Reputation: 531

SSIS Change datasource in connections

I'm new to SSIS and .net environment, I need to migrate one of the connection to new database server, I have tried by right click on connection and edit the details in dialog testing the connection results into successful notification popup message but when I click ok and review the connection it still the same as it was in the beginning.

I'm trying to change the server name but it never get change

enter image description here

Upvotes: 1

Views: 1085

Answers (2)

LONG
LONG

Reputation: 4610

The connection name does not matter but the actual connection does. The connection name will remain the same before you rename it and it will be the name as the first server that you connected to. For example, if you see A as connection name, when you check the connection, it is B, it is still connecting to B if you do not want to rename the connection manager name to A.

UPDATE

if you see the Server name inside the connection manager remains the same even if you tried to change it, that is most likely the connection string was set to dynamic expression not the static one, check the Expression of that connection in the properties page, see whether there is a expression specified for Connection string

2nd UPDATE If it has expression for Connection string, you have two ways to change to a new connection.

(1) Define a new connection string expression for Connection string in the Expression page.

(2) Remove expression, then the connection manager will not evaluate the connection as what it shows in the expression, after then you could repeat what you did before to specify a connection.

UPDATE after OP accepted it as an answer

I suggested op first try to remove the expression for connection and specify as a new like what he did before, and it shows the correct result.

For the connection string expression, if it does not have any parameter involved in deciding the final connection string, there is no need to specify a pure string to it, only when the connection string need to be a dynamic one, it is necessary to use expression to get the target server name.

Upvotes: 1

Jens
Jens

Reputation: 3299

You are currently changing the result of an expression. That's why the original value is back every time you reopen your connection manager. Instead, you should change your parameters.

To find out what parameters your need to set, look into the code of your connection: enter image description here

You'll find the parameters mentioned in there: enter image description here

Then just go ahead and change those parameter values in your basic parameters window and you're good to go.

Upvotes: 1

Related Questions