Kiruthik .Com

Deploying & Deleting a SSIS DTS Package in SQL Server 2005

Here we are going to see the steps involved in installing/deploying a SQL Server Integration Services (SSIS) DTS Package.

Deploying a SSIS Package in SQL Server 2005

SQL Server Integration Services Package can be deployed in many ways in SQL Server 2005.  The best way is creating a manifest file, and running that.

1. After creating the  SQL Server Integration Services (SSIS) DTS Package in Visual Studio 2005 goto the project properties, (by right-clicking the project in the solution explorer, Select : Configuration Properties -> Deployment Utility,  in the Right side pane, you have a property named : CreateDeploymentUtility which must be set to True. 

2. After that, build the project, and check for a folder called Deployment inside the projects' Bin folder.

3. All the .DTSX file(s) which was used in the project, along with the manifest file (name will be like : <Project Name>.SSISDeploymentManifest ) will be present in the folder Deployment.

4. To deploy the created package, just double click the Manifest file, which will bring a wizard in which you have to chose the deployment locality (File System Deployment / SQL Server Deployment )which means whether you want to store the DTS Package in a physical path (eg., D:\Package\DTS_Package.dtsx) or in the SQL Server Integration Service itself.  

5. Now the package is ready to Execute!!

6. Using Jobs, we can schedule the deployed package.

Connecting to SSIS

When we are browsing a SQL Server 2005 DB through SQL Server Management Studio, it is possible from there to switch to the SSIS by selecting the Integration Services in the Connect button present in the Object Browser, and loggin in.

Else,  select Server Type as : Integration Services while starting the SQL Server Management Studio IDE itself.

Deleting / Removing a Installed SSIS Package :

Deleting a SSIS Package is very easy.  Get connected to the SSIS first in the Management Studio, then traverse to the Package you want to delete, & just right click the Package and Select Delete. Cool na But Deleting a SSIS Package is just removing the corresponding DTSX file.

The reason why I want to add this also is, that I have seen many people struggling to do the same, and even I was one of them before!!!

Deleting/Removing a DTS Package or installing, updating or importing a DTS Package is also possible with DTUtil.exe file which is a command line tool, comes along with SQL Server 2005.