SSIS Connection Manager Properties

The SSIS packages stored in SSIS Catalog (introduced in SQL Server 2012, and also SQL Server 2014) allow configuration changes made on the project or the package connection managers, depending how the connection was setup. It is important to have a good understanding of how the values in these properties are being used.

I can't find any documentation on how these SSIS connection manager properties affect the actual connection value. In this post, Here is my observation on how these properties changes affect the actual value used during the execution.

Here is the SSIS connection manager in the designer (in this case Visual Studio with BI)

This is the connection manager property setting on the deployed package in SQL Server SSIS Catalogs.

It seems the ConnectionString value is generated based on the setting configured at the designer. Other properties (eg. ServerName and InitialCatalog) values appears to be similarly mapped, but not all the property like integrated security as SSPI.

Looking at this example, the connection string property, along with other properties appear to be serving the similar purpose. Let's look at the detail. Click the '...' on the right of the property show the different options,

1) Using the default value from package (not editable)
2) Edit value. new value could be entered here. This value is stored in the SQL Server.
3) Use environment value. If environment have been setup for the project, the value of the property could be mapped to the value setup in the environment value.

Let's perform a few testings to see how changing these properties affect the connection values being used during execution.

Preparation

I designed a simple SSIS package that obtain the server, database and login information, and store them in a table for examination.

For the Get Session Info task, it query the connection server name, database name and the login.

Store Session Info task pretty much store the information into a table.

Once the package is ready, it is deployed to the SSIS Catalogs for testing

First Test
Change the ServerName value to TestServer, the ServerName value font is in bold. Notice that the Data Source value in the connection string still Server1.

Execution shows the server used is TestServer. Similar test shows that execution pick up the edited InitialCatalog property (database) value as well.

Second Test
With ServerName property value edited as TestServer, now we change the Data Source value of the ConnectionString value to ProdServer.

Execution shows the server used is TestServer. In this case, it seems like the ServerName property edited value takes precedence.

Third Test
The original (unedited) connection string is this,
Data Source=Server1;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Notice the connection string has the SSPI specified.

Change the UserName property value to TestUser and add a Password to the Password property.

Executions shows the SSPI is used. In this case, seems like the SSPI take precedence.

Fourth test
Remove the SSPI, add user and password on the connection string,
Data Source=Server1;Initial Catalog=master;Provider=SQLNCLI11.1;User Name=User1; Password = CrazyPassword; Auto Translate=False;

Execution shows TestUser from User property and its Password value (CrazyPassword) are being used. These two properties take precedence when there is no SSPI specified in the connection string property.

Observation
It appears that the edited Server, InitialCatalog, UserName and Passoword property values take precedence over the values in ConnectionString. However, SSPI takes precedence wherever it is specified in the connection regardless of the value of the UserName and Password edited value specified in UserName and Password property.

Based on this observation, if these properties value are different from the designer value (default value), we could change the ConnectionString property value to reflect the change, or we could also change these value directly and possibly clearer on the respective property. When I say directly, I mean changing a specific property (eg. only the server value), without changing the entire connection string. Apparently this is when SSPI is not specified.

This also applicable when using environment variable. If we previously have an environment setup with its variables defined.

We could assign the ServerName property to use the environment value. Notice the property value now is underlined.

This is often the case when developer design the SSIS with connection pointing to developer database servers (sometimes different database as well), and these properties need to be modified when the packages are deployed into production environment that are to be pointed to production servers and databases, with different user account credential. With this clarification, we know where and how to change the connection configuration correctly and efficiently.