How To Deploy ISPAC File & SSIS Package From Command Line

Posted August 23, 2020 by Vishwanath Dalvi in Database, SQL Server

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.

Deploy ISPAC File & SSIS Package From Command Line_1

Figure 1 – Integration Services Deployment Wizard – GUI Based.

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.

 How To Deploy ISPAC File & SSIS Package From Command Line

Figure 2 – ISDeploymentWizard.exe Command Line Arguments.


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

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply