Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Deploy SQL 2000 DTS Package vs SQL Server 2005 SSIS Package


By:   |   Read Comments   |   Related Tips: More > Integration Services Upgrade

Attend these FREE MSSQLTips webcasts >> click to register


Problem
In SQL Server 7.0 and 2000 DTS was one of those freebies that did not catch on immediately, but when it did, the industry flooded to use the technology.  As SQL Server 2005 was rolled out, one of the first items many SQL Server Professionals checked out was SSIS due to the reliance on DTS Packages in their SQL Server 2000 environment.  Once their eyes rolled back into their heads from seeing Management Studio as a replacement for Enterprise Manager and Query Analyzer, they popped back out when reviewing the Business Intelligence Development Studio to build SSIS Packages among other sets of solutions (i.e. Reporting Services, Analysis Services, etc.) based on the rich feature set.  With the many interface and development differences between DTS and SSIS, do not be surprised by the deployment differences either.  In this tip we will outline the steps used to migrate DTS Packages and the steps necessary to migrate SSIS Packages. 

Solution
SQL Server 2000 - DTS Deployment

The process to develop and deploy DTS Packages for the most part is handled within Enterprise Manager as the steps below outline. 

ID Directions Screen Shot
1 Development - Develop your application in Enterprise Manager's DTS Designer.  In this interface the connection and task objects can be dragged and dropped from the tool bar to the design space.  Then the objects can be double clicked on for applying the code and configurations.

 

 

2 Save - If you want to save the DTS Package locally it is possible to do so by using the File menu 'Save' or 'Save As' option.  In this interface the following configurations can be made:
  • Package Name - Logical name of the package
  • Owner Password - Needed for coding modifications
  • User Password - Need to execute the package
  • Location - Format of the DTS Package
    • SQL Server stored in the MSDB database
    • Structured Storage file (compiled) stored in the file system (*.dts extension)
    • Visual Basic file (clear text VB file) stored in the file system (*.bas)
  • Server - SQL Server name
  • Authentication - Windows Authentication or SQL Server user and password to save the package
  • *** Note *** - With the Location of SQL Server the last two options above used, but with Structured Storage file and Visual Basic file the last 2 options are replaced by a text box to specify the file location.

 

 

3 Deployment - To deploy the DTS Package to another location, the 'Save As' option is available on the File menu with the same three format options listed above.  This option will enable you to save the files to another SQL Server or file system for future execution. 

In this example, the same DTS Package is being saved to the SQLTest01 SQL Server for testing purposes.  Once the testing is completed, the same process can be followed to deploy the DTS Package to the production environment.

 

 

4 Execution - DTS Packages can be executed interactively or on a regularly scheduled basis.  Below are the options to execute a DTS Package interactively:
  • Execute DTS's command line utility to provide parameters and run the DTS Package via a user interface
  • Press the green 'play' button on the tool bar to execute the DTS Package
  • In Enterprise Manager, right click on the DTS Package and select the 'Execute Package' option

Below are the options to execute the DTS Package in an automated manner:

  • Call the dtsrun.exe application via a Job
  • Call the dtsrun.exe application via xp_cmdshell in a stored procedure

 

 

SQL Server 2005 - SSIS Deployment

With SSIS functionally the same general steps are followed, but the detailed process is much different.  For example, development is completed in a separate tool from the deployment.

ID Directions Screen Shot
1 Creation- Develop your application in the Business Intelligence Management Studio (BIDS).  Create a SQL Server Integration Services Project to build 1 or many SSIS packages for the Solution.

 

 

2 Development - Develop the SSIS Package with the BIDS tool set to implement the business and error handling logic.

 

3 Save - In BIDS, all of the files supporting the Solution should be saved to the local file system when the Project was setup.

Notice - With SSIS during the development save process that the code cannot reside in SQL Server as was the case with DTS.

 

4a Deployment - In the Solution Explorer, right click on the name of the solution  (i.e. SSIS_Test1) and select the 'Properties' option.  Once the 'Properties Pages' loads, select the 'Deployment Utility' option in the left pane.  Change the following options:
  • AllowConfigurationChanges = True
  • CreateDeploymentUtility = True
  • DeploymentOutputPath = Specify a child directory to your current Solution i.e. bin\Deployment

Press the 'OK' button to proceed.
 

4b Deployment - In the Solution Window, right click on the name of the solution and select the 'Build' option.

Once this process is completed, BIDS will indicate 'Build succeeded' in the bottom left portion of the screen.


 

4c Deployment - Now the SSIS Package will reside in the parameter specified for the DeploymentOutputPath in step 4a (i.e. bin\Deployment directory) as a *.dtsx file with a *.SSIS_Test1DeploymentManifest file. 


 

4d Deployment - Copy the 'deployment' directory and the files to a location on server where you would like to deploy the files (i.e. C:\Program Files\ Microsoft SQL Server\MSSQL.2\MSSQL\Backup.)


 

4e Welcome Screen - Double click on the *.SSIS_Test1DeploymentManifest to start the Package Installation Wizard which deploys the SSIS Package.  Click the 'Next' button to proceed in the wizard.


 

4f Deploy SSIS Packages - Select the file system or SQL Server deployment option which indicates where the SSIS Package is stored. 

In this example, we are selecting the file system deployment.

Click the 'Next' button to proceed in the wizard.


 
4g Select Installation Folder - Select the directory where you want to save the SSIS Package in the file system.

Click the 'Next' button to proceed in the wizard.


 
4h Confirm Installation - Click the 'Next' button to proceed in the wizard.
 
4i Finish the Package Installation Wizard  - Click the 'Finish' button to complete the wizard.


 

4j Deployment Verification - To verify that the SSIS Package was deployed, navigate to the directory selected in step 4g.

4k Deployment - In Management Studio, login to the Integration Services instance where the SSIS Package was deployed.  Then navigate to the root | Stored Packages | File System folder to verify the package is available to Integration Services.


 

5 Execution - SSIS Packages can be executed interactively or on a regularly scheduled basis.  Below are the options to execute a SSIS Package interactively:
  • Press the 'play' button on the tool bar in BIDS
  • In Management Studio, login to the Integration Services instance, navigate to the SSIS Package, right click on the SSIS Package and select the 'Run Package' option then press the 'Execute' button once all of the options have been configured

Below are the options to execute the SSIS Package in an automated manner

Next Steps

  • Since SSIS (SQL Server 2005) is able to access SQL Server 2000 instances and SSIS has an expanded set of functionality, consider fulfilling new ETL requirements with SSIS as a means to begin to learn about SQL Server 2005, if you have the licensing.  This gradually will expose you to SQL Server 2005, offer you a more robust feature set and avoid the need to have to upgrade DTS Packages to SSIS Packages. 
  • As you begin to transition ETL code from SQL Server 2000 to 2005, keep in mind the differences in the code migration process.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools