SSIS Package Handling - SQL Server 2008

After you’ve completed your SSIS package, you can easily deploy and install it with SSIS wizards. You can also schedule SQL Server Agent jobs to execute SSIS packages. And if you have SQL Server 2000 packages, you may be able to use the Data Transformation Services Migration Wizard to map their tasks to the equivalent tasks in SSIS.

The SSIS Package Deployment Utility

BIDS creates a package deployment utility that you can use to deploy your packages to any computer. After building the package deployment utility, you can use the DTSInstall.EXE executable (created in the folder of your deployed project) to install packages using the SSIS Package Installer Wizard, which guides you through the process of installing files on either the file system or SQL Server.

The following steps will create a simple deployment utility for a completed SSIS package:

  1. In the Solution Explorer window of an open SSIS project, right-click the project and select Properties.
  2. In the Configuration Properties window, select Deployment Utility. In the Properties window, change the CreateDeploymentUtility property to True. Click the OK button.
  3. Right-click the project in Solution Explorer and select Build. This process will create the SSIS deployment manifest file under the bin\Deployment directory, with its name based on the deployed project.
  4. Double-click the SSIS deployment manifest file (e.g., BookSalesImport.SSISDeploymentManifest)
    to launch the SSIS Package Installer, where you are able to deploy your package(s) to the file system or to a SQL Server instance.

Migrating SQL Server 2000 DTS Packages

SQL Server 2000 packages can be migrated by using the Data Transformation Services Migration Wizard. The wizard maps tasks within the SQL Server 2000 package to their equivalent DTS tasks in SSIS. For those tasks that do not map to SSIS, the new package structure will encapsulate non-SSIScompliant functionality within an Execute DTS 2000 task.

To launch the Migration Wizard, in BIDS, within an SSIS project, right-click the SSIS packages folder in the Solution Explorer window and select Migrate DTS 2000 Package. The wizard will step you through the migration process.

It is also possible to run DTS packages within the SQL Server 2008 environment as legacy components.
This ensures that the conversion process of a large number of DTS packages does not disrupt any plans to migrate to SQL Server 2008. There is also a DTS package editor, contained in the SQL Server Feature Pack available from Microsoft.com, which you can use to edit legacy DTS packages.

Scheduling an SSIS Package

SSIS packages can be scheduled using SQL Server Agent jobs in SQL Server Management Studio. Jobs can be scheduled to execute SSIS packages stored in SQL Server, on the file system, or in the SSIS store.

This exercise shows you how to schedule an SSIS package that is stored within SQL Server (in the msdb database).

  1. In SQL Server Management Studio, expand the SQL Server Agent node and Jobs folder in Object Explorer.
  2. Right-click the Jobs folder and select New Job.
  3. Enter the name and owner of the job.
  4. Select the Steps page from the “Select a page” pane on the left.
  5. Click the New button to create a new step.
  6. Enter the step name and select the type of SSIS package from the drop-down list.
  7. Under the Package Source area, keep the default of SQL Server. Under the Server area, type in or select from the drop-down the SQL Server instance name.
  8. In the Package field, type the package name or select it by clicking the ellipsis button and selecting the name from the Select an SSIS Package dialog box. Then click OK.
  9. Select the Schedules page from the “Select a page” window. Click the New button to define your job schedule, and then click OK.
  10. Click OK in the Steps dialog box, and click OK again in the main New Job dialog box. Many people don’t actually use the SQL Server Agent for scheduling database activities. Instead, they prefer to use other scheduling tools. Typically, integration with these enterprise scheduling tools is accomplished through command-line utilities and batch files. SSIS provides full support for running packages from the command line. After working through the preceding example, you can view the Command Line tab within the job step that actually runs the SSIS package. This will give the exact syntax required to run the package with all the appropriate configuration settings.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics