SQLErudition.com

The default connection string for a OLEDB connection looks something like this –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoOLEDBPackage-{9758C970-4AAC-449E-8390-FA7EAC5CD52A}(local).ReportServer;

 

Notice the Application Name parameter in bold font. It has the name of the package, a unique Id for the connection, the SQL server name and the database name. This application name will be exported out to the config file too. Also, this is what you will see in the Program Name column of SP_WHO2 results when the package is running.

When the developers create the packages on a development server, the connection string includes the development server name. At the time of migrating the package and its config file to the QA and Production environment, the developers usually change the Data Source and Initial Catalog in the connection string but leave the Application Name as it is. Anyone monitoring the Production server with a SP_WHO2 would see a package running with a funny long name including GUID and the development server name. It is certainly not user-friendly.

This can be fixed easily with any one of the following methods.

Method 1 – Properties Window

Right click the OLEDB connection and select Properties (or select the connection and press F4). The Properties window will show up. Edit the Connection String property to change the Application Name parameter of the connection in this window.

Method 2 – Config File

Edit the Connection String property to change the Application Name parameter of the connection in the config file. As we know, the values in the config file override the design time values when the package is running. Although I prefer making the initial change in the package itself before creating the config file.

Method 3 – The Connection Manager Wizard

1. Double click the OLEDB Connection.
2. Click on the All Tab.
3. Edit the Application Name.

Now the connection string would look like the following, which is a lot cleaner than the default string –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoPackage;

 

I prefer using a _ convention based on the target environment e.g. SSIS-DemoPackage_TEST, SSIS-DemoPackage_QA or SSIS-DemoPackage_PROD in the different config files. You can plan to put in other information to make it more meaningful in your context.

I’d love to hear your thoughts on this!