Problem I have seen many of the changes with SQL Server 2005 Integration Services (SSIS) versus SQL Server 2000 Data Transformation Services (DTS). Integration Services certainly has much more functionality out of the box than DTS and I am learning SSIS as my projects move forward. One item that has seemed to be a thorn in my side is deploying an SSIS package. So, I have read your tip (SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package) related to deploying a package and wanted to find out if any other options are available? Can you shed some light on the situation?
Solution You are right about SSIS vs. DTS. SSIS certainly has a great deal of functionality out of the box as compared to DTS, but in some respects some of the simpler aspects of DTS Packages have been over shadowed by a standardized development platform (Business Intelligence Management Studio) with SSIS. The net result is for the sake of new technology, new processes are needed. One of those items could be considered the deployment of an SSIS Package with the deployment manifest wizard. In the SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package tip we talked about creating a deployment manifest file and using that file for deployment purposes. Let's also outline another approach in this tip which is using Management Studio to import or export an SSIS Package.
Build the package in Business Intelligence Management Studio (BIDS)
Change the package configurations to build the deployment utility
Build the deployment directory with all of the needed files
Execute the deployment manifest file which launches the wizard
In the course of the wizard, deploy the SSIS Package to either the file system or the MSDB database
Execute the SSIS Package via the DTExec utility, a SQL Server Agent Job or a script
As you can tell, this is a much different process as compared to the SQL Server 2000 DTS Packages where all of the development and deployment was directly in Enterprise Manager.
Importing an SSIS Package in Management Studio
As compared to the process above, importing an SSIS package via Management Studio may be considered a much simpler approach. Let's walk through the process of importing the SSIS Package via Management Studio once the package is saved in BIDS.
Import a SQL Server Integration Services Package in Management Studio
Import Process - In order to start the SSIS import process follow these steps:
Open Management Studio
Login to the SQL Server Integration Services instance where you want to import the SSIS Package
Expand the 'Stored Packages' folder
To access the 'Import Packages...' option, right click on either:
The 'File System' folder
The 'MSDB' folder
An individual SSIS Package
Once the SSIS Import Package interface opens, complete the options
Import Package Options - Below outlines the interface options:
SQL Server - MSDB database
File System - Directory with the SSIS Package (*.dtsx file)
SSIS Package Store - Directories related to the SSIS installation i.e. C:\Program Files\Microsoft SQL Server\90\DTS\Packages\
Although the Management Studio and Business Intelligence Development Studio offer rich interfaces to manage SSIS Packages, Microsoft also offers the option to copy, move, delete, or verify the existence of an SSIS Package with the dtutil command. This alternative may prove priceless if you are faced with a situation where you need to manage large numbers of SSIS Packages in an automated manner. Here are some simple coding examples with the dtutil command:
Copy and rename the SampleSSISPackage to Export_FlatFile_Daily_CustomerData.dtsx
Delete the SSIS Package named 'SampleSSISPackage' in the MSDB database
dtutil /SQL SampleSSISPackage /DELETE
Verify the SSIS Package named 'SampleSSISPackage' in the MSDB database
dtutil /SQL SampleSSISPackage /EXISTS
The sample code in this tip for the dtutil command is only the tip of the iceberg. For additional information about the dtutil command visit - dtutil Utility.
With all of the change between DTS and SSIS, be sure to have a standardized deployment process with a consistent location, naming standard, etc. If not, the SSIS Packages can quickly become unwieldy whether they are stored on the file system or in the MSDB database directory structure.
Make sure the deployment is also included in your standards. You do not want to have SSIS Packages living all over your SQL Servers as well as reading and writing from numerous locations in the file system. Check out the functionality that you need and build a set of standards.
Since a four primary options (deployment manifest file, Management Studio, BIDS, command line interface) are available, be sure to test out each of these processes and see which one best meets your needs.