One-click Deploy SSIS Package from Visual Studio to SQLServer

Visual Studio can deploy Analysis Services and Reporting Services projects to a server but there is no such option for Integration Services. Yet in my current setup I need to run packages on the server: multiple packages maintained by different people are part of a larger chain. Executing only one package makes no sense in this case. Sidenote: another reason that forces me to run packages on the server is because anything that has “TransactionOption = Required” fails to run from a client pc. If somebody knows of a way to make it run, please leave a comment…

Luckily there is an easy way to add the deployment capability to VS. In short: a custom action calls a batch command file that in turn uses dtutil to actually deploy the SSIS package.

1- Create a batch command file

@echo off
set DESTSERVER=yourserver
set DESTLOCATION=path
set SOURCE=%1
set DESTNAME=%~2
dtutil /FILE %SOURCE% /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%DESTNAME% /Quiet

There are 2 things hard-coded into the command file: the server name and the location within MSDB. The location is something like a folder structure. Leave it empty if you want to deploy to the base of MSDB.

2- Add a menu to the External Tools menu

Go to “Tools”, then “External Tools…” and click “Add”. This will add a menu option under the menu “Tools”.
Title: Deploy to MSDB (or whatever you want to see in the menu)
Command: the full path to the batch command file created in step 1.
Arguments:$(ItemPath) $(ItemFileName)
Keep a space between the two arguments. You’ll notice a new menu under the Tools menu. You can then go one step further and add an icon to the toolbar.

3- Add an icon for the deploy menu item in the toolbar
It is not easy to find, but you can create your custom icon and it takes only a minute or so.
- Right-click the toolbar and choose “Customize…”
- In the “Command” tab, choose in the left listbox “Tools”.
- Pick “External Command 1″ (or another number if you did not put the menu item on top in step 2) and drag it to the toolbar.
- Click “Rearrange Commands…”, tick the option “Toolbar” and select the toolbar in which you dropped the menu item (probably “Standard”).
- Select “External Command 1″ in the listbox and then click “Modify Selection”, then “Edit Button editor”.
- Make a nice little drawing and close the icon editor.
- Click “Modify Selection” once more and select “Default Style”.

4- All set!
If you click the newly added icon it will deploy the active SSIS package to the server.
Any Sql Server Agent jobs that launch that package will automatically use the newly deployed version – no need to edit the job.
There is one caveat: remember to first save the project before clicking on the deploy icon as it will deploy the dtsx in its current state on disk.