January 27, 2021
Hot Topics:

Deploying and Configuring SQL Server Integration Services Packages Without the Wizard

  • By Dee Quang
  • Send Email »
  • More Articles »

Step & Execute Package Utility Configuration

After packages are loaded into SQL Server's file system (SSFS), reconfiguring the packages can be a hassle when using the default package configuration. However, there are alternative methods to configure the packages either individually or globally after the packages are loaded into SSFS.

Configuring the packages individually can be done by using the Step and Execute Package Utility Configuration window. In the Step windows of a SQL Server job, navigate to the Data Sources tab to see all the data sources of the package's Connection Manager. Clicking on the check box as seen in Figure 13 will allow the user to edit the connection string.

Click here for a larger image.

Figure 13: Step Properties Configurations

Within a connection string, there are many variables representing a login credential. The following table describes the important variables of the connection string.

Variable Description
Data Source/Server Name Simply the name of the server
Initial Catalog The database of the server

Table 1: Connection String Important Variables

Simply changing the value of the Data Source or Initial Catalog will be like changing the data source connection on the default package configuration screen within a package. The rest of the connection string represents the authentication mode that unfortunately will not be explained in this article.

Configuring the packages individually by using the Execute Package Utility window is very similar to configuring the package by using the Step window. In the Execute Package Utility window, navigate to the Connections Manager tab and check the appropriate boxes to make the data connection changes. As seen in Figure 14, the options are very similar to Figure 12.

Click here for a larger image.

Figure 14: Execute Package Utility Connection Manager

Packages also can be configured globally by the use of configuration files. The notion of global configuration means that once a single change takes place in the configuration file, all packages that are pointing to that configuration file for the data source connection will be affected. Configuration files are created by choosing the Package Configurations... option from the right-click menu within the package's control flow. Unfortunately, this article will not get into the details of creating a configuration file. Configuration files can encompass the entire package and can get very complex. This article will only cover the aspect of the configuration file that is necessary to make a change to the data connection. Figure 15 represents the important variables of a data source connection in XML format.

Click here for a larger image.

Figure 15: XML Configuration File

Page 5 of 6

This article was originally published on October 2, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date