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
Loading
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_DemoDeployment_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
Create a new folder under LocalHostStored PackagesFile System to separate the projects and import the packages by right-clicking on the newly created folder. Choose Import Package…when the right-click menu pops up. This should bring up the Import Package screen, as shown in Figure 4.
Figure 4: Package Import
Select File System as the package location and browse for the package’s location. Once the package is selected, click on the package name input box and a suggested name will appear. The name that appears is simply the name of the package. Feel free to change it by overwriting the value in the box. When done, click OK to complete the import. Figure 5 displays how the directory should look like after the packages are successfully imported into SQL Server’s file system. (If you were to use the BIDS deployment wizard, this is as far as it will take you. Therefore, you have essentially performed all the steps that were to be performed by the deployment wizard!)
Figure 5: SQL Server File System
Execution
After all packages are imported, there are two methods to execute the package. Packages can execute by scheduling the package with the SQL Server Job Agent (SSJA) or on an ad-hoc basis by choosing to run it from SQL Server’s file system. All packages can be configured in their respective methods of executing. However, configurations will not be explained until the Configuring Packages section. The packages will currently run off the default package configurations.
SQL Server Job Agent
Packages that need to be processed in a timely manner can be scheduled by the SQL Server Job Agent. To use SSJA, the services need to be enabled. Navigate to the Services folder by going to Start→Settings→Control Panel→Administrative Tools→Servicesand locate SQL Server Agent (MSSQLSERVER). Make sure that the service is enabled. If not, right-click on SQL Server Agent and choose properties. Under the General tab of the Properties window, select Automatic for the startup type and click on the Start button. Figure 6 below displays the Properties window.
Figure 6: SQL Server Agent Properties
After Start is pressed, the SQL Server Agent should now be started. Click OK to close the window and get out of the Services folder.
Job Creation & Execution
Scheduling a package requires that a SQL Server job be created for the package. Load the Database Engine server type and connect using the LocalHost server name with Windows Authentication. After connection, navigate to the bottom of the LocalHost directory and expand the SQL Server Agent folder. Right-click on the Jobs folder and choose New Job… to display the New Job window. as seen in Figure 7.
Figure 7: SQL Server Job Creation
Give the job a name and navigate to the Steps tab on the left side to create a step process. Click on New… and a New Job Step window will appear, as seen in Figure 8.
Figure 8: SSIS Step Creation
Give the step a name and select SQL Server Integration Services Package as the type. Ensure that the value of Run as is SQL Agent Service Account. Now, navigate to the General tab located below the drop downs and select SSIS Package Store as the package source. Input LocalHost as the server and browse for the package in the file system. Select a package and the window should now resemble Figure 8. Click OK to close the window.
Now that the step is created, the package needs to be scheduled. Navigate to the Schedules tab on the left side of the New Job window and click on the New… button to display the New Job Schedule window. As seen in Figure 9, the New Job Schedule window is pretty self explanatory. Set the desired schedule and click OK to exit the scheduler.
Figure 9: SSIS Job Scheduler
Click OK again on the New Job window to create the job. The job should now be created and resemble Figure 10.
Figure 10: Directory after Job Creation
At the scheduled time, the job will process and complete. If the job completes without any errors, give yourself a pat on the back because you have successfully deployed an SSIS package via SSJA.
Ad-Hoc Execution
Whether packages are executed on a scheduled basis or not, packages can always be executed ad-hoc. Executing a package on an ad-hoc basis can be done in two methods. The first method is directly from the package in SQL Server’s file system (SSFS). Find the package in SSFS and choose Run Package from the right-click menu to display the Execute Package Utility window. Click on the Execute button and the ad-hoc execution is complete. Figure 11 displays the Execute Package Utility window.
Figure 11: Execute Package Utility
The second method is by executing it through the job created for the package. Simply right-click on the job and choose Start Job. That is all that is needed to execute the packages ad-hoc.
Configuring Packages
Packages can be configured in a multitude of ways. They also can be configured individually or globally. This article so far has only used the default package configuration to individually configure and execute the package. In addition to the default package configuration method, this section will describe two other methods to configure the package.Default Package Configuration
The default package configuration method uses the package’s connection manager’s OLE DB connection transformation to configure the server credentials. This method is used to configure the packages individually. As seen in Figure 12, the server name and server credential can be entered in the Connection Manager window.
Figure 12: Connection Manager Window
When a package is schedule by the SQL Server Job Agent or executed ad-hoc and configurations are not specified, the default package configuration will be used.
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.
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.
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
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.
Conclusion
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 dee.quang@crowehorwath.com.
