SSIS developers and DBAs often encounter scenarios where they need to copy, install, or delete SSIS packages in SQL Server. These tasks can be easily accomplished using the DTUTIL command prompt utility. In this article, we will explore how to perform these operations efficiently.
Copying SSIS Package from MSDB Database to File System
When you want to copy an SSIS package deployed in the MSDB database to the file system, follow these steps:
- Establish a Remote Desktop Connection with the source SQL Server where the SSIS package is deployed.
- Go to Start > Run, type CMD, and hit Enter to open the command prompt.
- Enter the following syntax:
COPY SSIS Package to File System
DTUTIL /SQL <PACKAGENAME> /COPY FILE;<DRIVE:\FOLDERNAME\PACKAGENAME.DTSX> /QUIET
For example, to copy the “SAMPLEPACKAGENAME” SSIS package from the MSDB database to the “C:\SSISPACKAGE” folder, use the following command:
DTUTIL /SQL SAMPLEPACKAGENAME /COPY FILE;C:\SSISPACKAGE\SAMPLEPACKAGENAME.DTSX /QUIET
Installing SSIS Package to MSDB Database
If you have an SSIS package available in the file system and want to install it to the MSDB database, follow these steps:
- Open a Remote Desktop Connection to the SQL Server where you want to install the package.
- Go to Start > Run, type CMD, and hit Enter to open the command prompt.
- Enter the following syntax:
Install SSIS Package to MSDB Database
DTUTIL /FILE <DRIVE:\FOLDERNAME\PACKAGENAME.DTSX> /COPY SQL;<PACKAGENAME>
For example, to install the “SAMPLEPACKAGENAME” SSIS package from the “C:\SSISPACKAGE” folder to the MSDB database, use the following command:
DTUTIL /FILE C:\SSISPACKAGE\SAMPLEPACKAGENAME.DTSX /COPY SQL;SAMPLEPACKAGENAME
Deleting SSIS Package Stored in the MSDB Database
If you need to delete an SSIS package from the MSDB database, follow these steps:
- Open a Remote Desktop Connection to the SQL Server where the package is deployed.
- Go to Start > Run, type CMD, and hit Enter to open the command prompt.
- Enter the following syntax:
Delete SSIS Package from MSDB Database
DTUTIL /SQL <PACKAGENAME> /DELETE /SourceS <ServerName>
For example, to delete the “SAMPLEPACKAGENAME” SSIS package from the MSDB database, use the following command:
DTUTIL /SQL SAMPLEPACKAGENAME /DELETE /SourceS AKMEHTA
Conclusion
The DTUTIL command prompt utility provides a convenient way for SSIS developers and DBAs to copy, install, and delete SSIS packages deployed in the MSDB database. This tool saves a significant amount of time when deploying packages across different environments such as development, testing, user acceptance testing, and production.