Deployment Options


You can deploy packages either to the file system on the server or into the SQL Server database engine. The SQL Server deployment option writes the package content into the MSDB system database. This system database, which exists on every SQL Server, is also used to manage important system objects such as agent jobs, tasks, and backup information. The SQL Server deployment option is the most secure because everything is written to the database catalog rather than to the file system. Packages can be recovered by simply backing up and restoring the MSDB database.

Push Deployment

You can push a package to a server by running the Package Installation Wizard (either manually or by opening an .SSISDeploymentManifest file). You can also push a package from SQL Server to another server or to a .dtsx file from within SSMS. A convenient right-click menu option is available in SSMS to import or export a package between servers or package definition files. To push a package to another location, from Integration Services, under the File System or MSDB folder, simply right-click the package and select Export Package.

You have the option to export a deployed package to another SQL Server or to a package file in the file system. You can use the Import Package utility to set the same configuration options as in the BIDS package designer, setting the protection level and a package password.

Pull Deployment

You can also deploy a package from the server in a pull scenario by importing a package within SSMS. In the Stored Packages folder, right-click the server destination (either File System or MSDB) and select Import Package to launch the Import Package utility.

The Import Package dialog box options are nearly identical to those in the Export Package dialog box. You can import a deployed package from another SQL server or from a package file in the file system. Like the export option, you can also set the protection level and a package password.

Managing Packages on the SSIS Server

In the previous exercise, you deployed a package to the database server. In the next exercise, you will redeploy the package to the file system and to the MSDB database. You will execute a package from the file system and then monitor running packages.

Import the ImportCustomers Package to the File System
  1. In SSMS, verify that you are connected to Integration Services. If not, click the Connect button in the Object Explorer pane, select Integration Services, accept the default local server connection, and then click Connect.

  2. Expand the Stored Packages folder, right-click File System, and choose Import Package.

  3. Set the following properties:

    • Package Location: File System

    • Package Path: C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap10\ImportCustomers\Mod10_Project.dtsx

  4. Click in the Package Name text box. This sets this property to the designer package name.

  5. Click the ellipsis button next to Protection Level. Note the value previously set in the package design. Leave this setting and click the Cancel button to close this dialog box.

  6. Click OK.

  7. Type the password password when prompted.

  8. Click OK.

    Tip 

    When you import to the File System option, Integration Services stores the package file in a known location on the server (in the \Microsoft SQL Server\90\DTS\Packages folder). You can then access the package without having to know the specific file location.

Import to MSDB
  1. Right-click MSDB and repeat the preceding steps to import the same package to the MSDB system database but, this time, choose SQL Server For Package Location in the Import Package dialog box.

    Note 

    If the package is already deployed, you will be prompted to overwrite it. Answer Yes if prompted.

  2. Complete the steps and note the new package listed on the tree.

Export a Deployed Package
  1. Find the ImportCustomers package in the SSMS Object Explorer under Stored Packages\MSDB.

  2. Right-click the package icon and choose Export Package.

  3. In the Export Package dialog box, select File System for Package Location.

  4. Click the Package Path ellipsis button.

  5. In the Save Package To File dialog box, select Desktop and leave all other defaults to save the package, using the default file name ImportCustomer.dtsx.

  6. Click Save to save the package file, and then click OK to save and close the Export Package dialog box.

Execute the File System Package
  1. View the computer's desktop and locate the image from book ImportCustomers.dtsx file.

  2. Double-click the file to open Execute Package Utility.

  3. Execute the package and view the execution progress. Depending on the speed of your computer, this might take only a few seconds or several minutes to complete. When completed, close the Package Execution Progress dialog box, and then close the Execute Package Utility dialog box.

Monitor Running Packages
  1. Repeat the preceding steps with SSMS and Package Execution Utility open in side-by-side windows.

  2. Start the package, and then open the Running Packages folder. If necessary, right-click and choose refresh to see the name of the running package.

    Note 

    The speed of your computer will determine how long this package runs. If the package finishes running before you have a chance to complete the preceding steps, you can extend the running time by creating duplicate copies of the input data files in the project's \Data subfolder. In Windows Explorer, select all of the data files; use the right-click menu to copy and paste options to create duplicate files. Repeat this step if necessary. There is no need to change the default file names.

  3. Click the package name on the tree to view the summary page for the package.

  4. Click the Report button on the Summary page toolbar.

    Note 

    Selecting the Report option in this view runs a Reporting Services report with summary and detail information about the current package execution.

    Note 

    Keep in mind that SSMS shows only the currently running packages. Some packages can complete in less than a second, and you might not be able to monitor these packages.

  5. Close the Execute Package Utility dialog box when completed.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net