ApplicationName in project connection manager

Question

We have a large number of packages within an SSIS project and these make use of project connection managers. I would like to change the ApplicationName property of the connection string to reflect which package it is coming from as this will be useful when running sp_who2 on the SQL Server to see which package is associated with a connection.

Is there a way to do this? I can only find project variables being accessible when configuring the project connection manager - seems reasonable - but is there a way around this when the connection is being established?

Thanks.

Answer 1

Hi @Peter Bishop,

You can try to use an Expression for a connection to adjust ApplicationName property value on the fly.

A couple of SSIS system variables are good candidates to be used:

• PackageID, The unique identifier of the package.
• PackageName, The name of the package.

https://learn.microsoft.com/en-us/sql/integration-services/system-variables?view=sql-server-ver16

Answer 2

Normally you define the CM at the project level. Then within a package you add a "reference" to the CM. Within this reference you can parameterize the connection string. So this is doable at the package level.

But what I might start with is setting your project CM's connection string to have application name portion of the string use the PackageName system variable. I'm not sure it'll pass validation at the project level. If that doesn't work then you'll need to instead make that change inside the package's CM properties.