ISPAC file & SSIS packages can be deployed from the command line. This can speed up the deployment and help in automating the deployments. ISPAC file referred to as deployable output file generated from SQL server integration projects. When we build SSIS project it creates an ISPAC file under the bin folder. Besides, deploying SSIS projects from Visual studio is equivalent to deploying ISPAC files in the back hood. ISPAC file deploys all the SSIS packages. We can also deploy only selected packages using the existing deployment wizard from the command line.
We already have a GUI based Integration Services Deployment Wizard as shown in figure 1. However, often we require to automate the deployment process to quickly deploy ISPAC file and SSIS packages using the command line. We can utilize ISDeploymentWizard.exe command-line utility. This utility is nothing but a command-line version of GUI based Integration services deployment wizard.
ISDeploymentWizard.exe – File Default Path
This command-line utility is by default installed with Visual Studio (SSDT). The file can be found on following paths, and you should be able to use it from the command prompt at any path without setting the environment variable. On my machine it is under folder 150 (Compatibility level), it may vary in your case, look for in 130/140 folder based on your set compatibility level.
In case your environment path is not set to SQL Server directly, you can use the absolute path for this file.
..\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn
..\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn
ISDeploymentWizard.exe – Command Line Arguments
Go to command prompt and run IsDeploymentWizard.exe /? Command to understand possible command line arguments. Here you will find only important command-line arguments, you can view all the arguments with the above command if needed.
Deploy ISPAC File Using ISDeploymentWizard.exe – Command Line
Using the following command you can deploy the ISPAC file to the destination server. Importantly, note here we are using Windows authentication to deploy the ISPAC file. Make sure to have the entire command in one line. With this approach, we can only deploy a single ISPAC file.
ISDeploymentWizard.exe /Silent /ModelType:Project /SourcePath:"D:\AdventuresWorksStaging.ispac" /DestinationServer:"DESKTOP-4COH4E7\SQLSERVER2019" /DestinationPath:"/SSISDB/AdventureWorks/AdventuresWorksStaging"
Deploy Single SSIS Package Using ISDeploymentWizard.exe – Command Line
ISDeploymentWizard.exe /Silent /ModelType:Package /SourcePath:"C:\Users\vishw\source\repos\AdventureWorksMigration\AdventureWorksMigration" /Packages:"SalesOrderDetails.dtsx;" /DestinationServer:"DESKTOP-4COH4E7\SQLSERVER2019" /DestinationPath:"/SSISDB/AdventureWorks/AdventureWorksMigration"
Deploy Multiple SSIS Package Using ISDeploymentWizard.exe – Command Line
While deploying multiple packages make sure package names comma separated. This will deploy multiple packages to destination SSIS DB catalog.
ISDeploymentWizard.exe /Silent /ModelType:Package /SourcePath:"C:\Users\vishw\source\repos\AdventureWorksMigration\AdventureWorksMigration" /Packages:"SalesOrderDetails.dtsx;DimProduct.dtsx;" /DestinationServer:"DESKTOP-4COH4E7\SQLSERVER2019" /DestinationPath:"/SSISDB/AdventureWorks/AdventureWorksMigration"
Summary & Read More
In summary, we have learned how to deploy ISPAC file using ISDeploymentWizard.exe command line. Including how to deploy single and multiple SSIS packages using command line. We can put this command line code in a batch file and automate the ISPAC and package deployment with a schedule or single run.
Read More and browse through more posts related to SQL Server on Tech-Recipes.
1. Introduction To DATETIME Functions In SQL Server
2. SQL Server – Error Solution – String or Binary Data would be Truncated
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Index Computed Column In SQL Server
5. How To Use Computed Column In SQL Server
Didn’t work. When you use the GUI to do the deployment on the last page it gives you the command you could run and that was the same as what I created and did not work. I think there are lots of bugs in the ISDeploymentWizard. There are lots of people across the community having problems with single package deployments.