Deploying and Configuring SQL Server Integration Services Packages Without the Wizard
A deployment wizard is provided by Visual Studio's Business Intelligence Development Studio (BIDS) to deploy and configure SQL Server Integration Services (SSIS) packages. However, deploying a package with the wizard can be cumbersome and is also a little confusing if you are unfamiliar with the tool. It is not always the most intuitive method to deploy and configure a package.
I will demonstrate deploying and configuring a SSIS package without the deployment wizard provided by BIDS. I will show you how to deploy and configure SSIS packages all intuitively. Please note that this article is not meant to derail developers from using the wizard. It is just a different method to accomplish the same task.
Application and technology wise, this article only pertains to the following software and versions. All other versions may have slight modifications to the instructions:
- Microsoft SQL Server 2005
- Microsoft Visual Studio 2005 v8.0.50727.42
- Microsoft SQL Server 2005 Management Studio v9.00.3042.00
- Microsoft SQL Server 2005 Integration Services v9.00.3042.00
Deploying a SSIS package requires either the SQL Server Job Agent to locate and execute the package or execute the package directly from SQL Server's file system. For optimal performance, the package should be stored on the same system as the deployment server's SQL Server 2005 Management Studio; whether that is the file system itself, SQL Server's file system, or SQL Server's MSDB, it really doesn't matter. It is the same deployment procedure for all three locations with the exception of where to select the package for deployment. For the sake of time, this article will only demonstrate deploying the package by storing it in SQL Server's file system.
Locating, Migrating, and Importing
When initially creating the project, BIDS allowed the developer to select the home directory of the project. Locate the home directory of your project and find all the packages (.dtsx files) in a subfolder directly under the home directory. The subfolder's name should be the exact name as the home directory. Figure 1 displays the structure of the project for the examples of this article.
Figure 1: Package Directory
The home directory of the demo is C:\Deployment_Demo. Within the subfolder C:\Deployment_Demo\Deployment_Demo there are two packages to deploy, called Deployment_Package_Demo_1.dtsx and Deployment_Package_Demo_2.dtsx. As mentioned earlier, deploying these packages at optimal performance requires the package to reside on the deployment server. Copy and migrate all the packages to the server.
Because all the packages should have migrated to the server, the next few steps should all be performed on the server. Import the packages to SQL Server's file system by first connecting to server type Integration Services from the Microsoft SQL Server 2005 Management Studio (SSMS) application. The Integration Services server type does not allow for SQL Server Authentication; only Windows Authentication is allowed. Set the server name to be LocalHost and click Connect. Figure 2 shows the login screen for the Integration Services server type.
Figure 2 Integration Services Login
Once connected, SSMS should display the object explorer with the Integration Services directory structure seen in Figure 3.
Figure 3: Integration Services Object Explorer