Deploying and Configuring SQL Server Integration Services Packages Without the Wizard
There are three variables in the configuration file. The three variables are the Connection String, Server Name, and Initial Catalog. Changing the Server Name and Data Source parameters within the Connection String will change the server package refers to, and changing the Initial Catalog will refer to the database that the package should refer to. Changing these values has the same exact effect of changing the values directly in the Step or Execute Package Utility window. The only exception is that only a single change is needed to affect all packages that are referring to the configuration file.
All references to a configuration file can be done in the Step and Execute Package Utility windows. Select a configuration file under the Configurations tab of the respective window and it is complete. Please note that if a configuration file is selected, it will override all other configurations done individually to the package.
Although a deployment wizard is provided by the Business Intelligence Development Studio, there are other methods to deploy the SQL Server Integration Services package more intuitively as described in this article. Packages can be loaded, configured, and processed without a single use of the deployment wizard. In addition, the deployment wizard only takes the user as far as loading the packages into SQL Server. It is really not a "deployment wizard" per se because it does not take you through the entire process of processing the packages. However, as mentioned earlier, this article is not meant to derail users from using the wizard. The deployment wizard provides the greatest value when trying to update packages after they have been loaded into SQL Server's file system. It removes the hassle of having to re-import the updated packages into SSFS because the wizard will complete the task for you. Hopefully, this article has helped those who are new to SSIS deployment understand the entire process better.
Download the Code
You can download the code that accompanies this article here.
About the Author
Dee Quang is a senior consultant with Crowe Horwath LLP in the Oakbrook office. He can be reached at 630.990.4405 or firstname.lastname@example.org.
Page 6 of 6