Deploying and Configuring SQL Server Integration Services Packages Without the Wizard
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.
|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.
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.
Figure 15: XML Configuration File
Page 5 of 6